Tip 9 – How to delete an object without retrieving it

Problem

The most common way to delete an Entity in the Entity Framework is to pull the Entity you want to delete into the context and then delete it like this:

// Find a category by ID by
// TRIVIA: in .NET 4.0 you can use .Single()
// which will throw if there is more than one match.
var category = (from c in ctx.Categories
where c.ID == 3
select c).First();

// Delete the item
ctx.DeleteObject(category);
// Save changes
ctx.SaveChanges();

However this code issues two database commands rather than just the one. I mean if you think about it all I really need to do is this:

DELETE FROM [Categories] WHERE ID = 3

Now most of the time this isn’t too bad, but if performance and scalability are really critical for you, then it’s definitely not ideal.

Solution

Fortunately the Entity Framework provides a method called AttachTo(…) which you can use to put an Entity into the ObjectContext in the unchanged state.

And you can use it to “fake” a query like this:

// Create an entity to represent the Entity you wish to delete
// Notice you don't need to know all the properties, in this
// case just the ID will do.
Category stub = new Category { ID = 4 };
// Now attach the category stub object to the "Categories" set.
// This puts the Entity into the context in the unchanged state,
// This is same state it would have had if you made the query
ctx.AttachTo("Categories", stub);
// Do the delete the category
ctx.DeleteObject(stub);
// Apply the delete to the database
ctx.SaveChanges();

And now you’ve deleted an object from the database, without first doing a query.

but…

Not so fast

In the sample above all I needed to provide in my stub object was the ID i.e. the Entity’s Primary Key (PK). But was only because it was a contrived example to illustrate the core principle.

Unfortunately in the real world it might not be so simple.

There are two things that can make it more complicated.

1. Concurrency Values
2. Foreign Keys (like a Product has a Category)

If the Entity you wish to delete has either of these, then you need to provide more information to the Entity Framework, before the delete will actually work.

Concurrency Values

Why you need to provide the concurrency values is reasonably clear. In the conceptual model you’ve said that the Entity Framework should use these values to verify that all updates and deletes are operating over the latest known good version of the Entity.

So when creating the stub entity for deletion you need to set the PK and any Concurrency values, to the values currently in the database.

If you know those values, this is no problem, you simply include then in the initialization code of your stub Entity, something like this:

Category stub = new Category { ID = 4, Version = 6 };

If you don’t know them, well you are out of luck, you have to resort to a query to get them, and you are officially right back at square one!

Foreign Keys Values

The reason you need to provide Foreign Key (FK) values is much less intuitive. It is a side-effect of the Entity Framework’s Independent Association model. This post introduces some of the side effects of Independent Associations.

NOTE: when I wrote that post we were calling “Independent Associations” “First Class Associations”, But with the Introduction of FK Associations things have changed. If we had continued to call “Independent Associations” “First Class Associations” it might have sent the message that “FK Associations” are second class in some way. Sorry for the confusion. My bad…

If you delete the Entity on either end of the Association, the entity framework needs to delete the Association too. But because associations are considered Independent, and are identified by two things: the PK of the dependent Entity and the FK value, the Entity Framework actually needs the FK to identify the Association to be deleted.

If you don’t understand this don’t worry, it is not easy, I’m still struggling with this concept!

The fact remains though, if the Entity has a FK in the table, you need to somehow provide the current value of this FK for the delete to succeed.

Providing the FK value is actually not that hard, you simply create a reference to another fake entity like this:

// Build a stub to delete, and simultaneously build
// a Category stub too, to tell the EF about the
// CategoryID FK value in the Products table.
Product stub = new Product {
ID = 3,
Category = new Category { ID = 1 }
};
// Attach, Delete and SaveChanges
ctx.AttachTo("Products", stub);
ctx.DeleteObject(stub);
ctx.SaveChanges();

And your are done.

Its official you’ve saved yourself a database command.

In the Future

The hoops you have to jump through for Foreign Keys are only necessary if you use Independent Associations, you had no choice in .NET 3.5 SP1, if however you use FK Associations which are new to .NET 4.0, there is no longer a need to provide the FK value unless you’ve explicitly marked the FK property as a concurrency value.

The result?

Your code ends up looking like my first contrived sample.

Easy peasy lemon squeezy.

Comments

• Anonymous
  March 26, 2009
  Hopefully if you're reading this you've noticed that I've started a series of Tips recently. The Tips

• Anonymous
  March 27, 2009
  I think the reason that the FK Association on delete thing is hard for people to understand is that, for most of us, it is very natural to think of this in SQL terms. In this case, the "association" is just a digit and a database constraint. The association itself has no life of its own. The mental hurdle to get over is that when we do this delete, we are not deleting database records (yet) but objects. In this case, the association is a separate instance. True, this boils down to the same DML statements in the end, but that is the result of the mapping, and that is done after the call to DeleteObject is already completed.

