ExecuteDelete 删除 ExecuteUpdate 更新

EF7.0 支持两个新的方法。会直接生成Sql的方式来删除数据 和更新数据

ExecuteDelete

await context.Tags.ExecuteDeleteAsync();

生成的sql

DELETE FROM [t]
FROM [Tags] AS [t]

包含条件

await context.Tags.Where(t => t.Text.Contains("malema")).ExecuteDeleteAsync();

生成的sql语句如下

DELETE FROM [t]
FROM [Tags] AS [t]
WHERE [t].[Text] LIKE N'%malema%'

使用其它导航表/属性

await context.Tags.Where(t => t.Posts.All(e => e.PublishedOn.Year < 2022)).ExecuteDeleteAsync()

生成的sql如下

DELETE FROM [t]
FROM [Tags] AS [t]
WHERE NOT EXISTS (
    SELECT 1
    FROM [PostTag] AS [p]
    INNER JOIN [Posts] AS [p0] ON [p].[PostsId] = [p0].[Id]
    WHERE [t].[Id] = [p].[TagsId] AND NOT (DATEPART(year, [p0].[PublishedOn]) < 2022))

ExecuteUpdate

跟 ExecuteDelete类似不过得指定如何更新

await context.Blogs.ExecuteUpdateAsync(
    s => s.SetProperty(b => b.Name, b => b.Name + " malema "));

会生成如下的语句

UPDATE [b]
    SET [b].[Name] = [b].[Name] + N'  malema '
FROM [Blogs] AS [b]

更新多个字段

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 + " ( 发布于" + b.PublishedOn.Year + ")"));

生成的语句如下

UPDATE [p]
    SET [p].[Content] = (([p].[Content] + N' ( 发布于') + 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

同样 可以 使用其它导航表/属性

await context.Tags
    .Where(t => t.Posts.All(e => e.PublishedOn.Year < 2022))
    .ExecuteUpdateAsync(s => s.SetProperty(t => t.Text, t => t.Text + " (old)"));

生成的Sql

UPDATE [t]
    SET [t].[Text] = [t].[Text] + N' (old)'
FROM [Tags] AS [t]
WHERE NOT EXISTS (
    SELECT 1
    FROM [PostTag] AS [p]
    INNER JOIN [Posts] AS [p0] ON [p].[PostsId] = [p0].[Id]
    WHERE [t].[Id] = [p].[TagsId] AND NOT (DATEPART(year, [p0].[PublishedOn]) < 2022))