Linq To SQL 批次更新方法匯總

 

方法一、官方例子

地球人都知道的，也是不少 Linq To SQL 反對者認為效率低下的一種方法。

NorthwindDataContext db = new NorthwindDataContext();var customers = db.Customers.Where(c => c.CustomerID.StartsWith("BL"));foreach (var customer in customers){    customer.Address = "Guangzhou";    customer.ContactName = "CoolCode";    customer.CompanyName = "Microsoft";}db.SubmitChanges();

 

這種方法必須要查詢出要更新的資料，確實有點不雅，也是Linq To SQL 略顯尷尬的一面。

 

方法二、使用 ExpressionVisitor擷取Lambda運算式產生的SQL條件陳述式

此方法是基於Jeffrey Zhao 的《擴充LINQ to SQL：使用Lambda Expression大量刪除資料》，從該文章得到一點啟發，繼而有了批次更新。使用樣本：

db.Customers.Update(c => c.CustomerID == "Bruce",                     c => new Customer                     {                         Address = "Guangzhou",                         ContactName = "CoolCode",                         CompanyName = "Microsoft"                     });

 

方法原型：

/// <summary>/// 批次更新/// </summary>/// <typeparam name="T"></typeparam>/// <param name="table">表</param>/// <param name="predicate">查詢條件運算式</param>/// <param name="updater">更新運算式</param>/// <returns>影響的行數</returns>public static int Update<T>(this Table<T> table, Expression<Func<T, bool>> predicate, Expression<Func<T,T>> updater) where T : class

 

 

實現原理：擴充Table<T>，解釋運算式樹狀架構成SQL語句。其中解釋運算式樹狀架構包括和更新運算式，後者相對容易處理，例如運算式：

c => new Customer { Address = "Guangzhou", ContactName = "CoolCode", CompanyName = "Microsoft" }

解釋成

Address = @Address, ContactName = @ContactName, CompanyName = @CompanyName

而相應的值（"Guangzhou", "CoolCode",  "Microsoft" ）作為SQL參數傳遞。

實現這一步，其實就是從運算式 Expression<Func<T,T>> 中取到初始化的屬性名稱字和值就可以，具體做法可以使用Expression Tree Viewer來輔助，從可以瞭解到 Expression<Func<T,T>> 的樹形結構。

然後我按上面的結構圖“照葫蘆畫瓢”就得到要更新的屬性名稱字和值：

//擷取Update的指派陳述式var updateMemberExpr = (MemberInitExpression)updater.Body;var updateMemberCollection = updateMemberExpr.Bindings.Cast<MemberAssignment>().Select(c => new{    Name = c.Member.Name,    Value = ((ConstantExpression)c.Expression).Value});

 

而解釋where條件就相對沒這麼輕鬆了。

這裡同 Jeffrey Zhao 的大量刪除一樣，同樣是藉助 ExpressionVisitor 來解釋。ExpressionVisitor 是 Expression Tree 的遍曆器，它自身不會幫你產生任何東西，通過繼承 ExpressionVisitor 就可以取運算式的任何資訊，本文就是通過讓 ConditionBuilder 繼承ExpressionVisitor 而產生 Where 條件的 SQL。

注：Jeffrey Zhao 的大量刪除一文提供的原始碼中，ConditionBuilder 並不支援產生Like操作，如 字串的 StartsWith，Contains，EndsWith 並不能產生這樣的SQL： Like ‘xxx%’, Like ‘%xxx%’ , Like ‘%xxx’ 。我通過分析 ExpressionVisitor ，也不難發現只要override VisitMethodCall 這個方法即可實現上述功能。

protected override Expression VisitMethodCall(MethodCallExpression m){    if (m == null) return m;    string format;    switch (m.Method.Name)    {        case "StartsWith":            format = "({0} LIKE {1}+'%')";            break;        case "Contains":            format = "({0} LIKE '%'+{1}+'%')";            break;        case "EndsWith":            format = "({0} LIKE '%'+{1})";            break;        default:            throw new NotSupportedException(m.NodeType + " is not supported!");    }    this.Visit(m.Object);    this.Visit(m.Arguments[0]);    string right = this.m_conditionParts.Pop();    string left = this.m_conditionParts.Pop();    this.m_conditionParts.Push(String.Format(format, left, right));    return m;}

 

到此刻，已經解決瞭解釋運算式樹狀架構的難題，那麼實現通過運算式樹狀架構產生完整的 Update SQL語句這個設想也不是什麼難事了。

/// <summary>/// 批次更新/// </summary>/// <typeparam name="T"></typeparam>/// <param name="table">表</param>/// <param name="predicate">查詢條件運算式</param>/// <param name="updater">更新運算式</param>/// <returns>影響的行數</returns>public static int Update<T>(this Table<T> table, Expression<Func<T, bool>> predicate, Expression<Func<T,T>> updater) where T : class{    //擷取表名    string tableName = table.Context.Mapping.GetTable(typeof(T)).TableName;    //查詢條件運算式轉換成SQL的條件陳述式    ConditionBuilder builder = new ConditionBuilder();    builder.Build(predicate.Body);    string sqlCondition = builder.Condition;    //擷取Update的指派陳述式    var updateMemberExpr = (MemberInitExpression)updater.Body;    var updateMemberCollection = updateMemberExpr.Bindings.Cast<MemberAssignment>().Select(c => new    {        Name = c.Member.Name,        Value = ((ConstantExpression)c.Expression).Value    });    int i = builder.Arguments.Length;    string sqlUpdateBlock = string.Join(", ", updateMemberCollection.Select(c => string.Format("[{0}]={1}", c.Name, "{" + (i++) + "}")).ToArray());    //SQL命令    string commandText = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, sqlUpdateBlock, sqlCondition);    //擷取SQL參數數組 (包括查詢參數和賦值參數)    var args = builder.Arguments.Union(updateMemberCollection.Select(c => c.Value)).ToArray();    //執行    return table.Context.ExecuteCommand(commandText, args);}

 

