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.

LINQ to Entities

May 10, 2008

I’ve been investigating LINQ to Entities lately, and I have to tell you, it looks great! I saw a presentation by Adam Cogan and Justin King at Code Camp Oz 08 (, which basically compared LINQ to Entities to NHibernate, and after the presentation,  I reckon NHibernate may well be on the way out. Using LINQ to Entities is just so simple. The beauty of both of these systems is that any errors are found at compile time. My article below, titled “Some fix time data” shows that finding code defects is significantly cheaper at compile time than at runtime. Also, finding an error at compile time is preventative. If you have to wait until the application is running before finding a bug, it might be months before you even try a potentially buggy code path. NHibernate requires you to create XML files that contain the details of the tables you are trying to access through objects. LINQ to Entities, on the otherhand, just requires you to drag and drop the objects onto the designer. LINQ to Entities also handles many-to-many relationships in a really easy to use way. Don’t get me wrong, NHibernate looks good, but LINQ to Entities looks like it could be even better!