您的位置:知识库 » .NET技术

Linq To SQL 批量更新方法汇总

作者: CoolCode  来源: 博客园  发布时间: 2010-07-12 11:48  阅读: 17823 次  推荐: 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

.NET技术热门文章

    .NET技术最新文章

      最新新闻

        热门新闻