• Anonymous
  March 27, 2009
  Hi Alex,when deleting entity instances one will not care about concurrency values at all. Why?An entity is something with it's own identity. This identity is modeled by the primary key of the entity. Despite the fact, that the attributes of the entity instance may change over time, the identity does not change! If the identity of the entity instance changes, then we get another entity instance.So when deleting the entity instance, one only cares about the correct identity. The values of the other attributes aren't important.Concurrency values are used to ensure that the database is not updated using outdated cached values. But with deleting, one won't care of the cached values at all. The only thing one cares about ist that the entity instance is in the database for now.For ensuring tat, it is sufficient to know the PK. If modeling VTH (valid time history) or TTH (transaction time history) then one is forced to query the database for the actual version as the PK is composed and not fully determined by the identity. But that has nothing to do with concurrency values, as one never knows this versioning information beforehand.Best regardsMartin

• Anonymous
  March 27, 2009
  @Craig,Yeah you are probably right. The problem is a mental model problem. People think either about the CLR type or the Database, they don't think about this other thing in the middle (the EDM) that has it's own semantics...@Martin,I'm not an expert on why we need the concurrency values. I think there are a number of reasonable positions here, your's is definitely one that many people share.The EF takes another position, namely that if you want to do a delete you have to display knowledge of the current concurrency values. While this might not make sense in a purely SQL world, it makes more sense in a world that makes it really easy to have stale values.Alex

• Anonymous
  April 02, 2009
  The Entity Framework is pretty big, so when the Entity Framework team talks about things internally we

• Anonymous
  April 07, 2009
  Background and Motivation: In my last post on EF Jargon I introduced the concept of Relationship Span.

• Anonymous
  April 20, 2009
  Please help.How can one delete >1 object in a single call?Example1...delete from SomeTableExample2...delete from SomeTable where id>11...???Please advise.Thank you.-- Mark Kamoski

• Anonymous
  April 20, 2009
  Mark, The current approach for delete/update in the EF, is one at a time, there is not ability to send a command that deletes/updates multiple rows. What you are asking for is multiple rows with one command, which is essentially a Data Modification Language or DML. This is not supported. But in 'some' simple circumstances you can achieve what you want using Extensions methods see this series on doing bulk updates for more information: http://blogs.msdn.com/alexj/archive/2008/02/11/rolling-your-own-sql-update-on-top-of-the-entity-framework-part-4.aspx
  http://blogs.msdn.com/alexj/archive/2008/02/11/rolling-your-own-sql-update-on-top-of-the-entity-framework-part-3.aspx
  http://blogs.msdn.com/alexj/archive/2008/01/15/rolling-your-own-sql-update-on-top-of-the-entity-framework-part-2.aspx
  http://blogs.msdn.com/alexj/archive/2007/12/07/rolling-your-own-sql-update-on-top-of-the-entity-framework-part-1.aspx
  This approach is not foolproof so you if you adopt it you do very careful testing. Alex  

• Anonymous
  April 20, 2009
  //Is this OK then or does it need some tweaking?public void FlushLog(string targetConnectionString){

  targetConnectionString = (targetConnectionString + "").Trim();using (SiteLogEntities myContext = new SiteLogEntities(targetConnectionString)){    var myQuery = from p in myContext.SiteLogRecords select p;    foreach (SiteLogRecord mySiteLogRecord in myQuery)    {        myContext.DeleteObject(myQuery);        myContext.SaveChanges(true);    }}

  }

• Anonymous
  April 20, 2009
  Mark,I suggest you do this instead.foreach (SiteLogRecord mySiteLogRecord in myQuery){  myContext.DeleteObject(myQuery);}myContext.SaveChanges();Bringing the SaveChanges out of the loop will just make this a more efficient, and everything will be done in one transaction.CheersAlex

• Anonymous
  April 20, 2009
  //I think I am close; but, I get a "not found" RTE, shown below...public void FlushLog(string targetConnectionString){

  targetConnectionString = (targetConnectionString + "").Trim();using (SiteLogEntities myContext = new SiteLogEntities(targetConnectionString)){    var myQuery = from p in myContext.SiteLogRecords select p;    Debug.WriteLine("myQuery.Count().ToString()='" + myQuery.Count().ToString() + "'");    //This prints a number greater than zero.    foreach (SiteLogRecord mySiteLogRecord in myQuery)    {        myContext.DeleteObject(myQuery);        //This throws a RTE "object cannot be deleted because it was not found in the ObjectStateManager".    }    myContext.SaveChanges();}

  }

• Anonymous
  April 20, 2009
  //OOPs... there is an error in my post most-previous to this one and the "fixed" version is below... and it works... thank you for all your help...

      public void FlushLog(string targetConnectionString)    {        targetConnectionString = (targetConnectionString + "").Trim();        using (SiteLogEntities myContext = new SiteLogEntities(targetConnectionString))        {            var myQuery = from p in myContext.SiteLogRecords select p;            foreach (SiteLogRecord mySiteLogRecord in myQuery)            {                myContext.DeleteObject(mySiteLogRecord);            }            myContext.SaveChanges();        }    }

