Entity framework and local cache of database tables

Question

I am developing an application that uses Entity Framework, but I have some performance issues. Imagine a user interface (C#, WPF) with some combo boxes and a data grid. Each time a value is selected in a combo box it changes the conditions for the data to show in the grid. It looks like the entity framework is not as flexible as I thought when it comes to caching. Because of the changed conditions the underlying sql will always be sligthly different (= no EF caching) and each cell update will result in a request to the database.

Is there any way I can cache the tables locally (with working navigation properties) and still use linq for selection etc. without generating any requests for the database?

• The tables are small so I don't expect any performance problems. (I guess local sorting etc. otherwise could be a problem since the database indexes are not used.)
• I don't need write access to the tables so if there is an easy way to make a deep copy of the tables and detach from the database connection that would perhaps be ok.
• I am not allowed to install any third party tools.

Perhaps entity framework was a bad choice from the beginning, but it's really convenient to use those generated classes and linq instead of manually writing a lot of classes and sql. (That I still would have to implement some cache for.)

Answer 1

Is there any way I can cache the tables locally?

This is what a DbContextdoes by default and there is an easy way for you to use that feature. Here is the basic pattern to follow:

context.Products.Where(p => <some intial condion>).Load();
var dataSource = context.Product.Local.Where(p => <some flexible condition>);

Note that in line 2 the Localcollection is used. This is a DbSetproperty that returns entities from the context's cache.

with working navigation properties

Any related entities that are loaded by the Load()statement will be automatically connected to one another by relationship fixup. So if a Product has a collection Components, you can do:

context.Components.Where(c => <some intial condion>).Load();

If this loads all components of the products that were loaded above, you'll see that their Components collection are now populated.

An alternative combining both steps is:

context.Products.Where(p => <some intial condion>)
       .Include(p => p.Components)
       .Load();

If there are many related tables you have to find a balance between individual Load statements and Load statements with Include, because many Includes in one statement may hit performance.

and still use linq for selection

As shown above: the flexible condition.

without generating any requests for the database

If you will always address Local collections only, these statements will never query the database. However, addressing navigation properties may still cause lazy loading. If you do ...

context.Products.Where().Load();
context.Components.Where().Load();

... this does populate product.Components collections, but doesn't mark them as loaded, whereas context.Products.Include(p => p.Components) does. So in the first case, addressing product.Components will trigger lazy loading. Similarly, addressing navigation properties for which the entities aren't loaded at all will also trigger lazy loading. So to be absolutely sure that no database interaction is triggered, you should disable lazy loading, either by ...

context.Configuration.LazyLoadingEnabled = false;

... or ...

context.Configuration.ProxyCreationEnabled = false;

The latter option forces EF to create simple POCO objects that are not capable of lazy loading.

So using these techniques, you can use your context as a local cache of connected entities. Which is an exception to the rule that a context should be short-lived.

One caution

Relationship fixup doesn't work for many-to-many associations. Suppose there is a m:n relationship between Product and Manufacturer, then ...

context.Products.Where().Load();
context.Manufacturers.Where().Load();

... won't populate product.Manufacturers and manufacturer.Products. Many-to-many associations should be loaded by Include:

context.Products.Where()
       .Include(p => p.Manufacturers)
       .Load();