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(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()
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

Share on Tumblr

19 thoughts on “Linq to SQL: Delete an entity using Primary Key only”

  1. I did something similar but as extension method of Table. I think should be mentioned that if DataContext is not readonly then single item query can return cached entity.

  2. Can't you just attach a constructed object to the datacontext and then call DeleteOnSubmit.

    Entity ent = new Entity();

    Entity.pk = 1;

    datacontext.Attach(ent);

    datacontext.Entity.DeleteOnSubmit(ent);

  3. No! doing so will simply raise row not found or some thing similar as the data is not matched…

    “Can't you just attach a constructed object to the datacontext and then call DeleteOnSubmit.

    Entity ent = new Entity();

    Entity.pk = 1;

    datacontext.Attach(ent);

    datacontext.Entity.DeleteOnSubmit(ent);”

  5. This will fail with composite keys but more worryingly is open to SQL injection in the DeleteByPK function where the key is a string.

    You should be very very careful about building SQL out of strings.

    [)amien

  6. Omar: on a different subject, I downloaded your DropThings project on CodePlex but the database file/scripts are missing.

  9. This is great. I have run into this problem so many times but have just dealt with it on a case by case basis. A generic solution is very slick.

  12. Add this to your datacontext and you'll be able to delete in a much more performant way

    like:

    dataContext.Delete(dataContext.Threads.Where(x=>x.ThreadId == threadId));

    public int Delete(IEnumerable entities)

    {

    System.Data.Common.DbCommand command = this.GetCommand(entities.AsQueryable());

    string s = this.GetCommand(entities.AsQueryable()).CommandText;

    command.CommandText = “DELETE [t0]rn” + s.Substring(s.IndexOf(“FROM”));

    command.Connection.Open();

    int records = command.ExecuteNonQuery();

    command.Connection.Close();

    return records;

    }

    thanks to my colleague Evaldas 🙂

  14. This is one solution to avoid roundtrip, you can use store procedure to delete particular entity without roundtrip (If it is a big performance issue in your app).

  16. you should not do the reflection yourself.

    What about

    table.Context.Mapping.GetTable(typeof(TTable)).TableName

  17. Great Post! Do you have any idea how we can do the same thing for tables with more than one primary key?

Leave a Reply