EF Related Objects Filter Performance, can't use as IQueryable?

Question

I've been working with EF for a while, and while I find it great, there's something that struggles my mind.

Let's say I'm talking about the classic Order / OrderDetails relationship. DbContext generated and everything. Among other properties, I have a navigation property ICollection OrderDetails inside class Order.

Now, why there is no clean way to use that navigation property as an IQueryable property? That way, I could make something like this with good performance, running the WHERE on SQL side:

var argDetails = order.OrderDetails.Where(d => d.Active==true);

or even...

order.OrderDetails.Count();

Instead, this fetches all the related Details into memory and filters/counts using EntityToObjects...

Totally not performant.

Any good reason behind this?

Thanks

Answer 1

IQueryable is an abstraction of the database query, but the features provided by IQueryable are dependent on the provider and that makes it a leaky abstraction. Many advocate that IQueryable shouldn't get out of the data layer: Using the repository pattern to support multiple providers

Most developers are striving to keep POCO's unpolluted by dependencies. Foreign keys and virtual methods are a compromise that most people will put up with but IQueryable is probably a step too far.

You can vote for filtered includes here: Allow filtering for Include extension method

References:

Foreign key properties in domain entities

Answer 2

you are right, the point is to get a IQueryable

for example:

Int32 i = ctx.Orders.Where(x => x.Id == 1).SelectMany(x => x.Details).Where(y => y.IsActive).Select(x => 1).Sum();

runs server side

So a response to your question may be: because it can be done other way, and doing it that way allows things that would not be if done another way. :)

Please don't ask me what other things :).

EDIT

Int32 i = ctx.Orders.Where(x => x.Id == 1).SelectMany(x => x.Details).Where(y => y.IsActive).Count();

is equivalent with nearly same queries:

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        SUM(1) AS [A1]
        FROM [dbo].[OrderDetails] AS [Extent1]
        WHERE (1 = [Extent1].[Order_Id]) AND ([Extent1].[IsActive] = 1)
    )  AS [GroupBy1]

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[OrderDetails] AS [Extent1]
        WHERE (1 = [Extent1].[Order_Id]) AND ([Extent1].[IsActive] = 1)
    )  AS [GroupBy1]

Answer 3

I hope I have not mis-understood your question, but are you looking for ObjectContext.LoadProperty

Also: How to: Explicitly Load POCO Entities

context.LoadProperty(order, "OrderDetails");