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

19 comments

  1. re: Linq to SQL: Delete an entity using Primary Key only says:

    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. re: Linq to SQL: Delete an entity using Primary Key only says:

    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. re: Linq to SQL: Delete an entity using Primary Key only says:

    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);”

  4. re: Linq to SQL: Delete an entity using Primary Key only says:

    Not sure I'm a fan…

  5. re: Linq to SQL: Delete an entity using Primary Key only says:

    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. re: Linq to SQL: Delete an entity using Primary Key only says:

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

  7. re: Linq to SQL: Delete an entity using Primary Key only says:

    Can you write similar function for Update?

  8. re: Linq to SQL: Delete an entity using Primary Key only says:

    Great post. Definately a little helper I'll add to my current list of L2Q extension methods (with a bit of a variation). Anyway, you can see my post at http://www.aneyfamily.com/…/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx to see how I do batch updates and deletes.

  9. re: Linq to SQL: Delete an entity using Primary Key only says:

    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.

  10. re: Linq to SQL: Delete an entity using Primary Key only says:

    Good solution and its very helpfull.

  11. re: Linq to SQL: Delete an entity using Primary Key only says:

    Are we going back to writing convoluted SQL code by using LINQ? The whole idea was to get away from that.

  12. re: Linq to SQL: Delete an entity using Primary Key only says:

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

  13. re: Linq to SQL: Delete an entity using Primary Key only says:

    smnbss – That code could get you into trouble if your entities IQueryable is built from joining two tables. See a more reliable way of deleting at http://www.aneyfamily.com/…/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx.

  14. re: Linq to SQL: Delete an entity using Primary Key only says:

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

  15. re: Linq to SQL: Delete an entity using Primary Key only says:

    Very nice! I've used this and it works perfectly!

    Thanks!!!

  16. re: Linq to SQL: Delete an entity using Primary Key only says:

    you should not do the reflection yourself.

    What about

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

  17. re: Linq to SQL: Delete an entity using Primary Key only says:

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

  18. Linq to SQL: Delete an entity using Primary Key only says:

    Thank you for submitting this cool story – Trackback from PimpThisBlog.com

  19. NoSqlDude says:

    This is NOT the way to do things using Linq!!

Leave a Reply

Your email address will not be published. Required fields are marked *