Databases
StackOverflow2013
Postsdbo
POAggregating Left Join in NHibernate using QueryOver/ICriteria
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAggregating Left Join in NHibernate using QueryOver/ICriteria
    text
    copied!<p>I have two simple classes</p> <pre class="lang-cs prettyprint-override"><code>public class Blog { public Blog(){ Comments=new List&lt;Comment&gt;(); } public virtual Guid Id { get; set; } public virtual string Title { get; set; } public virtual string Text { get; set; } public virtual DateTime CreatedDate { get; set; } public virtual IList&lt;Comment&gt; Comments { get; set; } } </code></pre> <p>and</p> <pre class="lang-cs prettyprint-override"><code>public class Comment { public virtual Guid Id { get; set; } public virtual string Author { get; set; } public virtual string Text { get; set; } } </code></pre> <p>Mapped using AutoMap and all is great with the world. I can add and save entities no problems.</p> <p>What I'd like to do is use QueryOver to get the number of comments per blog, but include those blogs where there are no comments, so in SQL:</p> <pre class="lang-sql prettyprint-override"><code>SELECT b.Title,COUNT(c.ID) AS Comments FROM Blogs b LEFT JOIN Comments c ON b.ID=c.BlogID </code></pre> <p>and get </p> <pre> Title Comments Blog 1 0 Blog 2 0 Blog 3 0 Blog 4 4 Blog 5 0 </pre> <p>The closest I've got is</p> <pre class="lang-cs prettyprint-override"><code>var results=session.QueryOver&lt;Blog&gt;() .Left.JoinQueryOver&lt;Comment&gt;(b=&gt;b.Comments) .TransformUsing(new DistinctRootEntityResultTransformer()) .List&lt;Blog&gt;() .Select(b =&gt; new { Id = b.Id, Title = b.Title, Comments=b.Comments.Count }); </code></pre> <p>which gets the right answer, but the SQL runs as</p> <pre class="lang-sql prettyprint-override"><code>SELECT b.Id,b.Title,c.ID,c.Author,etc... AS Comments FROM Blogs b LEFT JOIN Comments c ON b.ID=c.BlogID </code></pre> <p>then does the counting at the client end, which doesn't seem the most efficient way of doing it.</p> <p>Can this be done with QueryOver or ICriteria? I'd rather not use hql if possible.</p> <p>The entire solution is available at <a href="https://github.com/oharab/NHibernateQueriesSpike" rel="nofollow">https://github.com/oharab/NHibernateQueriesSpike</a> if you want to see all the config etc.</p> <p>Cheers</p> <p>B.</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