EF 4.3.1 and EF 5.0 DbSet.Local is slower than an actual Database query

Question

I've got a database with a table of about 16,500 cities, and an EF Data Model (Database-First) for that database. I preload them into memory with the code:

Db.Cities.Load()

...then when it's time to use them, I've tried each of the following queries:

Dim cities() As String = Db.Cities.Select(Function(c) c.CityName).ToArray

Dim cities() As String = Db.Cities.Local.Select(Function(c) c.CityName).ToArray

The first query is fast (~10ms), but the second one takes about 2.3 seconds to run the first time (although it's faster than the first query when it's called after that).

This doesn't make sense because SQL Server Profiler verifies that the first query is hitting the database on another machine, but the second isn't!

I've tried turning off Db.Configuration.AutoDetectChangesEnabled, and I've tried pre-generating the views.

What can I do to make .Local faster? (Not all clients running this application are going to be on a fast LAN.)

Nakesha

I should note that I'm using .NET 4.0 and not 4.5, so the test on "EF 5.0" is actually on the .NET 4.0 version of EF 5.0 (aka EF 4.4.0). Unfortunately, .NET 4.5 isn't an option for this project at the moment. – Nakesha

Answer 1

Why don't you simply save List of string from first query and use that instead.

List<string> cities = db.Cities.Select( x=>x.CityName).ToList();

Local may be slower because of Select, which might be doing some consistency checks.

Contagium

That's what I'm leaning toward, but I'm currently using a loop to call .Load() on all the DbSets. There are about 77 tables in the application loaded into memory for quick reference. I suspect the first call of .Local is creating the ObservableCollection<T> (with consistency checks), but we're not actually using the ObservableCollection<T> features. If I don't find a faster way to get at the local data, then I'll load the tables into List<T> collections. – Nakesha

Making this the accepted solution, as relying on the internal cache raises other issues, such as the ability to update cached objects with new values from the database. I've handled the overhead of creating, populating, and managing the large number of List<T> reference tables by writing a T4 template to do the heavy lifting, so I need only modify the .edmx file as necessary. – Nakesha

Answer 2

I walked the source for the Local property using Resharper's handy feature. You'll first see a call to DetectChanges which probably isn't your issue if all you're running is the above three lines. But then EF creates a new ObservableCollection for Local and fills it item by item. Either of those can be costly on the first call.

The query directly against the DbSet will route into the EF database providers which I'm sure directly access the internal local cache.

Etrem

Upvoted for pointing me in the right direction; I was unaware ReSharper allowed me to dig into the EF internals. – Nakesha

Resharper is a great tool for this. You'll also see in there that the EF tracks the Local ObservableCollection. I would strongly recommend against accessing the internal cache except for read-only purposes. But what I've found time and again is that caching in arrays or lists when you do your preload (like @Akash says) is really the way to go. There are lots of ways to do this without adding too much syntactic overhead. – Etrem

Answer 3

The following extension method will return an IEnumerable<T> containing the DbSet's local cached entities without the startup overhead incurred by the DbSet.Local() method detecting context changes and creating an ObservableCollection<T> object.

<Extension()>
Public Function QuickLocal(Of T As Class)(ByRef DbCollection As DbSet(Of T)) As IEnumerable(Of T)
    Dim baseType = DbCollection.[GetType]().GetGenericArguments(0)
    Dim internalSet = DbCollection.GetType().GetField("_internalSet", Reflection.BindingFlags.NonPublic Or Reflection.BindingFlags.Instance).GetValue(DbCollection)
    Dim internalContext = internalSet.GetType().GetProperty("InternalContext").GetValue(internalSet, Nothing)
    Return DirectCast(internalContext.GetType.GetMethod("GetLocalEntities").MakeGenericMethod(baseType).Invoke(internalContext, Nothing), IEnumerable(Of T))
End Function

Calling .QuickLocal on a DbSet containing 19,679 entities takes 9 ms, whereas calling .Local takes 2121 ms on the first call.

Nakesha

Ah, if that's the issue, you can avoid reflection and access the ObjectStateManager directly: ((IObjectContextAdapter)dbContext).ObjectContext.ObjectStateManager.GetObjectStateEntries(EntityState.Unchanged | EntityState.Added | EntityState.Modified).Select(entry => entry.Entity).OfType<T>() – Cindycine

That is simpler, but when writing an extension method on DbSet to replace Local() with QuickLocal(), there is no easy way to get the DbContext of the DbSet object without using reflection anyway. – Nakesha

You're right, you could use it to change Db.Cities.Local to Db.GetLocalEntities<City>(), but if you pass Db.Cities to a method, you're stuck and you need reflection. – Cindycine

In fact see in EF source code entityframework.codeplex.com/SourceControl/latest#src/… that GetLocalEntities is just using the code of hvd (actually a bit longer way, as hvd's idea of OfType is better from the EF Where().Select()) – Rossetti