Linq to SQL: Delete an entity using Primary Key only

Linq to Sql does not come with a function like "text-decoration:underline;">.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 "text-decoration:underline;">.DeleteOnSubmit(obj) to queue
it for delete. Then you have to call "text-decoration:underline;">DataContext.SubmitChanges() to
play the delete queries on database. So, how to delete object
without getting them from database and avoid database
roundtrip?

"/wp-content/images/993B225AC0D3502D2A3066EDE73C92F3.png">
style=
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Delete an object without getting it - Linq to Sql" src=
"/wp-content/images/7DFC275152E29A2A0147D0F4CCA759DB.png"
border="0" height="168" width="486">

You can call this function using "text-decoration:underline;">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 "text-decoration:underline;">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 "text-decoration:underline;">GetTableDef()
returns the table name and primary key field name for an
entity.

Every Linq Entity class is decorated with a "text-decoration:underline;">Table attribute that has the
table name:

"/wp-content/images/1F4B62053B097054F705213F60F24F20.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Lint entity declaration" src=
"/wp-content/images/64635CBC6AB9AA39EA7CC02A75A649EC.png"
border="0" height="67" width="551">

Then the primary key field is decorated with a "text-decoration:underline;">Column attribute with
IsPrimaryKey =
true
.

"/wp-content/images/AD1058884270DA83F6E9EACCCA0BD7EE.png">
"Primary Key field has Column attribute with IsPrimaryKey = true"
style=
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt=
"Primary Key field has Column attribute with IsPrimaryKey = true"
src=
"/wp-content/images/1051F7B84561E7F447AD7F72ADE20D9B.png"
border="0" height="77" width="709">

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:

"/wp-content/images/CD3A35EBE713FEADBA3CC3314D4B931E.png">
"Using reflection find the Table attribute and the Column attribute"
style=
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt=
"Using reflection find the Table attribute and the Column attribute"
src=
"/wp-content/images/B3CF9D69623A13FCE847388760DE4389.png"
border="0" height="615" width="685">

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:

"/wp-content/images/FF29D6E833E47C8AC3378A2BBC9362D2.png">
"border-top-width:0px;display:inline;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;"
alt="Delete a list of objects using Linq to SQL" src=
"/wp-content/images/2E8967A5EA108E9CDCDCDEF390BA280E.png"
border="0" height="398" width="553">

The code is available here:

href=
"http://code.msdn.microsoft.com/DeleteEntitiesLinq">http://code.msdn.microsoft.com/DeleteEntitiesLinq

"http://www.dotnetkicks.com/kick/?url=http%3a%2f%2fmsmvps.com%2fblogs%2fomar%2farchive%2f2008%2f10%2f30%2flinq-to-sql-delete-an-entity-using-primary-key-only.aspx">
"http://www.dotnetkicks.com/Services/Images/KickItImageGenerator.ashx?url=http%3a%2f%2fmsmvps.com%2fblogs%2fomar%2farchive%2f2008%2f10%2f30%2flinq-to-sql-delete-an-entity-using-primary-key-only.aspx"
alt="kick it on DotNetKicks.com" border="0">

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 *