Linq To SQL 批量更新方法匯總

作者: CoolCode  來源: 博客園  發布時間: 2010-07-12 11:48  閱讀: 17128 次  推薦: 0   原文鏈接   [收藏]  

方法一、官方例子

地球人都知道的，也是不少 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"
                     });

方法原型：

/// 
/// 批量更新
/// 
/// 
///  表
///  查詢條件表達式
///  更新表達式
/// 影響的行數
public static int Update(this Table table, Expression<Funcbool>> predicate, Expression<Func> 
updater) where T : class

實現原理：擴展Table，解釋表達式樹成SQL語句。其中解釋表達式樹包括和更新表達式，后者相對容易處理，例如表達式：

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

解釋成

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

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

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

image

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

//獲取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語句這個設想也不是什么難事了。

/// 
/// 批量更新
/// 
/// 
///  表
///  查詢條件表達式
///  更新表達式
/// 影響的行數
public static int Update(this Table table, Expression<Funcbool>> predicate, Expression<Func>
 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, sqlUp
dateBlock, 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 的賦值語句，兩者拼湊起來即可。

image

該方法比方法二支持更多Lambda表達式（實際上就是所有LinqToSQL支持的）生成SQL條件。

/// 
    /// 批量更新
    /// 
    /// 
    /// 表
    /// 查詢條件表達式
    /// 更新表達式
    /// 影響的行數
    public static int Update(this Table table, Expression<Funcbool>> predicate, Expression<Func
> 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 ", StringCompari
son.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.Forma
t("[{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] = @p1
FROM [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] = @p1
FROM [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

0
0
 
標簽：Linq
 
 

文章列表

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 大師兄 的頭像
    大師兄

    IT工程師數位筆記本

    大師兄 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