【EF Core】增删改查

插入

using (TestDbContext ctx = new TestDbContext())
{
    var b1 = new Book
    {
        AuthorName = "杨中科",
        Title = "零基础趣学C语言",
        Price = 59.8,
        PubTime = new DateTime(2019, 3, 1)
    };
    var b2 = new Book
    {
        AuthorName = "Robert Sedgewick",
        Title = "算法(第4版)",
        Price = 99,
        PubTime = new DateTime(2012, 10, 1)
    };
    var b3 = new Book
    {
        AuthorName = "吴军",
        Title = "数学之美",
        Price = 69,
        PubTime = new DateTime(2020, 5, 1)
    };
    var b4 = new Book
    {
        AuthorName = "杨中科",
        Title = "程序员的SQL金典",
        Price = 52,
        PubTime = new DateTime(2008, 9, 1)
    };
    var b5 = new Book
    {
        AuthorName = "吴军",
        Title = "文明之光",
        Price = 246,
        PubTime = new DateTime(2017, 3, 1)
    };
    ctx.Books.Add(b1);
    ctx.Books.Add(b2);
    ctx.Books.Add(b3);
    ctx.Books.Add(b4);
    ctx.Books.Add(b5);
    await ctx.SaveChangesAsync();
}

查询

using (TestDbContext ctx = new TestDbContext())
{
    //查询全部
    foreach (var b in ctx.Books)
    {
        Console.WriteLine($"Id={b.Id},Title={b.Title},Price={b.Price}");
    }
    Console.WriteLine("----------------");

    //按条件查询，并且排序
    foreach (var b in ctx.Books.Where(b => b.Price > 80).OrderByDescending(b => b.Id))
    {
        Console.WriteLine($"Id={b.Id},Title={b.Title},Price={b.Price}");
    }
    Console.WriteLine("----------------");

    //Single
    var book = ctx.Books.FirstOrDefault(b => b.Id == 1);
    Console.WriteLine($"Id={book.Id},Title={book.Title},Price={book.Price}");
    Console.WriteLine("----------------");

    //分组查询
    var groups = ctx.Books.GroupBy(b => b.AuthorName).Select(g => new { AuthorName = g.Key, BooksCount = g.Count(), MaxPrice = g.Max(b => b.Price) });
    foreach (var g in groups)
    {
        Console.WriteLine($"作者:{g.AuthorName},图书数量:{g.BooksCount},最高价格:{g.MaxPrice}");
    }

    //查询指定字段
    var book = ctx.Books.Select(b => new { b.Id, b.Title }).FirstOrDefault();
}

修改

using (TestDbContext ctx = new TestDbContext())
{
    var book = ctx.Books.FirstOrDefault(b => b.Id == 1);
    book.Price = 100;
    ctx.SaveChanges();
}

删除

using (TestDbContext ctx = new TestDbContext())
{
    var book = ctx.Books.FirstOrDefault(b => b.Id == 4);
    ctx.Remove(book);
    //ctx.Books.Remove(book);//这种方式也可以
    ctx.SaveChanges();
}

批量增删改

EF Core中不支持高效的删除、更新、插入数据，都是逐条操作。AddRange、DeleteRange等。
两种办法实现批量增删改：

• 执行sql
• 通过EF Core扩展包：Zack.EFCore.Batch

Zack.EFCore.Batch

GitHub：https://github.com/yangzhongke/Zack.EFCore.Batch
该程序集实现的批量更新、批量删除功能可以通过生成一条Update、Delete语句来实现，而不需要EFCore原始的写法先查询后操作了。

1、安装Nuget

Install-Package Zack.EFCore.Batch.MSSQL 

2、

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
            optionsBuilder.UseLoggerFactory(LoggerFactory.Create(build =>
            {
                build.AddDebug();
            }));

            optionsBuilder.UseBatchEF_MSSQL();// MSSQL Server 用户用这个
 }

3、批量增删改

//批量删除
await ctx.DeleteRangeAsync<Book>(b => b.Price > n || b.AuthorName =="zack yang");
//批量修改
await ctx.BatchUpdate<Book>()
   .Set(b => b.Price, b => b.Price + 3)
   .Set(b => b.Title, b => s)
   .Set(b =>b.AuthorName,b=>b.Title.Substring(3,2)+b.AuthorName.ToUpper())
   .Set(b => b.PubTime, b => DateTime.Now)
   .Where(b => b.Id > n || b.AuthorName.StartsWith("Zack"))
.ExecuteAsync();
//批量插入
ctx.BulkInsert(books);

批量操作详细：https://www.cnblogs.com/yaopengfei/p/14441115.html