When we need to do this kind of SQL, we have to use Linq's DefaultIfEmpty.
select
b.blog_id,
b.blog_title,
p.blog_text
from blog b
left join blog_post p on b.blog_id = p.blog_id
DefaultIfEmpty is supported now on NHibernate, it works on collections now:
var results =
from b in s.Query<Blog> ()
from p in b.Posts.DefaultIfEmpty ()
select new { b.BlogId, b.BlogTitle, BlogText = p.BlogText };
foreach (var item in results) {
Console.WriteLine ("{0} : {1} : {2}", item.BlogId, item.BlogTitle, item.BlogText);
}
Whereas prior to DefaultIfEmpty support, it's hair-splitting to write the LEFT JOIN query in QueryOver API, it's a bit convoluted to use QueryOver, especially for those who are at ease with Linq and SQL. This is the adhoc-y approach, i.e. using anonymous type, good to use when you wanted to immediately return a JSON object:
BlogPost bpAlias = null;
var results = s.QueryOver<Blog> ()
.Left.JoinAlias (b => b.Posts, () => bpAlias)
.SelectList (z => z.Select(b => b.BlogId).Select(b => b.BlogTitle).Select(b => bpAlias.BlogText))
.List<object[]> ()
.Select (z => new { BlogId = (int)z[0], BlogTitle = (string)z[1], BlogText = (string)z[2] });
foreach (var item in results) {
Console.WriteLine ("{0} : {1} : {2}", item.BlogId, item.BlogTitle, item.BlogText);
}
This is the DTO approach. All hell break loose if you want to use a DTO on your QueryOver ツ
public class BlogPlusPostDto
{
public int BlogId { get; set; }
public string BlogTitle { get; set; }
public string BlogText { get; set; }
}
.
.
.
BlogPost bpAlias = null;
BlogPlusPostDto bpDto = null;
var results = s.QueryOver<Blog> ()
.Left.JoinAlias (b => b.Posts, () => bpAlias)
.SelectList (z => z
.Select(b => b.BlogId).WithAlias(() => bpDto.BlogId)
.Select(b => b.BlogTitle).WithAlias(() => bpDto.BlogTitle)
.Select(b => bpAlias.BlogText).WithAlias(() => bpDto.BlogText)
)
.TransformUsing (Transformers.AliasToBean<BlogPlusPostDto>())
.List<BlogPlusPostDto> ();
foreach (var item in results) {
Console.WriteLine ("{0} : {1} : {2}", item.BlogId, item.BlogTitle, item.BlogText);
}
Contrast that on using DTO with Linq. The Linq version is much shorter, and without the noise of WithAlias and Transformers that gets in the way of your query's beautiful objective. Plus it's easy to mock and unit test in-memory objects with IQueryable:
public class BlogPlusPostDto
{
public int BlogId { get; set; }
public string BlogTitle { get; set; }
public string BlogText { get; set; }
}
.
.
.
var results =
(from b in s.Query<Blog> ()
from p in b.Posts.DefaultIfEmpty ()
select new { b.BlogId, b.BlogTitle, BlogText = p.BlogText }).ToList ()
.Select (x => new BlogPlusPostDto { BlogId = x.BlogId, BlogTitle = x.BlogTitle, BlogText = x.BlogText });
foreach (var item in results) {
Console.WriteLine ("{0} : {1} : {2}", item.BlogId, item.BlogTitle, item.BlogText);
}
If lambda is your thing, you can convert the Linq code to the equivalent lambda code, also working on latest NHibernate:
var xx = s.Query<Blog>()
.SelectMany (blog => blog.Posts.DefaultIfEmpty()
.Select(post => new { blog.BlogId, blog.BlogTitle, BlogText = post.BlogText })
);
foreach(var item in xx) {
Console.WriteLine ("{0} : {1} : {2}", item.BlogId, item.BlogTitle, item.BlogText);
}
Now, about the limited part, we still cannot use DefaultIfEmpty on manual join operation, this results to runtime error that says the requested feature is not implemented.
var results =
from b in s.Query<Blog>()
join p in s.Query<BlogPost>() on b equals p.Blog into bJoinP
from r in bJoinP.DefaultIfEmpty()
select new { b.BlogId, b.BlogTitle, r.BlogText };
foreach(var item in results) {
Console.WriteLine ("{0} : {1} : {2}", item.BlogId, item.BlogTitle, item.BlogText);
}
And DefaultIfEmpty on NHibernate Linq's GroupBy has a bug on Count, NHibernate's Count cannot do conditional counting even if you passed a condition on it, it always blindly do COUNT(*), which always results to at least a count of 1, never having a count of zero. LEFT JOIN should be able to report aggregated count of zero.
To do a working COUNT with GROUP BY on NHibernate: http://www.ienablemuch.com/2012/12/nhibernates-non-stringly-typed-left-join.html
Happy Computing! ツ
