Saturday 7 May 2011

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

1) 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!

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

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.

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.

No comments:

Post a Comment