21

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.)

Aluan Haddad
  • 29,886
  • 8
  • 72
  • 84
MCattle
  • 2,897
  • 2
  • 38
  • 54
  • 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. – MCattle Aug 31 '12 at 23:23

3 Answers3

9

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.

N Jones
  • 1,004
  • 11
  • 18
  • Upvoted for pointing me in the right direction; I was unaware ReSharper allowed me to dig into the EF internals. – MCattle Sep 05 '12 at 17:51
  • 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. – N Jones Sep 05 '12 at 22:41
6

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.

MCattle
  • 2,897
  • 2
  • 38
  • 54
  • 4
    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()` –  Sep 05 '12 at 18:16
  • 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. – MCattle Sep 05 '12 at 18:43
  • You're right, you could use it to change `Db.Cities.Local` to `Db.GetLocalEntities()`, but if you pass `Db.Cities` to a method, you're stuck and you need reflection. –  Sep 05 '12 at 18:47
  • In fact see in EF source code http://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework/Internal/InternalContext.cs 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()) – yoel halb Dec 04 '13 at 17:20
0

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.

Akash Kava
  • 39,066
  • 20
  • 121
  • 167
  • 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 (with consistency checks), but we're not actually using the ObservableCollection features. If I don't find a faster way to get at the local data, then I'll load the tables into List collections. – MCattle Sep 03 '12 at 14:30
  • 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 reference tables by writing a T4 template to do the heavy lifting, so I need only modify the .edmx file as necessary. – MCattle Sep 12 '12 at 00:55