Friday 29 August 2008

Entity Framework Verses LINQ to SQL

I have found myself in a rare opportunity to redesign the Data access layer in a WCF web service application.  Previously we were using a mixture of table adapters and typed data-sets abstracted away behind 'manager' classes.  By defining the manager classes with interfaces and creating mocking version we were able to easily decouple the functionality from the data.  The Data-set representations themselves being the only exception.

I have significant experience using typed data-sets on many projects. And I must say I have become rather sick of writing CRUD operations, sure learning to use new design patterns with them has brought new life.  But really... Do I need to define this stuff over and over again?

I am sure that I am not alone.

I'd heard about LINQ to SQL earlier in the year and had briefly toyed with it.  I'm a big fan of the LINQ concept, but the performance factor has always kept me weary of it as a technology.

Now for my confession.  I'm a virgin O/R mapping developer.  I have never had the excuse to use any database/object mapping tools on any production code as yet.

So being green, I decided to start learning more about the options available.

I think I am a creature of habit and I will keep to the Microsoft technology stack.  So I freely admit that I did not consider any other contenders.

"To use Entity Framework or Not to use Entity Framework"

I took the plunge and stripped out my table adapters from the DAL.  And in my eagerness inserted a new bright and shiny copy of the Entity Framework from .Net 3.5 SP1.  Being the later (I assumed greater) of the two options I chose it over LINQ to SQL.  And all was right with the world.

That night thoughts came unbidden...

How does the context know what to update?

Are two context instances independent of each other?

How do I make atomic level commits?

Can only the original context instance update any changes?

So this morning I was determined to nail this thing out.  Firstly I started making sure I understood how the entity framework behaves (and cracks it).  It is amazing how different using a O/R mapper is from simple Typed Data-sets.  But the more I played with the entities the more doubts crept in.

Previously my Manager classes would happily accept my word on the fact that a foreign key was correct.  The Entity Framework isn't so polite.  After playing around I stumbled upon a method of entity association which satisfied it.  Now I was really starting to worry, I had previously assumed that I could keep my changes limited to within the manager classes with the rest of the application none the wiser to the difference.  But Entity Framework was either demanding I Load the related table in order to associate the entity, or I was going to start leaking outside of my manager class.

I really really didn't want to leave the manager classes. 

While testing out the Entity Framework, I decided it would be worth while comparing it to raw data-sets / table adapters and also the LINQ to SQL alternative.  I set up a simple test of adding a new entry into the account table with a valid foreign key in the user table. 

The results were something of a surprise:

Entity Framework 8,700 milliseconds
LINQ to SQL 3,100 milliseconds
Data-sets 2,000 milliseconds

With only 500 repetitions it was quite clear that the Entity Framework is significantly slower.  What I didn't expect to see was how fast LINQ to SQL is!

And the real clincher for me was that I can manually set the foreign key with LINQ to SQL.  Also if speed becomes a critical we can down-grade to Data-Sets again.

No comments: