Materialize entity framework query

Question

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;

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;

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?

EDIT: 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);

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).

Answer 1

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);

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;

or build your dictionaries using from i in query in stead of from i in context.Instrument.

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

Answer 2

Look at EntityCollection<T> CreateSourceQuery and Attach. I think you could do this (not tested):

var instrumentQuery = 
    from i in context.Instrument
    from p in i.InstrumentPerformance  // 1 : n
    where p.PortfolioScenarioID == 6013
    select i;
var instruments = instrumentQuery.ToList();
foreach (var instrument in instruments) {
    var performanceQuery =
        instrument.InstrumentPerformance.CreateSourceQuery()
            .Where(p => p.PortfolioScenarioID == 6013);
    instrument.InstrumentPerformance.Attach(performanceQuery);
}

This executes everything at once (no lazy loading) and has a bit of code duplication, but it would result in a list of Instrument where each i.InstrumentPerformance returns the filtered collection, meaning any subsequent code that operates on it can treat it like any other EF collection without needing to know the details of the query.