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();
db.ExecuteCommand(
"DELETE FROM Item WHERE [CreateTime] < {0}",
DateTime.UtcNow.AddMonths(-1));
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:
- 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).
- 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}").
- 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();
db.Items.Update(
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