Linq query makes all calls to bring foreign key's table data

Question

from d in Customers
select d;

This query makes a call for every customerId to get the orders where customerId = that customerId.

This makes the call slow and long, I don't want orders data. How to disable that?

Addtional details:

I'm doing conditional

if (flag)
{
   return (from d in Customers
           select d).ToList();
}    
else
{
   return (from d in Customers
           where d.Orders.Count > 10
           select d).ToList();
}

Even in the if query it makes calls to all the Orders of each Customer which I want to prevent in both the cases.

That is incorrect as stated, and since you don't show real code it's hard to tell what you're actually asking. Can you replace this with real code? — Craig Stuntz, Mar 11 '11 at 21:39
Reading what David B has said, seems like I should never access d.Orders in any case as it makes stupid calls for each Customer in that case. I should probably join ? but then whats the use of d.Orders? if thats going to make so many calls, thats so lame of EF :D — ns12345, Mar 11 '11 at 21:46
Your updated query still doesn't show the problem. If `Customers` is `IQueryable`, then the `where` will be done in SQL. If it's `IEnumerable`, then it will be done in memory. You're hiding the parts that control the behavior you're asking about, but the problem is your code, not LINQ to Entities. — Craig Stuntz, Mar 11 '11 at 21:47
Your statement that `d.Orders` makes "stupid calls for each Customer" is wrong. LINQ to Entities doesn't do that. LINQ to Objects + lazy loading does, however. The core problem here seems to be that you're using L2O when you should be using L2E, but you don't show enough code to be sure. — Craig Stuntz, Mar 11 '11 at 21:49
What you describe looks like N+1 problem. Check this question: http://stackoverflow.com/questions/5070013/select-n1-in-next-entity-framework Do you perform any such operation? Do you access orders of loaded customers? — Ladislav Mrnka, Mar 11 '11 at 21:57
@Craig, sorry I'm fairly new to EF and .net. I've limited knowledge. But I'm using exact code but different names ofcourse. But as you said I'm not sure if I'm using L2E or L2O you are right. I'm not sure. But when I'm doing dataModel.Customers then Customers is of System.Data.Objects.ObjectSet type which I think becomes L2O? how to change it to entity? — ns12345, Mar 11 '11 at 21:58
@WholsNinja: I agree with @Craig, the pivotal issue here is going to be what `Customers` is and where it comes from. Can you show us the code for that? — Justin Morgan - On strike, Mar 11 '11 at 22:03
`ObjectSet` does not turn into L2O by itself. What makes you think this is done in memory? Have you actually looked? You need to describe the *complete* problem you see, since we can't sit down at your machine and look. — Craig Stuntz, Mar 11 '11 at 22:05
I'm not sure what code I should show you, but Customers comes from the edmx.cs file where it is a property of ObjectSet type. Where else should I look at? — ns12345, Mar 11 '11 at 22:45

score 3 · Accepted Answer · edited Mar 14 '11 at 14:41

As Craig Stuntz hinted, the question here is where Customers comes from and what type it is. If it's an in-memory object, the filtering is all going to happen in memory; if it's an object query, it'll happen on your database server, which is what you want. We can't see enough of your code to know much about Customers, but I'll suggest a right and wrong example:

Right (more or less):

using (var context = new MyContextType())
{
    var Customers = context.Customers;

    var query = from d in Customers
                select d;

    if (!flag)
    {
       query = from d in query
               where d.Orders.Count > 10
               select d;
    }

    return query.ToList();
}

Wrong:

using (var context = new MyContextType())
{
    var Customers = context.Customers.ToList(); // ToList triggers the query

    var query = from d in Customers
                select d;

    if (!flag)
    {
       query = from d in query
               where d.Orders.Count > 10
               select d;
    }

    return query.ToList();
}

See the difference? It's the context.Customers.ToList(). That runs the full query and loads everything into memory before you have a chance to filter it. Make sure you have the query fully built, including where logic, before you run ToList() on it.

@Craig - Hope you don't mind me picking up your idea and running with it. I'd have voted for your answer if you had one up.

I edited your answer to include little comment line. The reason I did so is because I stared for ten seconds before I realized the difference between two snippets.. — Dan Abramov, Mar 14 '11 at 14:51
@gaearon - Got it. I tried to point that out outside the code, but you're right, the comment helps. — Justin Morgan - On strike, Mar 14 '11 at 14:56

score 1 · Answer 2 · answered Mar 11 '11 at 21:38

1

The query you posted doesn't do that. Accessing the Orders property of a customer instance could do that. Don't access the Orders property and you'll be fine.

If your code isn't accessing the Orders property, perhaps some serialization code is... to prevent that code from being able to retrieve data - dispose the data context. To prevent that code from having an Orders property to access, remove the association in the dbml.

answered Mar 11 '11 at 21:38

Amy B

108,202
21
135
185

Please look at the additional details that I've added. – ns12345 Mar 11 '11 at 21:43
1

Accessing navigation properties in a L2E query does *not* result in n+1 queries. – Craig Stuntz Mar 11 '11 at 21:50

score 0 · Answer 3 · answered Mar 14 '11 at 13:45

0

I went to the dataModel, and deleted the property Orders from Customers table. and now it works fine, there's just one call. But I can't do e.Orders.Count anymore but I would rather use join instead of doing that to solve this problem.

answered Mar 14 '11 at 13:45

ns12345

3,079
5
41
62

Linq query makes all calls to bring foreign key's table data

3 Answers3