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?
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:
Then the primary key field is decorated with a 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:
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:
The code is available here:
http://code.msdn.microsoft.com/DeleteEntitiesLinq
