LINQ to SQL: opportunistic version of batch Deletion

I saw Jeffrey the other day.Extended LINQ to SQL: Batch delete data using lambda expressions. To be honest, parse the part where the expression generates the where ConditionCodeI basically didn't understand it (the fact is that there are too many code and I really don't have the patience to read it ).

According to the original design of LINQ to SQL, dbcommand obtained by parsing query should be performed by sqlprovider, but now this sqlprovider is only from ireaderprovider (in fact, Ms has not designed an iupdateprovider or ideleteprovider ), therefore, it is only common for select. We have to be self-reliant in datacontext.

But now that you have the ireaderprovider that can generate the SELECT statement, can you get the delete statement if you modify the SELECT statement slightly! Basic Ideas:

Public static int Deleteall <tentity> ( This Table <Tentity> table, Expression < Func <Tentity, Bool > Predicate)
Where Tentity: Class
{
Iqueryable Query = table. Where (predicate );
Dbcommand COM = Dc. getcommand (query );

// Todo: Modify the SQL statement

ReturnCom. executenonquery ();
 }
}

Here, we use the query generated by where to get the command directly. The obtained SQL statement is roughly as follows:

SelectFields... FromTablenameAsTablealiasWhereCondition

 

Our goal is to transform:

Delete fromTablenameWhereCondition

 

The key to getting tablename is to use regular expressions. However, there is also a defect that only expression can be used for deletion and not LINQ query. For example, I want:

VaR Query = From Item In Context. Items
Where Item. Name. startswith ( "XX" )
Select Item;
Context. deleteall (query );

It seems that deleteall should be placed in datacontext, but there is a risk that it may accept select statements that cannot be converted, and adding judgment is essential.

The final steps are as follows:

   Public static class  Datacontextex  {  Public static int  Deleteall (  This  Datacontext DC,  Iqueryable  Query ){  Dbcommand  COM = Dc. getcommand (query );  RegEx  Reg =  New  RegEx  (  "^ Select [\ s] * (? <Fields>. *) [\ s] * from [\ s] * (? <Table>. *) [\ s] * as [\ s] * (? <Tablealias>. *) [\ s] * Where [\ s] * (? <Condition> .*)" ,  Regexoptions  . Ignorecase );  Match  Match = reg. Match (COM. commandtext );  If  (! Match. Success)  Throw new  Argumentexception  (  "Cannot delete this type of Collection"  ); String  Table = match. Groups [  "Table"  ]. Value. Trim ();  String  Tablealias = match. Groups [  "Tablealias"  ]. Value. Trim ();  String  Condition = match. Groups [  "Condition"  ]. Value. Trim (). Replace (tablealias, table); com. commandtext = String  . Format (  "Delete from {0} Where {1 }"  , Table, condition );  If  (COM. Connection. State! = System. Data.  Connectionstate  . Open) COM. Connection. open ();  Return  Com. executenonquery ();}  Public static int Deleteall <tentity> (  This  Table  <Tentity> table,  Expression  <  Func  <Tentity,  Bool  > Predicate)  Where  Tentity:  Class {  Iqueryable  Query = table. Where (predicate );  Return  Table. Context. deleteall (query );}} 

 

Note: The reg expression is taken from msdn forum.