in Entity Framework, DbSet.Local remains out of sync

Question

This one is making me crasy : I have an EF model built upon a database that contains a table named Category with 6 rows in it. I want to display this in a drop down list in WPF, so I need to bind it to the Categories.Local Observable collection. The problem is that this observable collection never receives the content of the database table. My understanding is that the collection should get in sync with the database when performing a query or saving data with SaveChanges() So I ran the followin 2 tests :

  Categories = _db.Categories.Local;

  // test 1
  Debug.WriteLine(_db.Categories.Count());
  Debug.WriteLine(_db.Categories.Local.Count());

  // test 2
  _categories.Add(new Category() { CategoryName = "test" });
  _db.SaveChanges();
  Debug.WriteLine(_db.Categories.Count());
  Debug.WriteLine(_db.Categories.Local.Count());
  Debug.WriteLine(_categories.Count());

The test 1 shows 6 rows in the database, and 0 in local. The test 2 shows 7 rows in the database, and 1 in local (both versions)

I also atempted to use _db.Category.Load() but as expected, it doesn't work because it is db first, not code first.

I also went through this page https://msdn.microsoft.com/en-us/library/jj574514(v=vs.113).aspx, created an object-based data source and linked my combo box to it, without success.

Does anyone know what I am doing wrong? Thank you in advance for your help.

Answer 1

The DbSet<T> class is IQueryable<T>, hence DbSet<T>.Count() method maps to Queryable.Count<T> extension method, which in turn is translated to SQL query and returns the count of the records in the database table without loading anything into db context local cache.

While DbSet<T>.Local simply gives you access to the local cache. It contains the entities that you added as well as the ones being loaded by query that returns T instances (or other entities referencing T via navigation property). In order to fully load (populate) the local cache, you need to call Load:

 _db.Categories.Load();

The Load is a custom extension method defined in QueryableExtensions class, so you need to include

using System.Data.Entity;

in order to get access to it (as well as to typed Include, XyzAsync and many other EF extension methods). The Load method is equivalent of ToList but without overhead of creating additional list.

Once you do that, the binding will work. Please note that the Local will not reflect changes made to the database through different DbContext instances or different applications/users.