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.

Why do you think `_db.Category.Load();` doesn't work? It's not code first specific. Note that `_db.Categories.Count()` translates to SQL query and does not load anything into context. — Ivan Stoev, Jul 17 '17 at 18:14
I typed `_db.Category.Load();` it and it says the method is unknown. Did you try? I agree the `Count()` may not do the job, but I don't understand how I can come to a count of 1 after adding an item and saving changes, while there are then 7 lines in the database. — Cédric Bourgeois, Jul 17 '17 at 22:08
It's a custom extension method. `using System.Data.Entity;` should give you access to it (as well as many others inside the `QueryableExtensions` class). `Load` is basically `ToList` w/o creating a list (just to fill the `DbSet.Local`). — Ivan Stoev, Jul 18 '17 at 06:17
Hey! it works! Thank you so much! Now, how can I mark this as an answer? Do you want to write the answer so I can mark it as such? — Cédric Bourgeois, Jul 18 '17 at 07:14

score 2 · Accepted Answer · answered Jul 18 '17 at 07:31

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.

in Entity Framework, DbSet.Local remains out of sync

1 Answers1