如何使用 EF 预先加载整个数据库
我的数据库由 5 个表组成，总计约 10000 行。在共享文件夹上的 SQL Server CE 中大约需要 1Mb。数据库本身是分层的国家-地区-城市-街道-建筑物。我正在使用 Entity Framework 4。
由于数据库很小，用户可以在 WPF ListView 中浏览和编辑所有 2000 个城市。但到目前为止，我尝试过的每一种方法，GUI 都很缓慢（因为许多数据库往返，使用虚拟数据 GUI 是光快的）。如何通过一次或几次数据库往返将整个数据库加载到内存中？
我尝试了多次 Include() 但我注意到性能损失很大 如此处所述
我应该编写我自己的 ORM-light？我还可以使用纯 ascii CSV 文件而不是数据库，但它显然会排除并发性。
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 with 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
老实说，我自己也做过类似的事情，我的答案是在本地复制整个数据库并对其进行处理。
如果您不仅想阅读，还想写，我绝对建议放弃 CE 并安装 Sql Server 的 Express 版本之一。它们就是为这种情况而设计的； CE 不是*。
*SP1 更适合并发访问，但通过网络永远无法处理大型数据集。
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.
我在 上重新提出了这个问题微软论坛，他们很友善地给了我一些指导：
基本上我的问题可以重述如下：
对于普通 EF，这是不可能的，因为每个查询都会进入数据库。这意味着我必须在启动时快速读取数据，然后缓存它。
实现细节：
最好的方法似乎是使用 ESQL 快速导入数据然后缓存它，例如使用 实体未连接到上下文。从我的第一次实验来看，它似乎运作良好。
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:
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.