LINQ To SQL: Delete entity (by ID) with one query

Question

I've been working with LINQ To SQL for a little while now and when it comes to removing an entity from the DB, I've always called the table's .DeleteOnSubmit and passed in the entity. Sometimes I've found myself writing something like:

db.Users.DeleteOnSubmit(db.Users.Where(c => c.ID == xyz).Select(c => c).Single());

This of course causes two queries. One to get the entity matching the criteria and then another to delete it. Often I have the ID of the record I need removing and I am wondering if there is a more direct way to remove a row from a table via the ID only?

Regards,

Martin.

Answer 1

I don't believe Linq to Sql can do this natively, although writing a stored procedure would give you what you want, with syntax like:

db.spDeleteUserById(id);

This would requre writing a stored procedure for each table, but it's a pretty trivial amount of SQL.

---

Check out this blog post, in which the author has created an extension method that generates its own delete statement. You might be able to take this concept and run with it to create a delete routine that doesn't require a select. The downside is, you might end up with an extension method that is specific to your own schema.

I understand that you want to keep your logic out of the database layer, but in this case I think it would be far simpler to go the stored proc route. It wouldn't be subject to the problems with tightly coupled data access logic, since deleting a row by id isn't likely to ever need refactoring.

Answer 2

Hi,

Here is a solution...

public static void DeleteByPK<TSource, TPK>(TPK pk, DataContext dc)
  where TSource : class
{
  Table<TSource> table = dc.GetTable<TSource>();
  TableDef tableDef = GetTableDef<TSource>();

  dc.ExecuteCommand("DELETE FROM [" + tableDef.TableName
    + "] WHERE [" = tableDef.PKFieldName + "] = {0}", pk);
}

It's not my code! See for explaination - http://msmvps.com/blogs/omar/archive/2008/10/30/linq-to-sql-delete-an-entity-using-primary-key-only.aspx

Hope this helps.

Answer 3

The following blog article may be of interest to you. It allows not only that, but also more complex SQL-DML delete statements to be generated using a single lambda expression as the criteria:

http://blog.huagati.com/res/index.php/2008/11/25/architecture-linq-to-sql-and-set-based-operations-delete-statements/

...the sample code in the article above (set-based deletes) depends on the sample published in this article (set based updates):

http://blog.huagati.com/res/index.php/2008/11/05/architecture-linq-to-sql-and-set-based-operations-update-statements/