例如上面提到的樣本所產生的 Updae SQL語句是：

UPDATE dbo.Customers SET [Address]={1}, [ContactName]={2}, [CompanyName]={3} WHERE ([CustomerID] = {0})

相應參數："Bruce", "Guangzhou", "CoolCode",  "Microsoft"

據不完全統計，實際開發中用的 Update SQL 90%是很簡單的，以上擴充基本上符合要求。

 

方法三、使用 LinqToSQL 自身的解析器來擷取Lambda運算式產生的SQL條件陳述式

該方法與方法二基本上是同一思路，只是在擷取Lambda運算式產生的SQL條件上有點不一樣。

通過 DataContext 的 GetCommand 可以擷取到 DbCommand，所以通過產生的SQL查詢語句中截取Where後面的條件，再用方法二產生Update 的指派陳述式，兩者拼湊起來即可。

該方法比方法二支援更多Lambda運算式（實際上就是所有LinqToSQL支援的）產生SQL條件。

/// <summary>    /// 批次更新    /// </summary>    /// <typeparam name="T"></typeparam>    /// <param name="table">表</param>    /// <param name="predicate">查詢條件運算式</param>    /// <param name="updater">更新運算式</param>    /// <returns>影響的行數</returns>    public static int Update<T>(this Table<T> table, Expression<Func<T, bool>> predicate, Expression<Func<T,T>> updater) where T : class    {        //擷取表名        string tableName = table.Context.Mapping.GetTable(typeof(T)).TableName;        DbCommand command = table.Context.GetCommand(table.Where(predicate));        string sqlCondition = command.CommandText;        sqlCondition = sqlCondition.Substring(sqlCondition.LastIndexOf("WHERE ", StringComparison.InvariantCultureIgnoreCase) + 6);        //擷取Update的指派陳述式        var updateMemberExpr = (MemberInitExpression)updater.Body;        var updateMemberCollection = updateMemberExpr.Bindings.Cast<MemberAssignment>().Select(c =>        {            var p = command.CreateParameter();            p.ParameterName = c.Member.Name;            p.Value = ((ConstantExpression)c.Expression).Value;            return p;        })        .ToArray();        string sqlUpdateBlock = string.Join(", ", updateMemberCollection.Select(c => string.Format("[{0}]=@{0}", c.ParameterName)).ToArray());        //SQL命令        string commandText = string.Format("UPDATE {0} SET {1} FROM {0} AS t0 WHERE {2}", tableName, sqlUpdateBlock, sqlCondition);        //擷取SQL參數數組 (包括查詢參數和賦值參數)        command.Parameters.AddRange(updateMemberCollection);        command.CommandText = commandText;         //執行         try        {            if (command.Connection.State != ConnectionState.Open)            {                command.Connection.Open();            }            return command.ExecuteNonQuery();        }        finally        {            command.Connection.Close();            command.Dispose();        }    }

 

同樣使用文章開頭的樣本，產生的 Update SQL 跟方法二略有不同：

UPDATE dbo.Customers SET [Address]=@Address, [ContactName]=@ContactName, [CompanyName]=@CompanyName FROM dbo.Customers AS t0 WHERE [t0].[CustomerID] = @p0

 

方法四、支援多表關聯的複雜條件

要知道，前面提到的方法二和三都不支援多表關聯的複雜條件。可以用一個樣本讓大家更清楚為什麼——

例如，更新CustomerID=“Bruce”的使用者的所有訂單的送貨日前是一個月後。

db.Orders.Update(c => c.Customer.CustomerID == "Bruce",                    c => new Order                    {                         ShippedDate =  DateTime.Now.AddMonths(1)                    });

 

應該產生的 Update SQL 陳述式是：

UPDATE [dbo].[Orders] SET [ShippedDate] = @p1FROM [dbo].[Orders] AS [t0]    LEFT OUTER JOIN [dbo].[Customers] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]WHERE [t1].[CustomerID] = @p0--@p0 = 'Bruce', @p1 = '2010-08-11'

 

但遺憾的是無論用方法二或三都會拋異常，因為兩者皆沒法解釋多表關聯產生的語句： “LEFT OUTER JOIN [dbo].[Customers] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID] ”

一位叫 Terry Aney 的朋友在《Batch Updates and Deletes with LINQ to SQL》這篇博文中解決了這個問題。使用他提供的UpdateBatch 方法產生的 Update SQL 是：

UPDATE [dbo].[Orders]    SET [ShippedDate] = @p1FROM [dbo].[Orders] AS j0 INNER JOIN (    SELECT [t0].[OrderID]    FROM [dbo].[Orders] AS [t0]        LEFT OUTER JOIN [dbo].[Customers] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]    WHERE [t1].[CustomerID] = @p0) AS j1 ON (j0.[OrderID] = j1.[OrderID])-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Bruce]-- @p1: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2010/8/11 19:51:59]

 

雖然跟我剛才手寫的SQL略有不同，但 Update 的邏輯是對的。有興趣的朋友不妨試試，Terry Aney在他的文章裡有很詳盡的介紹，這裡不再詳述。

相關博文：

Batch Updates and Deletes with LINQ to SQL
LINQ to SQL Batch Updates/Deletes: Fix for 'Could not translate expression'
I've Left Query Analyzer Hell For LINQPad Heaven

 

總結

Linq To SQL 有很多地方值得探索的，Expression Tree 是探索的基礎， 嘿嘿！

完整代碼（內含Terry Aney 的代碼）

Linq2SQL批次更新.rar