c#.net entity-framework wcf-data-services

Increasing the number of records retrieved exponentially increases linq query duration

During some testing of my data access routines using EF 5.0 and WCF Data Services, I have run into a performance issue. I am executing the following simple query from within a WCF Data Service Operation:

var addresses = (from address in context.Addresses select address).Take(500);
var addressList = addresses.ToList();

When I only take 500 records, query time is about 300 milliseconds. Taking 1000 records, query time is 4 seconds. Taking 2000 records query time is 24 seconds. Taking about 6000 records, query time is 360 seconds or more.

When I execute the sql statement generated by EF in Sql Server Management Studio, the 6000 record query is essentially instantaneous.

What settings can be configured to eliminate this performance issue?

Solution

Is this a read-only operation (e.g. to display in a grid) or do you need to update the entities you're retrieving from the database?

If you don't need to update the entities then you should use the .AsNoTracking() method - this means that Entity Framework won't try to keep track of any changes to the entities. Try something like this:

var addressList = context.Addresses.AsNoTracking().Take(500).ToList();