A summary of the batch update Methods of LINQ to SQL

 

Method 1. Official example

As we all know, it is also a method that many opponents of the LINQ to SQL think is inefficient.

 
NorthwinddatacontextDB =NewNorthwinddatacontext();VaRCustomers = dB. Customers. Where (C => C. customerid. startswith ("Bl"));Foreach(VaRCustomerInCustomers) {customer. Address ="Guangzhou"; Customer. contactname ="Coolcode"; Customer. companyName ="Microsoft";} DB. submitchanges ();
 

 

This method must be used to query the data to be updated. It is indeed a bit indecent, and it is also a bit embarrassing for the use of LINQ to SQL.

 

Method 2: Use Expressionvisitor Obtain the SQL condition statements generated by lambda expressions

This method is based on Jeffrey Zhao's extended LINQ to SQL: Using Lambda expression to batch delete data.ArticleI was inspired by the batch update feature. Example:

 
DB. Customers. Update (C => C. customerid ="Bruce", C =>NewCustomer{Address ="Guangzhou", Contactname ="Coolcode", CompanyName ="Microsoft"});
 

 

Method prototype:

 /// <Summary> ///  Batch update /// </Summary> /// <typeparam name = "T"> </typeparam> /// <Param name = "table">  Table  </Param> /// <Param name = "Predicate">  Query condition expression  </Param> /// <Param name = "Updater">  Update expression  </Param> /// <returns>  Number of affected rows  </Returns>  Public static int Update <t> ( This  Table <T> table, Expression <Func <T, Bool > Predicate, Expression < Func <T, t> Updater) Where T: Class 

 

 

Implementation Principle: Expand table <t> and explain the expression to construct an SQL statement. The expression tree contains and updates expressions, which are relatively easy to process, such as expressions:

C =>NewCustomer{Address ="Guangzhou", Contactname ="Coolcode", CompanyName ="Microsoft"}

Interpreted

Address =@ Address, Contactname =@ Contactname, CompanyName =@ CompanyName

And the corresponding value ("Guangzhou ","Coolcode","Microsoft") Is passed as an SQL parameter.

To achieve this step, we actually useExpression<Func<T, t> the initialized attribute name and value can be obtained. You can use Expression Tree viewer to help you learn more.Expression<Func<T, t>.

Then, based on the above structure, I will get the name and value of the property to be updated:

// Obtain the update value assignment statementVaRUpdatememberexpr = (Memberinitexpression) Updater. Body;VaRUpdatemembercollection = updatememberexpr. bindings. Cast <Memberassignment> (). Select (C =>New{Name = C. member. Name, value = ((Constantexpression) C. expression). Value });
 

 

The where condition is not so easy to explain.

This is also explained by expressionvisitor, just like the batch deletion by Jeffrey Zhao. Expressionvisitor is the traversal tool of the Expression Tree. It does not generate anything for you. By inheriting the expressionvisitor, it can take any information of the expression, this article describes how to generate an SQL where condition by letting conditionbuilder inherit the expressionvisitor.

Note: Jeffrey Zhao's batch Delete article providedSource codeIn, conditionbuilder does not support generating like operations, such as startswith, contains, and endswith of strings. Such SQL statements: Like 'xxx % ', like' % xxx % ', like '% XXX '. By analyzing the expressionvisitor, it is not difficult to find that the above functions can be implemented as long as the override visitmethodcall method is used.

 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 ;}

 

Now, the problem of interpreting the expression tree has been solved. It is not difficult to generate a complete update SQL statement through the Expression Tree.

 /// <Summary> ///  Batch update  /// </Summary> /// <typeparam name = "T"> </typeparam> /// <Param name = "table">  Table  </Param> /// <Param name = "Predicate">  Query condition expression  </Param> /// <Param name = "Updater">  Update expression  </Param> /// <returns>  Number of affected rows </Returns>  Public static int Update <t> ( This  Table <T> table, Expression < Func <T, Bool > Predicate, Expression < Func <T, t> Updater) Where T: Class { // Obtain the table name  String Tablename = table. Context. Mapping. gettable ( Typeof (T). tablename; // Convert the query condition expression to the SQL Condition Statement  Conditionbuilder Builder = New  Conditionbuilder (); Builder. Build (predicate. Body ); String Sqlcondition = builder. condition; // Obtain the update value assignment statement  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 command  String Commandtext = String . Format ( "Update {0} set {1} Where {2 }" , Tablename, sqlupdateblock, sqlcondition ); // Obtain the SQL parameter array (including query parameters and value assignment parameters)  VaR ARGs = builder. Arguments. Union (updatemembercollection. Select (C => C. Value). toarray (); // Execute  Return Table. Context. executecommand (commandtext, argS );}

 

