Sql server Upsert update or insert 更新或者插入

跟Mysql不同的是我们不需要把检测的字段设为唯一索引或者Key （推荐做成主键或者唯一索引）

示例代码。 需要使用 Nuget 添加三个Package Microsoft.EntityFrameworkCore.SqlServer 6.0 Microsoft.Extensions.Logging.Console 6.0 FlexLabs.EntityFrameworkCore.Upsert

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using System.ComponentModel.DataAnnotations;
Console.WriteLine("Malema upsert");


var country = new Country
{
    Name = "china",
    ISO = "cn",
};
var visit = new PageVisit
{
    Date = DateTime.UtcNow.Date,
    Visits = 1,
};

var sp = GetSserviceProvider();
var context = sp.GetService<MalemaContext>();
context.Database.EnsureCreated();

await context.AddAsync(country);
await context.AddAsync(visit);
await context.SaveChangesAsync();


await context.Countries.Upsert(country)
    .On(c => c.ISO)
    .WhenMatched(c => new Country
    {
        Name = "中国",
        Updated = DateTime.UtcNow,
    })
    .RunAsync();
// 数据库中的数据会更新成  中国 还有 udpated的时间变成了现在
await context.PageVisits.Upsert(visit)
    .On(v => new { v.UserId, v.Date })
    .WhenMatched(v => new PageVisit
    {
        Visits = v.Visits + 1,
    })
    .RunAsync();
// 数据库中的 visit会加1

Console.WriteLine("step 1 done");

// 进行匹量的 upsert. 所有的属性都会被更新
var countries = new List<Country>() {
    new Country() {
        ISO = "cn",
        Name = "中国2",
        Created = DateTime.UtcNow.AddDays(-9),
        Updated= DateTime.UtcNow.AddDays(-6)
    },
    new Country() {
        ISO = "jp",
        Name = "japen",
        Created = DateTime.UtcNow.AddDays(-9),
        Updated= DateTime.UtcNow.AddDays(-6)
    }
};

await context.Countries.UpsertRange(countries)
    .On(c => c.ISO)
    .RunAsync();

Console.WriteLine("step 2 done");

// 进行匹量的 upsert. 在 whenMatched里面的才会被更新
await context.Countries.UpsertRange(countries)
    .On(c => c.ISO)
    .WhenMatched((cDb, cIns) => new Country
    {
        Name = cIns.Name,
        Updated = DateTime.UtcNow,
    })
    .RunAsync();
Console.WriteLine("step 3 done");

static ServiceProvider GetSserviceProvider()
{
    var connectionString = "Data Source=127.0.0.1;Initial Catalog=malematest;Integrated Security=False;Persist Security Info=False;User ID=sa;Password=malemadotnet";
    // 本机有开集成验证还可以用 Data Source=.;Initial Catalog=malematest;Trust Server Certificate=true;Integrated Security=True;
    var optionBuilder = new DbContextOptionsBuilder<MalemaContext>();
    optionBuilder.UseSqlServer(connectionString);
    var services = new ServiceCollection();
    services.AddLogging(x => x.AddConsole()); //注入Logging
    services.AddDbContextPool<MalemaContext>((sp, options) =>
    {
        options.UseLoggerFactory(sp.GetRequiredService<ILoggerFactory>()) //注入IloggerFactory
        .UseSqlServer(connectionString);

    }, poolSize: 64);
    services.AddPooledDbContextFactory<MalemaContext>((sp, options) =>
    {
        options.UseLoggerFactory(sp.GetRequiredService<ILoggerFactory>())
        .UseSqlServer(connectionString);
    }, poolSize: 64);
    var sp = services.BuildServiceProvider();
    return sp;
}

public class Country
{
    [Key]
    public int Id { get; set; }

    [Required, StringLength(50)]
    public string Name { get; set; }

    [Required, StringLength(2)]
    public string ISO { get; set; }
    public DateTime Created { get; set; }
    public DateTime Updated { get; set; }
}

//[Index("UserId", "Date", IsUnique = true, Name = "indexUserDate")] SqlServer 不需要设成唯一索引
public class PageVisit
{
    public int ID { get; set; }

    public int UserId { get; set; }
    public DateTime Date { get; set; }
    public int Visits { get; set; }
    public DateTime FirstVisit { get; set; }
    public DateTime LastVisit { get; set; }
}

public class MalemaContext : DbContext
{
    public MalemaContext(DbContextOptions options)
        : base(options)
    {
    }
    public DbSet<Country> Countries { get; set; }

    public DbSet<PageVisit> PageVisits { get; set; }
}

生成的sql代码如下

  MERGE INTO [Countries] WITH (HOLDLOCK) AS [T] 
USING ( VALUES (@p0, @p1, @p2, @p3), (@p4, @p5, @p6, @p7) ) AS [S] ([Created], [ISO], [Name], [Updated]) 
ON [T].[ISO] = [S].[ISO]
WHEN NOT MATCHED BY TARGET THEN 
INSERT ([Created], [ISO], [Name], [Updated]) VALUES ([Created], [ISO], [Name], [Updated])
WHEN MATCHED THEN 
UPDATE SET [Name] = [S].[Name], [Updated] = @p8;

再次测试需要删除掉数据库

drop database test

这个东西还可以解决并发冲突的问题。( Violation of PRIMARY KEY constraint 'PK_xxx'. Cannot insert duplicate key in object) 主键Id 不自动增长的，或者是某些特定值的。 通常的写法是先查询出来，如果没有的话插入。 （这个地方是会发生并发冲突） 用Upsert可以解决它。