2

I have a large query built dynamically with the usage of nested .Where(). I am using Any() but after poor performance and some SQL profiling, I found Any() actually causes many round trips and selects each record of a related table for evaluation instead of using a JOIN, for example, which would be much better.

Here's a representation of the tables and what's being done, assuming tables A, B and C:

A <-- B --> C

Suppose I'm querying A and have something like .Where(a => a.B.C.Any(c => c.IsActive))

Is there a better option in spite of using Any()?

EkoostikMartin
  • 6,831
  • 2
  • 33
  • 62
Joao de Araujo
  • 1,096
  • 2
  • 15
  • 27
  • 2
    _"I have a large query built dynamically with the usage of nested .Where"_ I assume that the query below is not the problem since it looks fine: `.Where(a => a.B.C.Any(c => c.IsActive))` – Tim Schmelter May 27 '14 at 14:21
  • 4
    I assume that you have tried the `LoadWith(a=>a.B)` trick to eliminate the extra roundtrips, right? – Sergey Kalinichenko May 27 '14 at 14:22
  • @dasblinkenlight if the query shown was translated to SQL LoadWith would have zero impact. He should get the query right, not patch over it with eager loading. – usr May 27 '14 at 14:32

1 Answers1

1

Apparently, you are not really using LINQ to remote your queries to the server. It appears you are using IEnumerable queries, not IQueryable queries.

Normally, the query as shown qould be translated to SQL wholesale. Find out, why you are using LINQ to objects. The bug is with you, not with L2S. (These bugs can easily happen because you cannot trivially spot them just by looking at the code).

Is there a better option in spite of using Any()?

Not sure why you think Any is the problem. It is a random bystander.

usr
  • 168,620
  • 35
  • 240
  • 369
  • I am querying the tables in DataContext and building the query with a number of `.Where()` so I guess this is `IQueryable`. – Joao de Araujo May 27 '14 at 14:40
  • Well, it is not. You'd need to post more code for review. Or, call `Queryable.Where` explicitly. Maybe, the compiler will tell you that's impossible and you have found the bug. – usr May 27 '14 at 14:41
  • Looks like you are right, `Any()` is not the problem. When I test the whole query with LinqPad a single query is generated. I'm not sure yet why using that in code makes round trips. I will do some further investigation and post the results. – Joao de Araujo May 27 '14 at 14:43
  • Hover over every call to a query method. The tool-tip will show you whether it is of the Enumerable or Queryable kind. – usr May 27 '14 at 14:44
  • My bad. I was issuing a `Select()` at the end of the query with a function that would do database access. That function is to blame. In fact, `Any()` doesn't cause round-trips as far as I can tell. – Joao de Araujo May 27 '14 at 15:06