LINQ to Entities n-tier disconnected CRUD

It seems so easy to add, update and delete objects in LINQ to SQL, but in LINQ to Entities, unless I’m missing something, the coder is expected to retrieve an object before modifying it or deleting it. A real pain as it causes extra unnecessary traffic to the database. I figured out a way to prevent the need to retrieve an object before making updates and deletes.

This article assumes that you know how to drag all the tables from the AdventureWorks database onto the LINQ to Entities designer (Add => New Item => ADO.NET Entity Data Model)

So here goes. CRUD using disconnected LINQ to Entities:

(1) Select all records from the Contact entity collection (table)

var result = 
    (from contact 
      in AdventureWorksEntities.Contact
select contact).ToList();

Once retrieved, you can do whatever you like with the results. Generally, you wouldn’t pass these objects all the way to the client. Instead , you’d populate some intermediary data model objects, and pass them over the wire.

(2) Create a record and insert it into the Contact entity collection (table)

AdventureWorksEntities awe 
             = new AdventureWorksEntities();
Contact c = Contact.CreateContact(23, false, 
  "FreddyFirstName", "MarleyLastName", 0, "blah2", 
  "blah3", Guid.NewGuid(), DateTime.Now);

(3) Update a record in the Contact entity collection (table)
Ok, here’s where it gets tricky. I don’t want to have to retain the original contact record in its original form. After I retrieve the object, I want to pass a copy of the data to the client, and destroy the original. When I receive the object’s details for updating, I may not necessarily want to update every field.

AdventureWorksEntities awe 
      = new AdventureWorksEntities();
Contact c = new Contact();
c.ContactID = 19978;
c.FirstName = "Yarble";
c.MiddleName = "Barble";

After I created the Contact object and set it’s primary key, then I added it to the AdventureWorksEntities context object. I immediately call AcceptAllChanges(). This does not make any database call. All this does is commit the changes in the entity context, setting the object to an unchanged state. The context is no longer aware that this object has just been added. As far as it’s concerned, the object has been there all along.

Then, make any necessary changes to the fields. Internally, the object tracker flags the fields as changed, because anything after the last AcceptAllChanges() is tracked. Next, call the SaveChanges() method. It makes a single call to commit the changes to the database. The only query that is executed in this case is as follows:

exec sp_executesql N’update [Person].[Contact]
set [FirstName] = @0, [MiddleName] = @1
where ([ContactID] = @2)
‘,N’@0 nvarchar(6),@1 nvarchar(6),@2 int’,@0=N’Yarble’,@1=N’Barble’,@2=19978

Great, we’ve now performed an update.

(4) Delete a record from the Contact entity collection (table)
After performing the update, the delete is trivial. Simply create an object, set it’s key, AcceptChanges() then DeleteObject.

Contact c = new Contact();
c.ContactID = 19980;

and the specific record is removed from the database.

Here’s the sql it generated:
exec sp_executesql N’delete [Person].[Contact]
where ([ContactID] = @0)’,N’@0 int’,@0=19979

So rather than retrieving a record from the database only to delete it or update it, it now performs a much more efficient single query in both the update and the delete calls.

One other thing. I noticed that LINQ to Entities takes significantly longer to execute than LINQ to SQL. It turns out that this is only the first time the data context is loaded, and is because LINQ to Entities has so much more work to do than it’s predecessor, LINQ to SQL. Further execution after that first time should be significantly faster, certainly approaching LINQ to SQL speeds.

5 Responses to LINQ to Entities n-tier disconnected CRUD

  1. iainwade says:

    Great article and solution mate!

    I’m really suprised at the lack of elegant solutions/recomendations around in regards to working with detached entitites when using EF with a or wcf enviroment.

    The often recommnded sugestion of returning copy of the original entity along with the updated one feels way messy.
    And the unneeded DB roundtrips when updating really is pretty wack.

  2. dudesmiles says:

    Great article! Great ideas and easy to read/understand. I’ve implemented Delete methods using this approach and it’s working great! I was taking a look at how you’re handling your Adds and Updates, and I have a question for you…

    Have you thought of (or found) a good way to add/update classes that have a ‘parent’ Navigating Relationship? For example, if you have a “Customer” class and it has a relationship to a ‘child’ class called “Order”; have you come up with a way to have both the Customer and Order classes in your EDM model and be able to update Order data without retrieving the parent Customer class?

    I’ve been trying to accomplish this in a disconnected fashion, but all efforts have led to having to populate the Customer EDM class before I can add or update the Order class. In other words, I can’t just set a value in my Order.CustomerID property and issue a SaveChanges() call – EF doesn’t have the CustomerID property because it replaced it with the Customer class for the Navigational Relationship. Instead, EF seems to want me to populate the Order.Customer Entity class before I can update the Order class.

    Any thoughts or suggestions? Thanks!


  3. dudesmiles says:

    Nothing to be sorry about – it’s still a great article! Thanks again for writing it (and for your response)!

  4. […] this solution gets me pretty far, but I can’t figure out how to null out a FK […]

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: