IQueryable and Mixing Lazy Loading and Eager Loading

Question

I have a situation where I will be using a repository pattern and pulling objects from the database with a lazy loaded GetAll method that returns IQueryable. However I also need to build dynamic objects that will be included with the lazy loaded objects(query).

Is it possible to add built objects to a lazy loaded IQueryable and still keep the lazy loaded benefits? For instance

public override IQueryable<Foo> GetAll()
{
    return _entities; // lazy loaded
}  

public override IQueryable<Foo> GetAllPlusDynamic()
{
    var entities = GetAll(); 
    foreach(var d in GetAllDynamic())
    {
        entities.Add(d); // eagerly loaded
    }
    return entities;
}

I honestly don't understand your question. What are those "dynamic objects" which are "eagerly loaded"? Do you mean some kind of Include expression, like in Include(customer => customer.Orders), or so? Can you make some concrete example? — Slauma
@Slauma my understanding was that Op wanted GetAllPlusDynamic to query the database collection plus some other collection... — McGarnagle
Yes, basically query the database for a set of objects and then query another data source (in this case a service) and build a set of objects. — Josh C

Slauma Slauma · Accepted Answer · 2012-06-23T11:20:16

I am unsure if I understand you correctly but refering to your comment...

Yes, basically query the database for a set of objects and then query another data source (in this case a service) and build a set of objects.

... I would say that it's not possible.

An object of type IQueryable<T> used with Entity Framework (LINQ to Entities) is basically a description of a query which the underlying data store can execute, usually an abstract description (expression tree) which gets translated into SQL.

Every part of such a query description - where expressions, select expression, Any(...) expressions, etc. - must be translatable into the native language (SQL) of the data store. It's especially not possible to include some method calls - like a service call - in an expression that the database cannot understand and perform.

IQueryable<T> knows an underlying "provider". This provider is responsible to translate the expression tree hold by the IQueryable<T> object into "something", for example T-SQL used by SQL Server, or the SQL dialects used by MySQL or Oracle. I believe it's possible to write your own provider which might then be able to perform somehow the service calls and the database queries. But I also believe that this is not an easy task.

Using the standard SQL providers for Entity Framework you have to perform database query and calling the service separately one after each other: Run query and materialize entities in memory - then run the service call on the result collection for each entity.

IQueryable and Mixing Lazy Loading and Eager Loading

1 Answers