fsql.InsertOrUpdate<User>() .SetSource(user,a=> a.Id) .IfExistsDoNothing() .ExecuteAffrowsAsync(); 不重复的数据也没有插入

[zhiquanchi]

问题描述及重现代码：

完整代码

// See https://aka.ms/new-console-template for more information

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using FreeSql;
using FreeSql.DataAnnotations;
public class User
{
    [Column(IsIdentity = true, IsPrimary = true)]
    public int Id { get; set; }
    public required string Name { get; set; }
    public string? NickName { get;set; } = "默认昵称";
}

namespace ConsoleApp2
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            using var host = Host.CreateDefaultBuilder(args)
                .ConfigureServices((context, services) =>
                {
                    var fsql = new FreeSql.FreeSqlBuilder()
                        .UseConnectionString(FreeSql.DataType.Sqlite,
                            "Data Source=./test.db;Pooling=true;Max Pool Size=10")
                        .UseAutoSyncStructure(true)
                        .Build();
                    services.AddSingleton<IFreeSql>(fsql);
                }).Build();
            
            
            var fsql = host.Services.GetRequiredService<IFreeSql>();
            
            await fsql.Delete<User>().
                    Where(a => true).ExecuteAffrowsAsync();

            var user = new User()
            {
                Name = "张三",
                NickName = "张三的昵称"
            };
            await fsql.InsertOrUpdate<User>()
                .SetSource(user,a=> a.Id)
                .IfExistsDoNothing()
                .ExecuteAffrowsAsync();
            
            var user2 = new User()
            {
                Name = "李四",
                NickName = "李四的昵称"
            };
            await fsql.InsertOrUpdate<User>()
                .SetSource(user2,a=> a.Id)
                .IfExistsDoNothing()
                .ExecuteAffrowsAsync();
            
            var user1 = await fsql.Select<User>().ToListAsync();
            foreach (var u in user1)
            {
                Console.WriteLine($"Id: {u.Id}, Name: {u.Name}, NickName: {u.NickName}");
            }
        }
    }
    
}

数据库版本

sqlite3

安装的Nuget包

FreeSql 3.5.210 3.5.210 FreeSql.Provider.Sqlite 3.5.210 3.5.210

.net framework/. net core？ 及具体版本

.net core9。

[zhiquanchi]

补充例子

// See https://aka.ms/new-console-template for more information

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using FreeSql;
using FreeSql.DataAnnotations;
public class User
{
    [Column(IsIdentity = true, IsPrimary = true)]
    public int Id { get; set; }
    public required string Name { get; set; }
    public string? NickName { get;set; } = "默认昵称";
}

namespace ConsoleApp2
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            using var host = Host.CreateDefaultBuilder(args)
                .ConfigureServices((context, services) =>
                {
                    var fsql = new FreeSql.FreeSqlBuilder()
                        .UseConnectionString(FreeSql.DataType.Sqlite,
                            "Data Source=./test.db;Pooling=true;Max Pool Size=10")
                        .UseAutoSyncStructure(true)
                        .Build();
                    services.AddSingleton<IFreeSql>(fsql);
                }).Build();
            
            
            var fsql = host.Services.GetRequiredService<IFreeSql>();
            
            // 删除所有数据
            await fsql.Delete<User>().
                    Where(a => true).ExecuteAffrowsAsync();

            var users = new List<User>
            {
                new User { Name = "张三" },
                new User { Name = "李四" },
                new User { Name = "李四" }
            };
            await fsql.InsertOrUpdate<User>()
                .SetSource(users, x => x.Name)
                .IfExistsDoNothing()
                .ExecuteAffrowsAsync();
            
            
            var user1 = await fsql.Select<User>().ToListAsync();
            foreach (var u in user1)
            {
                Console.WriteLine($"Id: {u.Id}, Name: {u.Name}, NickName: {u.NickName}");
            }
        }
    }
    
}

期望应该是可以查询到到两条数据。但是只查询到 name=张三 的记录

[2881099]

IsIdentity 在某些数据库实现不了，数据库限制了

[zhiquanchi]

IsIdentity 在某些数据库实现不了，数据库限制了

可以详细讲一下吗

[2881099]

你观察一下sql，分析一下不同数据库产生的sql

[2881099]

应该是sqlite不支持自增这样操作

[2881099]

INSERT OR IGNORE INTO "User3356"("Id", "Name", "NickName") VALUES(0, '张三', '张三的昵称')

sqlite 这种语句把 id=0 都插入了，因此后续再执行时认为记录存在。

[2881099]

如果想跨数据库兼容性好，建议不要用自增表执行 InsertOrUpdate

[zhiquanchi]

和pgsql的默认行为不符合

DROP TABLE IF EXISTS "TestUser";

create table "TestUser"(
  "Id" SERIAL primary key,
  "Name" text,
  "NickName" text
);

alter table "TestUser" add CONSTRAINT union_name unique ("Name");

-- 第一次插入，应该插入 2 行记录
INSERT INTO "TestUser"("Name", "NickName") VALUES('张三', '默认昵称'), ('李四', '默认昵称'), ('李四', '默认昵称') 
ON  CONFLICT ("Name") DO NOTHING;

-- 第一次插入，应该插入 0 行记录
INSERT INTO "TestUser"("Name", "NickName") VALUES('张三', '默认昵称'), ('李四', '默认昵称'), ('李四', '默认昵称') 
ON  CONFLICT ("Name") DO NOTHING;

freesql 中运行

// See https://aka.ms/new-console-template for more information

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using FreeSql;
using FreeSql.DataAnnotations;


[Table(Name = "TestUser")]
public class User
{
    [Column(IsIdentity = true, IsPrimary = true)]
    public int Id { get; set; }
    public required string Name { get; set; }
    public string? NickName { get;set; } = "默认昵称";
}

namespace ConsoleApp2
{

    public class Program
    {
        public static async Task Main(string[] args)
        {
            using var host = Host.CreateDefaultBuilder(args)
                .ConfigureServices((context, services) =>
                {
                    var fsql = new FreeSql.FreeSqlBuilder()
                        .UseConnectionString(FreeSql.DataType.PostgreSQL,
                            "Host=36.134.39.150;Port=5432;Username=renyimen;Password=Alice123.; Database=renyimen;ArrayNullabilityMode=Always;Pooling=true;Minimum Pool Size=100;connection lifetime=60;Keepalive=30")
                        .UseAutoSyncStructure(true)
                        .UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText))
                        .Build();
                    services.AddSingleton<IFreeSql>(fsql);
                }).Build();
            var fsql = host.Services.GetService<IFreeSql>() ?? throw new InvalidOperationException("IFreeSql service not registered.");
            
           
            await fsql.Delete<User>().Where(a => true).ExecuteAffrowsAsync();

            var user = new[]
            {
                new User { Name = "张三" },
                new User { Name = "李四" },
                new User { Name = "李四" }
            };

            var result = await fsql.InsertOrUpdate<User>()
                // .SetSource(user,u=>u.Name)
                .SetSource(user)
                .IfExistsDoNothing()
                .ExecuteAffrowsAsync();
            Console.WriteLine($"插入或更新的记录数: {result}");

           var users = await fsql.Select<User>().ToListAsync();
                      foreach (var u in users)
                      {
                          Console.WriteLine(u.Name);
            }
        }
    }
    
}

返回值为

插入或更新的记录数: 3 张三 李四 李四