One-to-Many-Designed LINQ Query Performed

Question

I design LINQ to SQL results for strongly typed classes: parent and child. The performance difference between the two queries is large:

Slow request - a record from the DataContext shows that a separate db call is made for each parent

var q = from p in parenttable select new Parent() { id = p.id, Children = (from c in childtable where c.parentid = p.id select c).ToList() } return q.ToList() //SLOW 

Quick request - logging from a DataContext shows a single request to a remote db that returns all the necessary data

 var q = from p in parenttable select new Parent() { id = p.id, Children = from c in childtable where c.parentid = p.id select c } return q.ToList() //FAST 

I want to force LINQ to use the single request style of the second example, but directly populate the parent classes with its child objects. otherwise, the Children property is the IQuerierable<Child> that must be requested to open the Child object.

Related questions do not seem to affect my situation. using db.LoadOptions does not work. perhaps this requires the type to be TEntity registered in the DataContext.

  DataLoadOptions options = new DataLoadOptions(); options.LoadWith<Parent>(p => p.Children); db.LoadOptions = options; 

Please note: parents and children are simple types, not Table<TEntity> . and there is no contextual relationship between parent and child. subqueries are ad-hoc.

The key is the problem: in the second LINQ example, I implement IQueriable operators and do not call the ToList() function, and for some reason LINQ knows how to create one single query that can receive all the necessary data. How to fill my advertising projection with actual data, as it was done in the first request? Also, if someone can help me better talk about my question, I would appreciate it.

Answer 1

It is important to remember that LINQ queries depend on deferred execution. In the second request, you actually do not receive information about the children. You created the queries, but in fact you did not execute them to get the results of these queries. If you renamed this list and then iterated over the Children collection of each item that you see, it takes as much time as the first request.

Your request is also inherently very inefficient. You use a subquery to represent a Join relationship. If you use Join , then the query will be optimized accordingly by both the query provider and the database for execution much faster. You may also need to tune indexes in your database for better performance. Here's what the connection might look like:

 var q = from p in parenttable join child in childtable on p.id equals child.parentid into children select new Parent() { id = p.id, Children = children.ToList(), } return q.ToList() //SLOW 

Answer 2

The fastest way I found for this is to execute a query that returns all the results and then groups all the results. Make sure you execute .ToList () for the first request, so that the second request does not make many calls.

Here r should have what you want to execute with only one db request.

  var q = from p in parenttable join c in childtable on p.id equals c.parentid select c).ToList(); var r = q.GroupBy(x => x.parentid).Select(x => new { id = x.Key, Children=x }); 

Answer 3

You must set the correct parameters for loading your data.

 options.LoadWith<Document>(d => d.Metadata); 

Take a look

PS Include for LINQToEntity only.

Answer 4

The second request is executed quickly precisely because the Children are not populated.

And the first is slow only because children are populated.

Choose the one that best suits your needs, you simply cannot combine your functions!

EDIT:

As @Servy says:

In your second query, you are not actually getting information about the children. You created the queries, but in fact you did not execute them to get the results of these queries. If you tried the list and then iterated over the Children collection of each item, you would see that it takes as much time as the first request.