• EntityFramework:支持同一事务提交的批量删除实现方式
  • 苦丁茶 发表于 2014/12/2 17:50:00 | 分类标签: EntityFramework 批量删除
  •  一切从一段代码说起,下面一段代码是最近我在对一EF项目进行重构时发现的。
    protected override void DoRemove(T entity)
    {
        this.dbContext.Entry(entity).State = EntityState.Deleted;
        Committed = false;
    }
    
    protected override int DoRemove(System.Linq.Expressions.Expression<Func<T, bool>> predicate = null)
    {
        var set = dbContext.Set<T>().AsQueryable();
        set = (predicate == null) ? set : set.Where(predicate);
    
        int i = 0;
        foreach (var item in set)
        {
            DoRemove(item);
            i++;
        }
        return i;
    }    

    没错,这是使用Expression表达式实现批量删除数据的代码。当中的foreach代码块循环调用DoRemove(T entity)方法来修改符合条件的实体的状态码,最后执行dbContext的SaveChanges方法来提交到数据库执行删除命令。正是这个foreach引起了我的思考,我们知道EF会自动生成SQL语句提交到数据库执行,删除一条记录的话,会生成一条delete语句。如果要删除多条记录,手写SQL的话,只需一条带where条件的delete语句即可(如:delete from [tableName] where [condition]),而通过执行上面代码的批量删除方法,EF会不会生成一条类似这样的SQL呢?通过SQL Server Profiler跟踪发现这样的结果:

    一向NB的EF这下反而SB了,生成的是多条delete语句,一条记录生成一条SQL!这样搞的话,如果要删除的记录有上百条、上千条,甚至上万条的话,那不是把EF给累死,数据库DB也会跟着被连累:”老大,一句话可以说完的事,你给我拆开一个字一个字来说,欠揍啊!”。我们知道任何ORM框架在生成SQL时都会存在性能的损耗,某些SQL会被优化后生成,但在按指定条件批量删除记录这方面很多ORM框架都存在上面出现的尴尬场面。正如老赵在《扩展LINQ to SQL:使用Lambda Expression批量删除数据》一文所说这并不是ORM的问题,只是使用起来不方便。老赵一文所提的方法不失为一种好方案,主要思路是通过解析Expression表达式构造SQL的Where条件子句。看到这里大家估计也清楚了问题的焦点其实就是一条SQL。如果手写SQL的话上面的批量删除SQL语句会类似这样:delete from [DomainObjectA] where [Prop1] in (N'prop2',N'prop3',N'prop5'),而EF对于Expression参数的查询是十分给力的,自动生成的查询SQL是这样的:

    SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[Prop1] AS [Prop1], 
        [Extent1].[Prop2] AS [Prop2]
    FROM [dbo].[DomainObjectA] AS [Extent1]
    WHERE [Extent1].[Prop1] IN (N'prop2',N'prop3',N'prop5')

    大家发现了吧,EF生成的查询SQL跟手写的删除SQL语句结构上是如此的神似!如果我们把生成的SQL从SELECT到FROM那段替换成DELETE FROM的话,其实就是我们期望得到的一段SQL。那就按照这个思路动手吧,首先要获取EF生成的SQL语句,对于返回数据集是IQueryable<T>类型的,我们直接对结果ToString()即可获得,够简单吧。

    var set = dbContext.Set<T>().AsQueryable();
    
    string sql = set.Where(predicate).ToString().Replace("\r", "").Replace("\n", "").Trim();

     对于获取到的SQL字符串我们要进行处理,去除换行符、回车符、多余的空白字符,最关键是要把select到from这一段替换掉。如何替换呢?其实还有个细节遗漏了,先看看替换后的SQL,等会再讲明。

    DELETE
    FROM [dbo].[DomainObjectA] AS [Extent1]
    WHERE [Extent1].[Prop1] IN (N'prop2',N'prop3',N'prop5')

    一眼看上去,似乎没问题,挺标准的一段SQL呀。问题恰恰是在太过标准了!在SQL查询器上运行会报这样的出错提示:“Incorrect syntax near the keyword 'AS'.” 原来delete from。。。这样的SQL不接受“AS”关键字啊!其实也不能说完全不接受“AS",如果改为下面这样是可以通过的。

    DELETE
        [dbo].[DomainObjectA]
    FROM ( 
        SELECT 
            [Extent1].[ID] AS [ID], 
            [Extent1].[Prop1] AS [Prop1], 
            [Extent1].[Prop2] AS [Prop2]
        FROM [dbo].[DomainObjectA] AS [Extent1]
        WHERE [Extent1].[Prop1] IN (N'prop2',N'prop3',N'prop5')
    ) AS [T1]        

    细心的你可能已经发现了,括号里面的SQL其实就是EF生成的那段。如果这样做的话,我们完全可以把生成的SQL整段拿来用,只需在delete后再指定表名即可。
    现在问题变为如何从生成的SQL中提取出表名了。从前面EF生成的SQL中可以看出,它的结构是比较固定的(SELECT...FROM... AS...[WHERE...],当返回所有结果的话WHERE不会被生成)。如果熟悉正则表达式的话你可能第一时间已经想到了,没错!其实我们一开始就可以用正则表达式来解决。只怪本人的正则表达式功力欠佳,走了很多弯路。这次只能求助“度娘”了。。。求助中。。。有答案了,原来很早很早前就已经有位大牛在一篇名为《Linq to Sql: 批量删除之投机取巧版》解决了我所面临的问题,看来园子里不差牛人,闲来多逛逛,还是有意外收获的。借用他文中的正则表达式得了,省时省力。写到这,插个题外话:作为开发人员,信息搜索能力也是必备的(甚至可以毫不夸张地说是开发人员的第一门学问),对遇到的问题难题能迅速搜索到解决方法,从中借鉴别人的经验(不代表抄袭别人的作品),可以少走弯路,节省的时间、精力可以更多花在业务知识方面。言归正传,上代码!

    Regex reg = new Regex("^SELECT[\\s]*(?<Fields>.*)[\\s]*FROM[\\s]*(?<Table>.*)[\\s]*AS[\\s]*(?<TableAlias>.*)[\\s]*WHERE[\\s]*(?<Condition>.*)", RegexOptions.IgnoreCase);
    
    Match match = reg.Match(sql);
    
    if (!match.Success)
        throw new ArgumentException("Cannot delete this type of collection");
    
    string table = match.Groups["Table"].Value.Trim();
    string tableAlias = match.Groups["TableAlias"].Value.Trim();
    string condition = match.Groups["Condition"].Value.Trim().Replace(tableAlias, table);
    
    string sql1 = string.Format("DELETE FROM {0} WHERE {1}", table, condition);

    现在已经得到我们期望的SQL了,接下来是想办法让数据库去执行它即可,还好EF还是比较人性化的,它支持开放底层ADO.net框架,有三个API可以支持直接访问数据库。

    1、DbContext.Database.ExecuteSqlCommand
    
    2、DbContext.Database.SqlQuery
    
    3、DbSet.SqlQuery

    从名字我们知道后两个API主要用来查询数据,我们选用第一个API:DbContext.Database.ExecuteSqlCommand,而且返回是受影响的结果数,我们还能知道被删除的数据有多少条,简直是为此量身定制嘛。

    protected override int DoRemove(System.Linq.Expressions.Expression<Func<T, bool>> predicate = null)
    {
        var set = dbContext.Set<T>().AsQueryable();
        set = (predicate == null) ? set : set.Where(predicate);
    
        string sql = set.ToString().Replace("\r", "").Replace("\n", "").Trim();
        if (predicate == null && !string.IsNullOrEmpty(sql) && !string.IsNullOrWhiteSpace(sql))
            sql += " WHERE 1=1";
    
        Regex reg = new Regex("^SELECT[\\s]*(?<Fields>.*)[\\s]*FROM[\\s]*(?<Table>.*)[\\s]*AS[\\s]*(?<TableAlias>.*)[\\s]*WHERE[\\s]*(?<Condition>.*)", RegexOptions.IgnoreCase);
        Match match = reg.Match(sql);
    
        if (!match.Success)
            throw new ArgumentException("Cannot delete this type of collection");
    
        string table = match.Groups["Table"].Value.Trim();
        string tableAlias = match.Groups["TableAlias"].Value.Trim();
        string condition = match.Groups["Condition"].Value.Trim().Replace(tableAlias, table);
    
        string sql1 = string.Format("DELETE FROM {0} WHERE {1}", table, condition);
        int i = 0;
        i = dbContext.Database.ExecuteSqlCommand(sql1);return i;
    }    

    执行上面方法,通过SQL Server Profiler跟踪,发现确实是我们想要的结果,只有一条DELETE语句,而且满足条件的数据也确实被删除了。

    搞定,收工!等等。。。还有什么问题呢?貌似还漏了点东西。嗯。。。还有”事务“!差点被人说成”标题党“啦。我们知道EF对于实体对象的新增(Add)、修改(Update)、删除(Remove)等操作都要等到最后DbContext.SaveChanges()方法执行后才最终提交到数据库执行。而DbContext.Database.ExecuteSqlCommand是绕过EF直接交给数据库去执行了,这样就出现很尴尬的情况:调用Remove(T entity)方法删除一条数据时要执行SaveChanges(),而通过批量删除的方法删除一条或更多的数据就不用经过SaveChanges()直接可以删除了。如何将SaveChanges和ExecuteSqlCommand放到同一个事务来提交呢?这正是下面要继续探讨的。通过查看DbContext.Database命名空间下面的方法,发现了这样一个方法:

    DbContext.Database.UseTransaction(DbTransaction transaction)

    这个方法的摘要说明如下:

    // 摘要: 
    //     Enables the user to pass in a database transaction created outside of the
    //     System.Data.Entity.Database object if you want the Entity Framework to execute
    //     commands within that external transaction.  Alternatively, pass in null to
    //     clear the framework's knowledge of that transaction.

    也就是说,我们可以给EF指定一个外部的事务(参数:transaction)来控制其提交Commands到数据库,这样一来所有由EF产生的Commands的提交都要通过这个外部事务(参数:transaction)来控制了。

    protected override int DoRemove(System.Linq.Expressions.Expression<Func<T, bool>> predicate = null)
    {
        var set = dbContext.Set<T>().AsQueryable();
        set = (predicate == null) ? set : set.Where(predicate);
    
        string sql = set.ToString().Replace("\r", "").Replace("\n", "").Trim();
        if (predicate == null && !string.IsNullOrEmpty(sql) && !string.IsNullOrWhiteSpace(sql))
            sql += " WHERE 1=1";
    
        Regex reg = new Regex("^SELECT[\\s]*(?<Fields>.*)[\\s]*FROM[\\s]*(?<Table>.*)[\\s]*AS[\\s]*(?<TableAlias>.*)[\\s]*WHERE[\\s]*(?<Condition>.*)", RegexOptions.IgnoreCase);
        Match match = reg.Match(sql);
    
        if (!match.Success)
            throw new ArgumentException("Cannot delete this type of collection");
    
        string table = match.Groups["Table"].Value.Trim();
        string tableAlias = match.Groups["TableAlias"].Value.Trim();
        string condition = match.Groups["Condition"].Value.Trim().Replace(tableAlias, table);
    
        string sql1 = string.Format("DELETE FROM {0} WHERE {1}", table, condition);
        int i = 0;
        dbContext.UseTransaction(efContext.Transaction);
        i = dbContext.Database.ExecuteSqlCommand(sql1);
        efContext.Committed = false;
        return i;
    }    
    复制代码

    接下来我们要把DbContext的SaveChanges()和事务Transaction的Commit()封装到同一个方法Commit中去,为此采用Repository模式来实现。具体的实现过程就不细说了,直接给出重构后的代码吧。

     1 public interface IEntityFrameworkRepositoryContext
     2 {
     3     DbContext Context { get; }
     4 
     5     DbTransaction Transaction { get; set; }
     6 
     7     bool Committed { get; set; }
     8 
     9     void Commit();
    10 
    11     void Rollback();
    12 
    13 }

     

     1 public class EntityFrameworkRepositoryContext : IEntityFrameworkRepositoryContext
     2 {
     3     private readonly DbContext efContext;
     4     private readonly object sync = new object();
     5 
     6     public EntityFrameworkRepositoryContext(DbContext efContext)
     7     {
     8         this.efContext = efContext;
     9     }
    10 
    11     public DbContext Context
    12     {
    13         get { return this.efContext; }
    14     }
    15 
    16     private DbTransaction _transaction = null;
    17     public DbTransaction Transaction
    18     {
    19         get
    20         {
    21             if (_transaction == null)
    22             {
    23                 var connection = this.efContext.Database.Connection;
    24                 if (connection.State != ConnectionState.Open)
    25                 {
    26                     connection.Open();
    27                 }
    28                 _transaction = connection.BeginTransaction();
    29             }
    30             return _transaction;
    31         }
    32         set { _transaction = value; }
    33     }
    34 
    35     private bool _committed;
    36     public bool Committed
    37     {
    38         get
    39         {
    40             return _committed;
    41         }    
    42         set
    43         {
    44             _committed = value;
    45         }
    46     }
    47 
    48     public void Commit()
    49     {
    50         if (!Committed)
    51         {
    52             lock (sync)
    53             {
    54                 efContext.SaveChanges();
    55 
    56                 if (_transaction != null)
    57                         _transaction.Commit();
    58             }
    59             Committed = true;
    60         }
    61     }
    62 
    63     public override void Rollback()
    64     {
    65         Committed = false;
    66         if (_transaction != null)
    67         _transaction.Rollback();
    68     }
    69 
    70     //其他方法略。。。
    71 
    72 }            

     

     1 public class EntityFrameworkRepository<T> : IRepository<T>         
     2     where T : class,IEntity
     3 {
     4     private readonly IEntityFrameworkRepositoryContext efContext;
     5 
     6     public EntityFrameworkRepository(IEntityFrameworkRepositoryContext context)
     7     {
     8         this.efContext = context
     9     }
    10 
    11     //Add, Update, Find等等其他方法略。。。
    12 
    13     public int Remove(System.Linq.Expressions.Expression<Func<T, bool>> predicate = null)
    14     {        
    15         var set = efContext.Context.Set<T>().AsQueryable();
    16         set = (predicate == null) ? set : set.Where(predicate);
    17 
    18         string sql = set.ToString().Replace("\r", "").Replace("\n", "").Trim();
    19         if (predicate == null && !string.IsNullOrEmpty(sql) && !string.IsNullOrWhiteSpace(sql))
    20             sql += " WHERE 1=1";
    21 
    22         Regex reg = new Regex("^SELECT[\\s]*(?<Fields>.*)[\\s]*FROM[\\s]*(?<Table>.*)[\\s]*AS[\\s]*(?<TableAlias>.*)[\\s]*WHERE[\\s]*(?<Condition>.*)", RegexOptions.IgnoreCase);
    23         Match match = reg.Match(sql);
    24 
    25         if (!match.Success)
    26             throw new ArgumentException("Cannot delete this type of collection");
    27         string table = match.Groups["Table"].Value.Trim();
    28         string tableAlias = match.Groups["TableAlias"].Value.Trim();
    29         string condition = match.Groups["Condition"].Value.Trim().Replace(tableAlias, table);
    30 
    31         string sql1 = string.Format("DELETE FROM {0} WHERE {1}", table, condition);
    32         int i = 0;
    33         efContext.Context.Database.UseTransaction(efContext.Transaction);
    34         i = efContext.Context.Database.ExecuteSqlCommand(sql1);
    35         efContext.Committed = false;
    36         return i;
    37     }
    38 
    39 }        

     


    1 public interface IRepository<T>
    2 {
    3     void Add(T entity);
    4     void Update(T entity);
    5     void Remove(T entity);
    6     int Remove(Expression<Func<T, bool>> predicate = null);
    7     IQueryable<T> Find(Expression<Func<T, bool>> predicate = null);
    8     ......
    9 }

  • 请您注意

    ·自觉遵守:爱国、守法、自律、真实、文明的原则

    ·尊重网上道德,遵守《全国人大常委会关于维护互联网安全的决定》及中华人民共和国其他各项有关法律法规

    ·严禁发表危害国家安全,破坏民族团结、国家宗教政策和社会稳定,含侮辱、诽谤、教唆、淫秽等内容的作品

    ·承担一切因您的行为而直接或间接导致的民事或刑事法律责任

    ·您在编程中国社区新闻评论发表的作品,本网站有权在网站内保留、转载、引用或者删除

    ·参与本评论即表明您已经阅读并接受上述条款

  • 感谢本文作者
  • 作者头像
  • 昵称:苦丁茶
  • 加入时间:2013/6/19 0:00:00
  • TA的签名
  • 这家伙很懒,虾米都没写
  • +进入TA的空间
  • 以下内容也很赞哦
分享按钮