CODEWITHSUNDEEP
sqlperformanceentity-framework
Amanda Kitson
Amanda Kitson

Reputation: 5557

Entity Framework slow with large datasets

I am using entity framework to retrieve a large dataset.

The dataset set has a parent/child relationship, and I need to bring back the child information at the same time as the parent.

What I am finding is that EF sends one query initially to get the list of parent objects, and then iterates through each parent object and queries the DB for the child information.

So if I have 1000 parent objects, I end up with a total 1001 calls to the DB (once for each parent object, plus the original query that gets the list of parents).

Is there anyway to make EF query this is a more efficient manner? Something like:

SELECT * FROM CHILD_OBJECT_TABLE WHERE PARENTID IN (LIST OF PARENT_IDS HERE)

Upvotes: 3

Views: 3529

Answers (2)

Akash Kava
Akash Kava

Reputation: 39956

In simplest way, you can use two calls in one context, and all will be loaded, as EF will map relations anyway even if they are loaded differently, however, all children should be loaded first and then parents.

 // load children
 var children = context.ChildTypes.Where(
      x => x.Parent.SomeProperty == SomeValue );

 var parents = context.ParentTypes.Where(
      x => x.SomeProperty == SomeValue );

This will load everything correctly and note that the condition is applied in both queries, there is no need for IN operation, as EF will map ParentType's condition to join correctly and load related entries.

Upvotes: 0

Adam Robinson
Adam Robinson

Reputation: 185703

How are you performing your query? If you're using lazy loading, you would experience the behavior you describe. Instead, use the Include function to specify that a particular navigation property (be it a related entity or collection) be loaded as part of the query. Put simply, EF will generate a flattened cartesian product of both relationships, then will properly reinflate the data into a parent-> child relationship when it instantiates your objects.

For example, if your parent class has a collection property called "Children", you'd call it like this:

context.Parents.Include("Children").Where(p=> ...)

Or if you prefer query syntax to extension method syntax...

from p in context.Parents.Include("Children")

where p ...

Upvotes: 5

Related Questions