【EF Core】直接更新数据

install-package microsoft.entityframeworkcore.sqlite

public class Photo
{
    public int Pid { get; set; }
    public required string Title { get; set; }
    public int DPI { get; set; }
    public int Width { get; set; }
    public int Height { get; set; }
    public string? Tag { get; set; }
}

public class MyContext : DbContext
{
    public MyContext(DbContextOptions<MyContext> ops)
        : base(ops)
    { }

    /// <summary>
    /// 数据集合
    /// </summary>
    public DbSet<Photo> Photos { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Photo>(et =>
        {
            // 主键要配置，它不符合公共约定的格式
            et.HasKey(k => k.Pid).HasName("PK_Pid");
            // 其他属性可以自动配置，没特别要求可以跳过
        });
    }
}

// 先生成数据库上下文选项
DbContextOptionsBuilder<MyContext> optBuilder = new();
// 连接字符串
optBuilder.UseSqlite("data source=hehe.db");
// 过滤日志，只记录生成的SQL
optBuilder.LogTo((eventId, logLevel) =>
{
    // CommandExecuting 表示SQL执行前
    // CommandExecuted 表示SQL执行后
    // 两个事件任选其一都可以拿到SQL语句
    if (eventId.Id == RelationalEventId.CommandExecuted)
    {
        return true;    // true 表示记录该日志
    }
    return false;       // false 表示不记录
},
eventData =>
{
    if(eventData is CommandEventData cmdEvtData)
    {
        // 获取控制台当前的文本颜色
        ConsoleColor backup = Console.ForegroundColor;
        // 把文本改成地球颜色
        Console.ForegroundColor = ConsoleColor.Yellow;
        // 打印SQL
        Console.WriteLine($"已执行：{cmdEvtData.LogCommandText}");
        // 恢复文本颜色
        Console.ForegroundColor = backup;
    }
});

// 实例化数据库上下文
using(var c = new MyContext(optBuilder.Options))
{
    // 如果存在，删库跑路
    // 如果不存在，建库留守
    c.Database.EnsureDeleted();
    if(c.Database.EnsureCreated())
    {
        // 新库无数据，加点料
        c.Photos.Add(new Photo
        {
            Title = "山的对面有恐龙",
            Width = 50,
            Height = 35,
            DPI = 300,
            Tag = "休闲"
        });
        c.Photos.Add(new()
        {
            DPI = 300,
            Title = "清明上坟图",
            Tag = "祭祖，观光",
            Width = 1700,
            Height = 650
        });
        // 保存数据
        c.SaveChanges();
    }
}

public class MyContext : DbContext
{
    ……

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Photo>(et =>
        {
            // 主键要配置，它不符合公共约定的格式
            et.HasKey(k => k.Pid).HasName("PK_Pid");
            // 初始数据
            et.HasData(new Photo
            {
                Pid = 1,
                Title = "山的对面有恐龙",
                Width = 50,
                Height = 35,
                DPI = 300,
                Tag = "休闲"
            },
            new()
            {
                Pid = 2,
                DPI = 300,
                Title = "清明上坟图",
                Tag = "祭祖，观光",
                Width = 1700,
                Height = 650
            });
        });
    }
}

// 先生成数据库上下文选项
DbContextOptionsBuilder<MyContext> optBuilder = new();
// 连接字符串
optBuilder.UseSqlite("data source=hehe.db");
……
// 初始化数据
optBuilder.UseSeeding((context, _) =>
{
    context.Photos.Add(
     new Photo
     {
         Title = "山的对面有恐龙",
         Width = 50,
         Height = 35,
         DPI = 300,
         Tag = "休闲"
     });
    context.Photos.Add(new()
    {
        DPI = 300,
        Title = "清明上坟图",
        Tag = "祭祖，观光",
        Width = 1700,
        Height = 650
    });
    context.Photos.Add(new Photo
    {
        Title = "燕子",
        Width = 500,
        Height = 400,
        DPI = 300,
        Tag = "动物"
    });
    // 保存到数据库
    context.SaveChanges();
});

 using (MyContext context = new(optBuilder.Options))
 {
    int n = context.Photos
         .Where(x=> x.Pid == 3)
         .ExecuteUpdate(updsetBuilder =>
              {
                  updsetBuilder.SetProperty(a => a.Tag, "下次拍更好的");
              });
     Console.WriteLine("更新了{0}条记录", n);
 }

UPDATE "Photos" AS "p"
SET "Tag" = @p
WHERE "p"."Pid" = 3

// 重载1
SetProperty<TProperty>(Expression<Func<TSource,TProperty>> propertyExpression, TProperty valueExpression);
// 重载2
SetProperty<TProperty>(Expression<Func<TSource,TProperty>> propertyExpression, Expression<Func<TSource,TProperty>> valueExpression);

int n = context.Photos
     .Where(x=> x.Pid == 3)
     .ExecuteUpdate(updsetBuilder =>
          {
              updsetBuilder.SetProperty(a => a.Tag, "下次拍更好的");
              updsetBuilder.SetProperty(a => a.DPI, a => (a.DPI - 100));
          });

UPDATE "Photos" AS "p"
SET "Tag" = @p,
    "DPI" = "p"."DPI" - 100
WHERE "p"."Pid" = 3

 int n = context.Photos
     .Where(p => p.Pid == 2)
     .ExecuteDelete();
 Console.WriteLine($"已删除{n}条记录");

DELETE FROM "Photos" AS "p"
WHERE "p"."Pid" = 2