Databases
StackOverflow2013
Postsdbo
POone-to-many projected LINQ query executes repeatedly
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POone-to-many projected LINQ query executes repeatedly
    text
    copied!<p>I am projecting LINQ to SQL results to strongly typed classes: Parent and Child. The performance difference between these two queries is large:</p> <p>Slow Query - logging from the DataContext shows that a separate call to the db is being made for each parent</p> <pre><code>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 </code></pre> <p>Fast Query - logging from the DataContext shows a single db hit query that returns all required data</p> <pre><code>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 </code></pre> <p>I want to force LINQ to use the single-query style of the second example, but populate the Parent classes with their Children objects directly. otherwise, the Children property is an <code>IQuerierable&lt;Child&gt;</code> that has to be queried to expose the Child object.</p> <p>The referenced questions do not appear to address my situation. using db.LoadOptions does not work. perhaps it requires the type to be a TEntity registered with the DataContext.</p> <pre><code> DataLoadOptions options = new DataLoadOptions(); options.LoadWith&lt;Parent&gt;(p =&gt; p.Children); db.LoadOptions = options; </code></pre> <p>Please Note: Parent and Child are simple types, not <code>Table&lt;TEntity&gt;</code> types. and there is no contextual relationship between Parent and Child. the subqueries are ad-hoc. </p> <p>The Crux of the Issue: in the 2nd LINQ example I implement <code>IQueriable</code> statements and do not call <code>ToList()</code> function and for some reason LINQ knows how to generate one single query that can retrieve all the required data. How do i populate my ad-hoc projection with the actual data as is accomplished in the first query? Also, if anyone could help me better-phrase my question, I would appreciate it.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload