LINQ to SQL Extension: Batch Deletion with Lambda Expression

Batch deletion in the O/R Mapping frameworks is always depressing. We need to query all the entities we want to delete from the database, pass them to the DeleteOnSubmit or DeleteAllOnSubmit methods of DataContext, and finally invoke SubmitChanges to delete the records form database. In this case, we will cost an additional query and send lots of "DELETE" commands to database. How wasteful!

So if we want to make batch deletion with LINQ to SQL, we'll probably execute a SQL command in souce code:

ItemDataContext db = new ItemDataContext();
    "DELETE FROM Item WHERE [CreateTime] < {0}",

But in my opinion, it's ugly if we put SQL commands in source code. Ideally, the developers should focus on the business logics, data access logics or domain models, and the database operations are encapsulated by some other layers. In the days without O/R Mapping frameworks, we always defines Stored Procedures in databases and make calls in the source code. And finally we get O/R Mapping frameworks such as Hibernate and LINQ to SQL, so in most cases we could write code to query the database without dealing with SQL commands.

But batch deletion is one of the exceptions, as I said minutes ago. To avoid making batch deletion with SQL commands, I build an extension method for LINQ to SQL. Now we can delete the records from database with lambda expression:

ItemDataContext db = new ItemDataContext();
db.Items.Delete(item => item.CreateTime < DateTime.UtcNow.AddMonths(-1));

Of course, we could use more complicated expressions to indicate the entities to delete:

ItemDataContext db = new ItemDataContext();
db.Items.Delete(item =>
    item.CreateTime < DateTime.UtcNow.AddMonths(-1) ||
    item.ViewCount < item.CommentCount && item.UserName != "jeffz"); 

For the extensions I built for LINQ to SQL, most of them are based on DataContext. But now I extended the Table<TEntity> class. The implementation is not so difficult as I thought before I finish it. The key part of extending with LINQ/Lamba Expression is parsing the Expression Tree, but in this case, most of the expressions are binary expression and that's almost all of the extension - I just implemented the common used conditions. For instance, it doesn't support the expressions like "item.Introduction.Length < 10" and it should be converted to the use of LEN function in an completely implemented version.

The "Where Condition" in the SQL command converted from lambda expression is built in 3 steps, you can see the corresponding classes in the source code for more details:

  1. Use PartialEvaluator class to replce the expressions which can be evaluated directly with the constant expressions indicated the calculated results (E.g., replace the expression "3 * 3" with the constant expression of "9", or replace the variables with the values of them).
  2. Use ConditionBuilder class to collect all the constants in the expression tree as parameters for te SQL command, and generate the SQL where condition with the parameters' placeholders in it (E.g., "[CreateTime] < {0} AND [UserName] <> {1}").
  3. Use DataContext.ExecuteCommand method to execute the whole SQL command with parameters and return the number of effected records.

Now we have the function of batch deletion but what about batch updating? Actually I'm working on it. I'm going to let the developers updating the records in the database like this:

ItemDataContext db = new ItemDataContext();
    item => new Item
        Introduction = item.Title + "Hello World",
        ViewCount = item.ViewCount + 1,
    }, // SET [Introduction] = [Title] + 'Hello World', ...
    item => item.CommentCount > 100 /* WHERE condition */);

For more details about the batch deletion extionsion please see the attachment.

Chinese version of this post


  • An alternative approach would be to use the GetCommand method of the DataContext, and alter the command text to delete the matching records. That way, any condition supported by LINQ to SQL is supported.

    The delete command will need to look something like:

    FROM TableName
    FROM TableName As D0 INNER JOIN (SELECT ....) As D1
    ON (D0.Key1 = D1.Key1 And D0.Key2 = D1.Key2 ...)

    where "SELECT ..." is the original command text, and the "ON" clause contains all primary key columns for the table. You can find the primary key columns by using reflection on the entity type:

    from p in entityType.GetProperties()
    from ColumnAttribute a in p.GetCustomAttributes(typeof(ColumnAttribute), false)
    where a.IsPrimaryKey
    select new
    Property = p,
    Column = a

  • @Richard
    That's an interesting idea.

  • If there code available for download at all?

  • Looking at the view from 30,000 feet - Is it only me who thinks that much of Linq is a giant step backward? I mean, comeon, deleting/inserting multiple records is an extremely common task (in my code anyway) and is accomplished using a trivial SQL statement in ADO.NET. This shouldn't be an exercise in academia!

  • very good.
    thanks jeffery zhao.

  • How does this support using a Delete statement with an "IN" clause.
    Like: "DELETE FROM tblMyTable WHERE col1 IN (val1,val2, val3, val4)"

  • Using the extension from my url above, you could do something like...

    tblMyTables.DeleteBatch( t => ( new [] { val1, val2, val3, val4 } ).Contains( t.col1 ) );

  • Awesome job - the code is great.

    I tried implementing it for v4.0 and got everything working great except for table mapping - evidently EF 4.0 does not have any properties for accessing for table mappings (due to the fact that multiple database tables can be mapped to a single entity). Any ideas?

  • I liked @Richard's idea.
    LINQ to SQL already knows how to generate SELECTs T-SQL from lambda expressions.
    Why not reuse that to generate WHERE clauses for UPDATEs and DELETEs, and don't forget batch INSERTs too (like: INSERT INTO (Col1, Col2,...) SELECT Var1 as Col1, Var2 as Col2, ... FROM Table WHERE )

    Am I completely wrong here?

  • Linq to sql extension batch deletion by lambda expression.. OMG! :)

  • Linq to sql extension batch deletion by lambda expression.. Huh, really? :)

  • @Mitchell said: I tried implementing it for v4.0 and got everything working great except for table mapping - evidently EF 4.0 does not have any properties for accessing for table mappings (due to the fact that multiple database tables can be mapped to a single entity). Any ideas?

    I am having the same problem. Any ideas/workarounds?

  • nn6JUu Muchos Gracias for your post. Cool.

  • qxLx0x Awesome post.Much thanks again. Really Cool.

  • C36GSC Very neat blog.Really looking forward to read more. Really Great.

  • Normally I don't learn article on blogs, but I would like to say that this write-up very pressured me to take a look at and do so! Your writing style has been surprised me. Thanks, very great post.

Comments have been disabled for this content.