How to eager load entire database with EF

My database consists of 5 tables with ~10000 rows combined. It takes ~1Mb in SQL Server CE which is on shared folder. The database itself is hierarchical Country-Region-City-Street-Building. I am using Entity Framework 4.

Because the database is small users are able to explore and edit all 2000 Cities in a WPF ListView. But with every approach I tried so far the GUI is sluggish (because of many database round-trips, with dummy data GUI is lightfast). How can I load entire database into memory w开发者_如何学编程ith one or few database round-trips?

I tried multiple Include() but I noted great performance penalty as described here

Should I write my own ORM-light? I could also use plain ascii CSV files instead of database but it would obviously exclude concurrency.

Honestly, I've done something like this myself, and the answer for me was to copy the whole database locally and work on it.

If you're looking not only to read but also to write, I'd definitely suggest ditching CE and installing one of the Express versions of Sql Server. They are designed for this kind of situation; CE is not*.

*SP1 is better for concurrent access, but over the network will never be performant for large datasets.

I re-asked this question on Microsoft forum and they were kind to give me some guidance:

Basically my question can be restated as following:

• read only once from database on application start
• do all subsequent queries from local data, not database (for performance)
• write to both context and database each time an entity is being added or deleted.

With plain EF it is not possible because each query goes to the database. This implies that I must read data fast on start and then cache it.

Implementation details:

The best way seems to be using ESQL to import data fast and then cache it, for example using entities not connected to context. From my first experiments it seems to work well.