LINQ to SQL FAQ – Bulk Updates and Deletes

LINQ to SQL is mainly aimed at reading, querying grouping and sorting data. It can update, insert and delete individual records, but there is no direct support for bulk operations that are common in SQL. For example, there is no LINQ to SQL statement for this T-SQL:

DELETE Products WHERE ProductCategory=’Example’;

The basic solution is to do the SELECT and then delete – e.g. :

var toDelete = from p in db.Products
                      where p.ProductCategory=="example"
                      select p;
db.Products.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();

The drawback of this method is that each of the records has to be loaded over the wire – which in this example is not necessary – imagine if the delete is a million records, and you’ll understand this approach is not scalable or even ideal.

Stored Procedures

A much better approach is of course to use a parameterised stored procedure, e.g.

db.SPDeleteProductsByCategory("Example");

and this then becomes accessible through the data context.

Direct SQL

The final alternative is to use a direct SQL command, executed through the ExecuteCommand method of the DataContext. This supports parameters, so you could equally do this:

const string DeleteSQL = "DELETE Products WHERE ProductCategory={0}";
db.ExecuteCommand(DeleteSQL, "Example");

Note the {0} entry in the SQL command – that’s the parameter, just like it is in string formatting. "Hold on!" I hear web security experts shout, you’re not passing an unsafe string into SQL are you? That’s so dangerous! What if someone types malicious values into a search box that does horrible things?

Well, breathe easy – this is covered. Those parameters are properly parameterised by the ExecuteCommand – so what is generated is "… WHERE ProductCategory = @p1" and the value passed as a SQL parameter.

Note however there is a bug with this ExecuteCommand (and also I believe for ExecuteQuery) where nulls are not properly handled by the function. See this bug report for more details.

Third Party Extensions to LINQ to SQL

Some people have been developing .Update and .Delete extensions to LINQ to SQL. See Aney’s blog and Zhao’s blog for more information.

Advertisements

One thought on “LINQ to SQL FAQ – Bulk Updates and Deletes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s