Performance degradation after upgrading EF4 model to EF5 model (DbContext)

Question

I had an EF4 Model in a .NET 4.0 Application that I upgrade to .NET 4.5 and EF5 (referencing the new EntityFramework 5 Assembly), I changed the "Code Generation Strategy" to "None" and added a Code generation item (EF 5.x DbContext Generator) to the model. Which works fine in almost any situation. But now a got big problems when i access a navigation property which references a lot of records (> 100.000 records). The database is a MSSQL 2005 Server.

My scenario looks like this:

Every customer in my db has a unique ID (It's the primary key in the DB), in addition every customer record contains a parent customer ID (in this special case almost every customer references to the same parent id (about 145.000 records out of 150.000 records) which is record with id 1).

My model contains the DbSet<CustomerBase> CustomerBase which represents the table which contains all customers an their data. In addition there are navigation properties called ICollection<CustomerBase> CustomerBaseChildren and ICollection<CustomerBase> CustomerBaseParent which connect the customer id and the customer parent id with an 0..1 to * multiplicity.

I build a simplified version to demonstrate what I mean:

Build the table with 150.000 records for this test:

CREATE TABLE CustomerBase
(
  id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
  parent_id int FOREIGN KEY REFERENCES CustomerBase(id),
  some_data1 varchar(100),
  some_data2 varchar(100),
  some_data3 varchar(100),
  some_data4 varchar(100),
  some_data5 varchar(100),
)
GO

DECLARE @i int = 0
WHILE @i < 150000 BEGIN
  INSERT INTO CustomerBase (parent_id, some_data1, some_data2, some_data3, some_data4, some_data5) VALUES (1, newid(), newid(), newid(), newid(), newid())

  SET @i = @i + 1
END

Import the table including the referencial constraint into a new entity model. I used as "Entity Container Name" ef5Entities. Then i renamed the Navigation Propierties CustomerBase1 and CustomerBase2 to CustomerBaseChildren and CustomerBaseParent.

And here is my sample application:

static void Main(string[] args)
{
  ef5Entities context = new ef5Entities();

  // Start with selecting a single customer.
  // Works fine in EF4/ObjectContext, works even faster in in EF5/DbContext
  CustomerBase someCustomer = context.CustomerBase.First(customer => customer.id == 1234);
  // Do something ...

  // Get the parent of the customer.
  // Works fine in EF4/ObjectContext, works even faster in in EF5/DbContext
  CustomerBase parentCustomer = someCustomer.CustomerBaseParent;
  // Do something ...

  // Get the first child of the given parent id.
  // Takes about 10 seconds in EF4/ObjectContext, I stopped the debugger after 2 minutes waiting in EF5/DbContext
  CustomerBase firstChild = parentCustomer.CustomerBaseChildren.First();

  Console.WriteLine("Press any key to quit.");
  Console.ReadKey();
}

I used the SQL Server Profiler to see what entity framework executes on the database. It seems that the EF4 and EF5 code is exactly the same:

SELECT TOP (1) 
[Extent1].[id] AS [id], 
[Extent1].[parent_id] AS [parent_id], 
[Extent1].[some_data1] AS [some_data1], 
[Extent1].[some_data2] AS [some_data2], 
[Extent1].[some_data3] AS [some_data3], 
[Extent1].[some_data4] AS [some_data4], 
[Extent1].[some_data5] AS [some_data5]
FROM [dbo].[CustomerBase] AS [Extent1]
WHERE 1234 = [Extent1].[id]

exec sp_executesql N'SELECT 
[Extent1].[id] AS [id], 
[Extent1].[parent_id] AS [parent_id], 
[Extent1].[some_data1] AS [some_data1], 
[Extent1].[some_data2] AS [some_data2], 
[Extent1].[some_data3] AS [some_data3], 
[Extent1].[some_data4] AS [some_data4], 
[Extent1].[some_data5] AS [some_data5]
FROM [dbo].[CustomerBase] AS [Extent1]
WHERE [Extent1].[id] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

exec sp_executesql N'SELECT 
[Extent1].[id] AS [id], 
[Extent1].[parent_id] AS [parent_id], 
[Extent1].[some_data1] AS [some_data1], 
[Extent1].[some_data2] AS [some_data2], 
[Extent1].[some_data3] AS [some_data3], 
[Extent1].[some_data4] AS [some_data4], 
[Extent1].[some_data5] AS [some_data5]
FROM [dbo].[CustomerBase] AS [Extent1]
WHERE [Extent1].[parent_id] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

If I execute all three statements in the SQL Management Studio, it takes about 1-2 seconds until all 1 + 1 + 150.000 records are fetched.

But as I understand the third statement is the problem. It returns 150.000 records (even if i use .First() like in the code above or .Single()or .Take(10) no matter if i use .OrderBy(...) in front of it or not. It seems like Entity Framework fetch all 150.000 records and caching the records in DbContext takes a awful lot of time (after waiting 2 minutes i stopped the test code, testing it with my real customer base table it took 100 minutes to finish). Caching in ObjectContext takes only about 10 seconds (which is bad to considering that the database itself is 5-10 times faster, but i could life with that).

Even the memory consumption is horribly, with ObjectContext the application Working Set raises about 200mb, with DbContext the Working Set raises about 10 times higher.

Is there a way to inject a TOP(n) clause in the select statement to stop receiving all records from the database if I only want the first record or the first n records (usual 10 to 100 records)? In the first statement there was an TOP(1) in the select statement (or a TOP(2) if use .Single() instead of .First()).

I even tried to change the line CustomerBase someCustomer = context.CustomerBase.First(customer => customer.id == 1234); to no-tracking: CustomerBase someCustomer = context.CustomerBase.AsNoTracking().First(customer => customer.id == 1234);

But then a get an System.InvalidOperationException at CustomerBase firstChild = parentCustomer.CustomerBaseChildren.First(); with the following message:

When an object is returned with a NoTracking merge option, Load can only be called when the EntityCollection or EntityReference does not contain objects.

If I change the Code Generation Strategy back to use ObjectContext with EF5 everything works fine like in good old EF4. Am I doing something wrong while using DbContext or is DbContext just not usable in larger environments?

Answer 1

I recently upgraded to Visual Studio 2013, .NET 4.5.1 and Entity Framework 6. If I modify my model to use EF6 instead of EF5 it works like a charm.

So the solution is to use EF4/EF5 with ObjectContext or EF6 with DbContext.