Linq to SQL: Delete an entity using Primary Key only

Linq to Sql does not come with a function like .Delete(ID) which allows you to delete an entity using it’s primary key. You have to first get the object that you want to delete and then call .DeleteOnSubmit(obj) to queue it for delete. Then you have to call DataContext.SubmitChanges() to play the delete queries on database. So, how to delete object without getting them from database and avoid database roundtrip?

Delete an object without getting it - Linq to Sql

You can call this function using DeleteByPK<Employee, int>(10, dataContext);

First type is the entity type and second one is the type of the primary key. If your object’s primary key is a Guid field, specify Guid instead of int.

How it works:

  • It figures out the table name and the primary key field name from the entity
  • Then it uses the table name and primary key field name to build a DELETE query

Figuring out the table name and primary key field name is a bit hard. There’s some reflection involved. The GetTableDef<TSource>() returns the table name and primary key field name for an entity.

Every Linq Entity class is decorated with a Table attribute that has the table name:

Lint entity declaration

Then the primary key field is decorated with a Column attribute with IsPrimaryKey = true.

Primary Key field has Column attribute with IsPrimaryKey = true

So, using reflection we can figure out the table name and the primary key property and the field name.

Here’s the code that does it:

Using reflection find the Table attribute and the Column attribute

Before you scream “Reflection is SLOW!!!!” the definition is cached. So, reflection is used only once per appDomain per entity. Subsequent call is just a dictionary lookup away, which is as fast as it can get.

You can also delete a collection of object without ever getting any one of them. The the following function to delete a whole bunch of objects:

Delete a list of objects using Linq to SQL

The code is available here:

http://code.msdn.microsoft.com/DeleteEntitiesLinq

kick it on DotNetKicks.com

3 Comments

  • how about a sample of to actually use this on a datacontext?

  • This is a really useful piece of code. Thanks for posting it.

  • Very nice and useful.

    I tweaked 2 lines in your code to be an Extension of System.Data.Linq.Table; therefore, having it available for all of my Table definitions.

    Only tweak was to remove the table definition:

    Table&lt;TSource&gt; table = dc.GetTable&lt;TSource&gt;();

    And define it as part of the extension method:

    public static void DeleteByPK&lt;TSource, TPK&gt;(this Table&lt;TSource&gt; table, TPK pk, DataContext dc)
    where TSource : class

Comments have been disabled for this content.