Friday, November 13, 2009

Why I Hate Entity Framework

A few months ago I took over development of an insurance-related system. It was a Windows Forms application created for a small workgroup. Original design utilized ADO.NET Entity Framework and it was my first serious encounter with the technology. Immediately, I sensed that something wasn't right: application was too slow. There were only a dozen or so tables and hardly any data in them, yet forms took whole seconds to load. Database updates were even more problematic - not only they took long time, but sometimes they failed for no apparent reason.

Initially, I tried to write it all off as part of a learning curve. I recreated entity model and ran a SQL Profiler in an effort to better understand the technology. Profiler results were simply shocking: instead of executing a half-dozen or so SELECT statements in response to user opening a record, Entity Framework generated hundreds of them (trace file was over 1Mb in size).

So, I remembered the little performance test I did for LINQ to SQL about two years ago and decided to expand it to include Entity Framework. New method looks similar to the one used to test LINQ to SQL:
private TimeSpan RunEntityTest()
{
var swatch = Stopwatch.StartNew();
NorthwindEntities db = new NorthwindEntities(
ConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString);

for (int orderId = 10248; orderId < 11078; orderId++)
{
var query = from o in db.Orders
where o.OrderID == orderId
select new
{
o.OrderID,
o.OrderDate,
o.Customers.CustomerID,
o.Customers.CompanyName,
ProdCount = o.Order_Details.Count
};

foreach (var item in query)
{
string s = item.CompanyName;
}
}
return swatch.Elapsed;
}
By the way, that original blog post has been criticized by an anonymous guest, who pointed out that my test routines for stored procedures, dynamic SQL and parameterized SQL were not equivalent to LINQ test, because I never read any values from SqlDataReader object after opening it. So, I changed the code in all three methods as follows:
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string s = Convert.ToString(dr["CompanyName"]);
}
dr.Close();
I'm glad to say that this change didn't make any difference to test results: LINQ to SQL is still 40 times slower than stored procedures or dynamic SQL. But Entity Framework results were much worse: 2.2 times slower than LINQ to SQL. Here is the full table:

METHODAVERAGE TIME, ms
Stored Procedure110
Dynamic SQL115
Parameterized Dynamic SQL126
LINQ to SQL4,699
Entity Framework10,547

So, in the end, I rewrote the data layer of the application using LINQ to SQL.
Updated test harness code is available here: http://members.cox.net/rmamedov/blog/EntityFrameworkTestHarness.zip