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

  1. Anonymous says:

    Hopefully if you’re reading this you’ve noticed that I’ve started a series of Tips recently. The Tips

  2. Craig Stuntz says:

    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.

  3. Martin Laufer says:

    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 regards

    Martin

  4. Alex D James says:

    @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

  5. Anonymous says:

    The Entity Framework is pretty big, so when the Entity Framework team talks about things internally we

  6. Anonymous says:

    Background and Motivation: In my last post on EF Jargon I introduced the concept of Relationship Span.

  7. mkamoski says:

    Please help.

    How can one delete >1 object in a single call?

    Example1…

    delete from SomeTable

    Example2…

    delete from SomeTable where id>11

    …???

    Please advise.

    Thank you.

    — Mark Kamoski

  8. Alex D James says:

    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  

  9. mkamoski says:

    //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);
    
        }
    
    }
    

    }

  10. Alex D James says:

    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.

    Cheers

    Alex

  11. mkamoski says:

    //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();
    
    }
    

    }

  12. mkamoski says:

    //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();
    
            }
    
        }
    
  13. mkamoski says:

    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;
    
        }
    
  14. mkamoski says:

    //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();
    
        }
    
    }
    

    }

  15. Anonymous says:

    Background and Motivation: In my last post on EF Jargon I introduced the concept of Relationship Span

  16. Anonymous says:

    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?