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

The main downside of ORMs in general is exactly this: performance against the DB. I still haven't see any BIG project made with EF due to these aspects. — LittleSweetSeas, Dec 04 '13 at 16:21
I can't answer your question, but if you haven't read the following link, you may want to: http://msdn.microsoft.com/en-us/data/hh949853 — Steve, Dec 04 '13 at 16:21
@Martijn: This does NOT run on SQL side. You can guess that by seing the return types: order.OrderDetails is a HashSet collection. And only IQueryable can defer query execution. — Tuk, Dec 04 '13 at 16:47
@LittleSweetSeas: guess you are right... I'm just frustrated by not getting a valid reason of why not return an IQueryable here. — Tuk, Dec 04 '13 at 16:50

score 1 · Accepted Answer · edited May 23 '17 at 12:28

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

edited May 23 '17 at 12:28

Community

1
1

answered Dec 04 '13 at 17:45

Colin

22,328
17
103
197

Thank's Colin, that makes absolute sense. I guess the root solution is to enhance the Include functionality as treated in the feature request you shared. Meanwhile, navigation properties fall back to useless in performance-critic scenarios... It's a pitty not much people is looking at this. – Tuk Dec 06 '13 at 15:49
@Tuk there are ways of populating navigation properties with a subset of the data required if you are working at the level of the context http://stackoverflow.com/a/20358097/150342. Can't say I'm a big fan of that idea though. – Colin Dec 06 '13 at 16:03
Yes, I've seen that post. As well as @tschmit007's answer, those are valid workarounds, yet dont solve Navigation Properties issue – Tuk Dec 06 '13 at 18:16

score 0 · Answer 2 · answered Dec 04 '13 at 16:27

0

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

answered Dec 04 '13 at 16:27

Shiva

20,575
14
82
112

I realize there are numerous ways to "achieve" what I want by indirect ways, like using .Entry(...).Collection(...).Query(), and many other... But What I'm asking is the reason on the design choice of not mantaining "open for query" the navigation properties, being a super common case of use – Tuk Dec 04 '13 at 16:58
Oh ok. Yeah I don't know. Dapper (the ORM that SO uses) came out of frustrations from the Performance issues with EF's "one size fits all" architecture. – Shiva Dec 04 '13 at 17:04

tschmit007 · Answer 3 · 2013-12-04T17:40:43.610

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]

There's no need for the selecting 1 and then using Sum, just use `Count` as the OP does to do that. — Servy, Dec 04 '13 at 17:34
Ok, that works. But still requires a nasty workaround for using Navigation Properties efficiently. — Tuk, Dec 06 '13 at 16:02

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

3 Answers3