LINQ to Entities n-tier disconnected CRUD

August 15, 2008

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.