扩展LINQ to Entity：使用Lambda Expression批量删除数据------让微软帮我们生成T-SQL语句

　　我们在使用EF（ADO.NET Entity Framework）的时候，大部分的的是查询操作，当然Insert，Update，Delete是没有问题的。但如果我们想要批量删除数据，那该怎么做呢？然后就尝试着去寻找一些方法，而后看到老赵的《扩展LINQ to SQL：使用Lambda Expression批量删除数据》，从中获益匪浅，学到不少东西，非常感谢。老赵的方法中需要解析Lambda Expression（当然也是正规的必经之路），来生成where条件及整个T-SQL语句，那么解析Expression需要考虑到很多方面，所以要实现一个比较完善的解析方法，目前来说比较复杂，要花费大量的时间和精力。就在前几天寻找方法的时候，偶得灵感，故此文记之，希望对大家有所帮助，如有不足，欢迎提出来讨论。

　　下面就已知的方法列举出来，以供比较。

第一种解决办法：使用EF的DeleteObject方法。

NorthwindEntities db = new NorthwindEntities();
            //1
            var deleteQuery = db.Products.Where(p => p.ProductID < -10
                                  && p.Categories.CategoryName.Contains("test"));

            foreach (var item in deleteQuery)
            {
                db.DeleteObject(item);
            }
            db.SaveChanges();

这种方法，对于业务中使用（少量的数据），应该没有大的问题，但对于大量的批量删除就有很大的性能问题。因为它是将所有符合条件的对象都查询出来到内存，然后标记删除，在进行提交数据库执行T-SQL删除的。换句话说，对于有些同学认为，第一步查询出对象，完全没有必要，但EF是需要管理这些对象的。

第二种解决办法：直接使用ADO.NET来执行T-SQL删除。

这种方法，会直接写删除的SQL语句，就像老赵所说“在程序里出现直接的SQL语句是一件很丑陋的事情”（我也比较认同，但有些特殊情况，用用也不错，哈哈）。同时因为是SQL

字符串，就损失了编译时的错误检测（某个字母写错误，直到运行时才会检测到），而且可读性或者维护性降低了，写起来不像linq那么爽了。

 

第三种解决方法：就是老赵给出的解决方案——使用解析Expression的方式，生成T-SQL语句，然后通过ADO.NET执行，它直接综合了前两种方法的优点。其中关键就是解析表达式，可解析Lambda Expression，没有一定的功力是写不出比较完善的解析的，而且需要花费大量的时间精力。前天灵光一闪，想到微软不是给我们实现好生成T-SQL的代码了吗，我们可以直接使用ObjectQuery的ToTraceString方法来得到SQL语句呀，同时使用ObjectQuery的Where方法来过滤条件，然后生成删除的SQL语句。

比如：var selectSql = (source.Where(predicate) as ObjectQuery).ToTraceString();

那么这个查询的SQL语句，如何转化成删除的呢？看到这个语句后，你或许就有办法了，如：

SELECT 
1 AS [C1], 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[ProductName] AS [ProductName], 
[Extent1].[SupplierID] AS [SupplierID], 
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], 
[Extent1].[UnitPrice] AS [UnitPrice], 
[Extent1].[UnitsInStock] AS [UnitsInStock], 
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 
[Extent1].[ReorderLevel] AS [ReorderLevel], 
[Extent1].[Discontinued] AS [Discontinued], 
[Extent1].[CategoryID] AS [CategoryID]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[ProductID] < -10

然后发现这个语句中，给表起了个别名，如果去掉别名，可以，不去掉可以吗？于是google下，发现delete语句是可以起别名的，比如：delete tab from table1 tab where tab.Field1=1 and tab.Field2...。这下好办了，直接得到这个语句中的别名，然后不久可以生成删除的SQL语句了。

 

下面给出完整的实现，请查考。

namespace SoftLibrary.Extensions
{
    public static class ObjectQueryExtension
    {
        /// <summary>
        /// 执行批量删除操作。直接使用 ado.net
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="source"></param>
        /// <param name="predicate">条件</param>
        /// <returns></returns>
        public static int Delete<TEntity>(this ObjectQuery<TEntity> source, Expression<Func<TEntity, bool>> predicate) 
                                                               where TEntity : global::System.Data.Objects.DataClasses.EntityObject
        {
            var selectSql = (source.Where(predicate) as ObjectQuery).ToTraceString();

            //
            int startIndex = selectSql.IndexOf(",");
            int endIndex = selectSql.IndexOf(".");
            string tableAlias = selectSql.Substring(startIndex + 1, endIndex - startIndex - 1);//get table alias
            startIndex = selectSql.IndexOf("FROM");
            string deleteSql = "DELETE " + tableAlias + " " + selectSql.Substring(startIndex);
            //Gets the string used to open a SQL Server database
            string connectionString = ((source as ObjectQuery).Context.Connection as EntityConnection).StoreConnection.ConnectionString;

            return ExecuteNonQuery(connectionString, deleteSql);
        }

        /// <summary>
        /// 执行T-sql 语句
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private static int ExecuteNonQuery(string connectionString, string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.AddRange(parameters);
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = sql;

                if (conn.State != System.Data.ConnectionState.Open)
                {
                    conn.Open();
                }
                return cmd.ExecuteNonQuery();
            }
        }
    }
}

调用示例代码：

NorthwindEntities db = new NorthwindEntities();
db.Products.Delete(p => p.ProductID < -10);

db.Products.Delete(p => p.ProductID < -10
                                  && p.Categories.CategoryName.Contains("test"));

这样以来，就兼得了前两种方法的优点，是不是写批量删除很爽了。至于这种方法有什么优点，有什么缺点，请各位发表意见吧。

对于如果像这样封装了一个业务层基类

public class BLLBase<TEntity, TObjectContext>
        where TObjectContext : global::System.Data.Objects.ObjectContext
        where TEntity : global::System.Data.Objects.DataClasses.EntityObject
    {}

那么其中如何实现这个批量的Delete呢？我想你应该是没有问题的，去尝试下吧。

 

从网上还看到另一种做法，从Metadata中获取表名，然后生成T-SQL，思路还是和第三种思路类似。有兴趣的可以看一看，学习一下。

 

　　由于时间关系，先写到这里吧。希望大家踊跃留言，讨论发表观点，有不足之处，还请指正，谢谢！