EntityFrameWork Core 批量操作
在EF7.0的更新中,新增了批量更新和批量删除的功能:
批量删除:ExecuteDelete
对 DbSet
调用 ExecuteDelete
或 ExecuteDeleteAsync
会立即从数据库中删除该 DbSet
的所有实体。 例如,删除所有 Tag
实体:
await context.Tags.ExecuteDeleteAsync();
使用 SQL Server 时,这将执行以下 SQL:
DELETE FROM [t] FROM [Tags] AS [t]
查询可以包含筛选器:
await context.Tags.Where(t => t.Text.Contains(".NET")).ExecuteDeleteAsync();
这会执行以下 SQL:
DELETE FROM [t] FROM [Tags] AS [t] WHERE [t].[Text] LIKE N'%.NET%'
批量更新:ExecuteUpdate
await context.Blogs.ExecuteUpdateAsync( s => s.SetProperty(b => b.Name, b => b.Name + " *Featured!*"));
SetProperty
的第一个参数指定要更新的属性;在本例中,Blog.Name
。 第二个参数指定应如何计算新值;在本例中,通过获取现有值并追加 "*Featured!*"
。 生成的 SQL 为:
UPDATE [b] SET [b].[Name] = [b].[Name] + N' *Featured!*' FROM [Blogs] AS [b]
与 ExecuteDelete
一样,查询可用于筛选更新的实体。 此外,可以使用多个调用 SetProperty
来更新目标实体上的多个属性。
await context.Posts .Where(p => p.PublishedOn.Year < 2022) .ExecuteUpdateAsync(s => s .SetProperty(b => b.Title, b => b.Title + " (" + b.PublishedOn.Year + ")") .SetProperty(b => b.Content, b => b.Content + " ( This content was published in " + b.PublishedOn.Year + ")"));
在这种情况下,生成的 SQL 稍微复杂一些:
UPDATE [p] SET [p].[Content] = (([p].[Content] + N' ( This content was published in ') + COALESCE(CAST(DATEPART(year, [p].[PublishedOn]) AS nvarchar(max)), N'')) + N')', [p].[Title] = (([p].[Title] + N' (') + COALESCE(CAST(DATEPART(year, [p].[PublishedOn]) AS nvarchar(max)), N'')) + N')' FROM [Posts] AS [p] WHERE DATEPART(year, [p].[PublishedOn]) < 2022
文章摘自微软文档:https://learn.microsoft.com/zh-cn/ef/core/what-is-new/ef-core-7.0/whatsnew?view=aspnetcore-2.2