实现实体框架查询 [英] Materialize entity framework query

问题描述

我正在使用实体框架5进行如下查询：

I am using entity framework 5 for a query like this:

var query = 
    from i in context.Instrument
    from p in i.InstrumentPerformance  // 1 : n
    where p.PortfolioScenarioID == 6013
    select i;

我想将这个（过滤的）查询的可查询的记录存储在内存中。理想情况下，我将能够断开上下文，并仍然请求一个特定的InstrumentPerformance集合，如下所示：

I want to store a queryable respresentation of this (filtered) query in memory. Ideally, I would be able to disconnect the context and still request a specific InstrumentPerformance collection like so:

var perf = query.First(i => i.InstrumentID == 407240).InstrumentPerformance;

但这当然不会产生所需的结果，因为perf对象将包含一个InstrumentPerformance集合包含每1：n加入的InstrumentPerformance实体（无论其PortfolioScenarioID是否为6013），它将通过延迟加载检索这些实体，context.ContextOptions.LazyLoadingEnabled = false（或上下文超出范围）查询不会产生任何结果。

But this - of course - does not produce the desired result, since the "perf" object will contain an InstrumentPerformance collection that contains every 1:n joined InstrumentPerformance entity (whether its PortfolioScenarioID is 6013 or not) and it will retrieve these entities via lazy loading, with context.ContextOptions.LazyLoadingEnabled = false (or the context runnning out of scope) the query will not yield anyting.

所以这远不及我想要的：从原始查询中容易查询内存中的表示。我试图实现字典和类似的方法，但最终编写自定义数据对象的结果，我想避免。

So this is far from where I want to get: an easy to query in-memory representation from the original query. I tried to materialize into dictionaries and similar approaches, but ended up coding custom data objects for the result which I would like to avoid.

所以我的问题是：什么是推荐的方法来获取这样的内存视图？

So my question is: what is the recommended method to get such in-memory view?

编辑：
我正在使用两个字典来缓存数据，例如：

I am currently using two dictionaries to cache the data, e.g:

var instruments = (
    from i in context.Instrument
    from p in i.InstrumentPerformance
    where p.PortfolioScenarioID == 6013
    select i)
    .ToDictionary (i => p.InstrumentID, i => i);

var performances = (
    from i in context.Instrument
    from p in i.InstrumentPerformance
    where p.PortfolioScenarioID == 6013
    select p)
    .ToDictionary (p => p.InstrumentID, p => p);

但是，这需要对数据库进行两次往返，其中一个似乎是足够的，更重要的是查询的语义性能数据（现在是性能[InstrumentID]）与EF的查询方式不同（应该是instrument.InstrumentPerformance.First（）等）。

However, this requires two roundtrips to the database where one seems sufficient and more importantly the semantics for querying the performance data (which is now performances[InstrumentID]) is inconsistent with the EF way of querying (which should be instrument.InstrumentPerformance.First() and the like).

推荐答案

可以首先检索一个对象，然后通过以下方式创建字典：

It is possible to retrieve the objects in one take first and then create the dictionaries by:

var query = 
    (from i in context.Instrument
    select new { 
                 i,
                 ps = i.InstrumentPerformance
                          .Where(p.PortfolioScenarioID == 6013)
               }).AsEnumerable()
               .Select(x => x.i);

这将实现并选择 Instrument 这是一个伎俩，他们的部分加载 InstrumentPerformance 集合。即仪器只包含满足条件 PortfolioScenarioID == 6013 的 InstrumentPerformance 实体。这是因为EF运行一个称为关系fixup 的进程，将子对象与数据库中的对象绑定在一起。

This materializes and selects Instrument entities and, here's the trick, their partly loaded InstrumentPerformance collections. I.e. the instruments only contain InstrumentPerformance entities that meet the condition PortfolioScenarioID == 6013. This is because EF runs a process known as relationship fixup that ties child objects to the right parent object when they are fetched from the database.

所以现在你可以处理上下文，之后的任何时间。

So now you can dispose the context and any time after that do

var perf = query.First(i => i.InstrumentID == 407240).InstrumentPerformance;

或使用查询中的构建您的字典

重要提示：延迟加载应该被禁用 / strong>，否则EF在寻址时仍会尝试加载完整的集合。

IMPORTANT: lazy loading should be disabled, otherwise EF will still try to load the full collections when they are addressed.

这篇关于实现实体框架查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！