• Anonymous
  April 21, 2009
  If it is not too far off-topic, I now need to delete a bunch of rows, such as to truncating a table to 1/2 size, generically, such as shown in the code below, and I think there is no L2E equivalent...

      /// <summary>    /// This will trim the given table to the specified size.    /// </summary>    /// <param name="targetConnectionString">This is the connection string to use.</param>    /// <param name="targetTableName">This is the table to truncate.</param>    /// <param name="targetColumnNameForSort">This is the column to use for sorting before truncating.</param>    /// <param name="targetSortOrder">This is the sort to use, where Ascending=TrimFromTop and Descending=TrimFromBottom.</param>    /// <param name="targetColumnNameForKey">This is the single-column primary-key column name.</param>    /// <param name="targetRowCountMax">This is the max size allowed in the table.</param>    /// <param name="targetTrimDenominator">This is the trim factor, where (targetRowCountMax/targetTrimDenominator)=(RowCountToTrim).</param>    /// <returns>This is the number of rows affected.</returns>    public int TrimTable(string targetConnectionString, string targetTableName, string targetColumnNameForSort, System.Data.SqlClient.SortOrder targetSortOrder, string targetColumnNameForKey, int targetRowCountMax, int targetTrimDenominator)    {        int myRowsAffected = int.MinValue;        targetConnectionString = (targetConnectionString + "").Trim();        targetTableName = (targetTableName + "").Trim();        targetColumnNameForSort = (targetColumnNameForSort + "").Trim();        targetColumnNameForKey = (targetColumnNameForKey + "").Trim();        string mySortDirection = "";        if (targetSortOrder == System.Data.SqlClient.SortOrder.Ascending)        {            mySortDirection = "ASC";        }        else        {            mySortDirection = "DESC";        }        int myRowCountCurrent = this.GetCount(targetConnectionString, targetTableName);        if (myRowCountCurrent <= targetRowCountMax)        {            //Continue.        }        else        {            decimal myTrimCount = (targetRowCountMax / targetTrimDenominator);            myTrimCount = Math.Round(myTrimCount, 0);            string myCommandText = " DELETE " + targetTableName + " FROM (SELECT TOP " + myTrimCount + " * FROM " + targetTableName + " ORDER BY " + targetColumnNameForSort + " " + mySortDirection + ") AS T1 WHERE " + targetTableName + "." + targetColumnNameForKey + " = T1." + targetColumnNameForKey;            using (SqlConnection myConnection = new SqlConnection(targetConnectionString))            {                SqlCommand myCommand = new SqlCommand(myCommandText, myConnection);                myCommand.Connection.Open();                myRowsAffected = myCommand.ExecuteNonQuery();            }        }        return myRowsAffected;    }

• Anonymous
  April 21, 2009
  //relative to my post above, here is my TrimTable code in L2E but it probably needs work...public int GetCount(string targetConnectionStringL2e){

  int myCount = int.MinValue;using (SiteLogEntities myContext = new SiteLogEntities(targetConnectionStringL2e)){    var q = from p in myContext.SiteLogRecords            select p;    if ((q == null) || (q.Count() < 0))    {        myCount = 0;    }    else    {        myCount = q.Count();    }}return myCount;

  }public long GetIdMax(string targetConnectionStringL2e){

  long myIdMax = long.MinValue;using (SiteLogEntities myContext = new SiteLogEntities(targetConnectionStringL2e)){    var q = from p in myContext.SiteLogRecords            orderby p.ID descending            select p;    if ((q == null) || (q.Count() < 0))    {        myIdMax = 0;    }    else    {        myIdMax = q.First().ID;    }}return myIdMax;

  }public void TrimLog(string targetConnectionStringL2e){

  targetConnectionStringL2e = (targetConnectionStringL2e + "").Trim();int myCountCurrent = this.GetCount(targetConnectionStringL2e);//TODO. 200904221. This works but it assumes the ID is the PK, identity, //auto-increment by 1, and start at 0, and does not handle gaps rigorously.if (myCountCurrent <= Team.Framework.SiteLogLibrary.Consts.DefaultTrimRowCountMax){    //Continue.}else{    //Note that Team.Framework.SiteLogLibrary.Consts.DefaultTrimDenominator = 2.    //Note that Team.Framework.SiteLogLibrary.Consts.DefaultTrimRowCountMax = 10000.    long myIdMax = this.GetIdMax(targetConnectionStringL2e);    long myMinIdAllowed = ((myIdMax) - (Team.Framework.SiteLogLibrary.Consts.DefaultTrimRowCountMax / Team.Framework.SiteLogLibrary.Consts.DefaultTrimDenominator));    using (SiteLogEntities myContext = new SiteLogEntities(targetConnectionStringL2e))    {        var myQuery = from p in myContext.SiteLogRecords                      where (p.ID < myMinIdAllowed)                      select p;        foreach (SiteLogRecord mySiteLogRecord in myQuery)        {            myContext.DeleteObject(mySiteLogRecord);        }        myContext.SaveChanges();    }}

  }

• Anonymous
  June 21, 2009
  Background and Motivation: In my last post on EF Jargon I introduced the concept of Relationship Span

• Anonymous
  October 05, 2010
  This will fail if the object you are going to delete is loaded into the context. Is there any way to check and do the attaching only if there's no such object in the context?