Upsert update or insert 更新或者插入

很多时候我们需要判断数据是不是在数据库，如果在的话就更新某些字段不在话就插入进去。

使用EntityFramework我们也得先用firstOrDefault把数据查出来。 然后进行操作。

大部分数据库本身 已经有支持这个特性了

PostgreSQL/Sqlite 支持 INSERT … ON CONFLICT DO UPDATE SqlServer 支持 MERGE MySQL 支持 INSERT INTO … ON DUPLICATE KEY UPDATE

EF 官方没有支持这个，我们可以使用第三方的库

https://github.com/artiomchi/FlexLabs.Upsert

示例代码。 需要使用 Nuget 添加三个Package

Pomelo.EntityFrameworkCore.MySql 6.0.2 Microsoft.Extensions.Logging.Console 6.0.0 FlexLabs.EntityFrameworkCore.Upsert 6.0.2

mysql下面 是使用 DUPLICATE KEY UPDATE.所以检查的字段必须是唯一索引或者主键。

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

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

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

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 mysqlConn = "Server=127.0.0.1;port=3306;Database=test;uid=root;pwd=mysql;Character Set=utf8;";
  
    var services = new ServiceCollection();
    services.AddLogging(x => x.AddConsole()); //注入Logging
    services.AddDbContextPool<MalemaContext>((sp, options) =>
    {
        options.UseLoggerFactory(sp.GetRequiredService<ILoggerFactory>()) //注入IloggerFactory
        .UseMySql(mysqlConn, ServerVersion.AutoDetect(mysqlConn));

    }, poolSize: 64);
    services.AddPooledDbContextFactory<MalemaContext>((sp, options) =>
    {
        options.UseLoggerFactory(sp.GetRequiredService<ILoggerFactory>())
         .UseMySql(mysqlConn, ServerVersion.AutoDetect(mysqlConn));
    }, poolSize: 64);
    var sp = services.BuildServiceProvider();
    return sp;
}
public class Country
{
    [Required, StringLength(50)]
    public string Name { get; set; }

    [Key]
    [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")]
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; }
}

会生成如下的代码

      INSERT INTO `Countries` (`ISO`, `Created`, `Name`, `Updated`) VALUES (@p0, @p1, @p2, @p3), (@p4, @p5, @p6, @p7) ON DUPLICATE KEY UPDATE `Created` = VALUES(`Created`), `Name` = VALUES(`Name`), `Updated` = VALUES(`Updated`)

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

drop database test