Architecture - Linq-to-SQL and set-based operations: Delete statements
A few weeks back I published a code sample showing an implementation of set-based update operations using Linq-to-SQL. I also promised a followup showing batch deletes, and “insert into…select from” so I guess it is time for a followup. This time I will cover set-based deletes.
To avoid unnecessary duplication the code in this article is an incremental update to the code in my previous article on the subject.
The original set-based update-statement implementation is here:
http://blog.huagati.com/res/index.php/2008/11/05/architecture-linq-to-sql-and-set-based-operations-update-statements/
On a semi-related note: a couple of days ago, Microsoft’s Bart de Smet (author of the B# blog) also recognized the need for language integrated set-based operations in his “Dude, where’s my LINQ DML” blog entry. Although his article left out the actual implementation - he’s instead elaborating on a generic interface to front this functionality. (Although I have to say that I think my implementation results in more clean end-user code than his suggested interface. I know I’m abusing lambdas for the “set”-part but I think it is clear enough anyway…).
Set-based delete statements
I’m not going to elaborate on the rationale for set-based deletes rather than a row-based approach. The reasons for set-based/batch deletes are exactly the same as set-based/batched updates, already covered in the previous article.
Similar to the update statements I will have a couple of overloaded functions allowing for some flexibility in how it is used. The simple form takes a type and a criteria lambda:
dc.Delete<Employee>( (where => where.City.StartsWith("XYZ")) );
The SQL generated by this statement is:
delete from dbo.Employees where EmployeeID in (SELECT [t0].[EmployeeID] FROM [dbo].[Employees] AS [t0] WHERE [t0].[City] LIKE @p0)
Just like the update-statement version, we can use navigation properties, CLR functions etc to set up more complex expressions in a single lambda:
dc.Delete<Customer>( where => where.Orders.Max( od => od.OrderDate ) < DateTime.Now.AddYears(-5) && where.Country == "Ukraine" );
…and because we’re using Linq-to-SQL to generate the SQL, the where clause lambda is translated into nice SQL. After concatenation with the delete part of the statement the line above translates into:
delete from dbo.Customers where CustomerID in (SELECT [t0].[CustomerID] FROM [dbo].[Customers] AS [t0] WHERE ((( SELECT MAX([t1].[OrderDate]) FROM [dbo].[Orders] AS [t1] WHERE [t1].[CustomerID] = [t0].[CustomerID] )) < @p0) AND ([t0].[Country] = @p1))
If we need a more complex filter than what a lambda expression on the existing L2S classes allow, or if we want a more readable lookup query as the base there’s also an overload that accept an IQueryable query:
IQueryable<Customer> customersOfKievRep = from cust in dc.Customers join ord in dc.Orders on cust.CustomerID equals ord.CustomerID join emp in dc.Employees on ord.EmployeeID equals emp.EmployeeID where emp.Region == "Kiev" select cust; dc.Delete<Customer>(customersOfKievRep, LockModeEnum.Row);
The last example also shows the lock mode parameter in use, so the generated SQL will be:
delete from dbo.Customers with (rowlock) where CustomerID in (SELECT [t0].[CustomerID]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
INNER JOIN [dbo].[Employees] AS [t2] ON [t1].[EmployeeID] = ([t2].[EmployeeID])
WHERE [t2].[Region] = @p0)
So. Enough usage examples, on to the implementation.
Implementation
Here’s the implementation. Add it to the code sample from the previous article and you’re ready to use it. As with the previous example, feel free to use it in your own L2S-based software. Remember that you use it at your own risk, don’t blame me if it breaks anything. If you want to translate/reuse any part with LLBLGen, subsonic, NHibernate etc that’s fine too. The only thing not allowed is to convert it for use with Entity Framework, or to re-publish in blog articles without prior written permission. 
public static void Delete<T>(this DataContext dc, Expression<Func<T, bool>> selectionCriteria) { IQueryable<T> baseQuery = ((IQueryable<T>)dc.GetTable(typeof(T))).Where(selectionCriteria); Delete<T>(dc, baseQuery, LockModeEnum.NotSpecified); } public static void Delete<T>(this DataContext dc, IQueryable<T> baseQuery, LockModeEnum lockMode) { //get the type representing the table we're dealing with Type baseType = typeof(T); //get primary key members for the type we're dealing with List<MetaDataMember> primaryKeyMembers = GetPKMembers(dc, baseType); if (primaryKeyMembers.Count == 0) { throw new InvalidOperationException(baseType.Name + " has no primary key members."); } //create a new class containing only the PK members of the table we're going to update MemberBinding[] memberBindings = null; ParameterExpression[] baseTypeParams = new ParameterExpression[] { Expression.Parameter(baseType, "") }; Type pkType = CreatePKClass(baseType, primaryKeyMembers, baseTypeParams[0], out memberBindings); //change the query to only get the PK members IQueryable pkQuery = GetPKQuery(baseQuery, pkType, baseTypeParams, memberBindings); //extract the select query for the primary key members DbCommand pkSelectCommand = dc.GetCommand(pkQuery); string whereClause = null; if (primaryKeyMembers.Count == 1) { whereClause = "where " + primaryKeyMembers[0].Name + " in (" + pkSelectCommand.CommandText + ")"; } else { string ct = pkSelectCommand.CommandText; whereClause = ct.Substring(ct.IndexOf("\r\nfrom ", StringComparison.InvariantCultureIgnoreCase)); } //get the table name string qualifiedTableName = dc.Mapping.GetTable(baseType).TableName; //concatenate the delete statement string deleteStatement = ReplaceSQLParameters( "delete from " + qualifiedTableName + " " + GetLockHint(lockMode) + " " + whereClause); //execute the delete dc.ExecuteCommand(deleteStatement, MergeParameters(pkSelectCommand.Parameters)); }