For example, the updae SQL statement generated in the preceding example is:

Update DBO. Customers set [address] = {1}, [contactname] = {2}, [companyName] = {3} Where ([customerid] = {0 })

Parameters:"Bruce ","Guangzhou ","Coolcode","Microsoft"

According to incomplete statistics, the update SQL 90% in actual development is very simple, and the above extensions basically meet the requirements.

 

Method 3: Use Linqtosql The parser to obtain the SQL Condition Statement generated by the lambda expression.

This method is basically the same as method 2, but it is a little different in obtaining the SQL conditions generated by lambda expressions.

Dbcommand can be obtained through getcommand of datacontext. Therefore, the conditions after where are intercepted in the generated SQL query statement, and then the update value assignment statement is generated using method 2. The two can be pieced together.

This method supports more lambda expressions than method 2 (actually all supported by linqtosql) to generate SQL conditions.

 /// <Summary> ///  Batch update /// </Summary> /// <typeparam name = "T"> </typeparam> /// <Param name = "table">  Table  </Param> /// <Param name = "Predicate">  Query condition expression  </Param> /// <Param name = "Updater">  Update expression  </Param> /// <returns>  Number of affected rows  </Returns>  Public static int Update <t> ( This  Table <T> table,Expression < Func <T, Bool > Predicate, Expression < Func <T, t> Updater) Where T: Class { // Obtain the table name  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 ); // Obtain the update value assignment statement  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 command  String Commandtext = String . Format ( "Update {0} set {1} from {0} As t0 where {2 }" , Tablename, sqlupdateblock, sqlcondition ); // Obtain the SQL parameter array (including query parameters and value assignment parameters) Command. Parameters. addrange (updatemembercollection); command. commandtext = commandtext; // Execute  Try { If (Command. Connection. State! = Connectionstate . Open) {command. Connection. open ();} Return Command. executenonquery ();} Finally {Command. Connection. Close (); command. Dispose ();}}

 

In the example starting with the document, the generated update SQL statement is slightly different from Method 2:

Update DBO. customers set [address] = @ address, [contactname] = @ contactname, [companyName] = @ companyName from DBO. MERs as t0 where [t0]. [customerid] = @ P0

 

Method 4: complex conditions supporting multi-Table Association

You must know that the methods 2 and 3 mentioned above do not support complex conditions for multi-table join. We can use an example to show you why --

For example, the shipping date for all orders of the user who updated customerid = "Bruce" is one month later.

 
DB. Orders. Update (C => C. Customer. customerid ="Bruce", C =>NewOrder{Shippeddate =Datetime. Now. addmonths (1 )});
 

 

The generated update SQL statement is:

 
Update[DBO].[Orders]Set[Shippeddate]=@ P1From[DBO].[Orders]As[T0]Left Outer Join[DBO].[MERs]As[T1]On[T1].[Customerid]=[T0].[Customerid]Where[T1].[Customerid]=@ P0-- @ P0 = 'Bruce ', @ p1 = '2017-08-11'
 

 

However, no matter method 2 or 3 is used, an exception is thrown, because neither of them can explain the statements generated by multi-Table Association:"Left Outer Join[DBO].[MERs]As[T1]On[T1].[Customerid]=[T0].[Customerid]"

A friend named Terry aney solved this problem in the blog "batch updates and deletes with LINQ to SQL. The update SQL statement generated using the updatebatch method provided by him is:

 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] . [MERs] 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) [19:51:59] 

 

Although it is slightly different from the SQL statement I wrote just now, the update logic is correct. If you are interested, try again. Terry aney has a very detailed introduction in his article, which will not be detailed here.

Related blog posts:

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

Summary

Expression Tree is the basis for exploration!

CompleteCode(InclusionTerry aneyCode)

Linq2sqlbatch update .rar