NHibernate LINQ left join not supported yet

Given this entities:

public class Question
{
    public virtual int QuestionId { get; set; }
    public virtual string Text { get; set; }
    public virtual string Poster { get; set; }

    public virtual IList<QuestionComment> Comments { get; set; }
    public virtual IList<Answer> Answers{ get; set; }

   
    public virtual byte[] RowVersion { get; set; }
}


public class QuestionComment
{
    public virtual Question Question { get; set; }        

    public virtual int QuestionCommentId { get; set; }
    public virtual string Text { get; set; }
    public virtual string Poster { get; set; }
}



public class Answer
{
    public virtual Question Question { get; set; }

    public virtual int AnswerId { get; set; }
    public virtual string Text { get; set; }
    public virtual string Poster { get; set; }

    public virtual IList<AnswerComment> Comments { get; set; }
    
}



public class AnswerComment
{
    public virtual Answer Answer { get; set; }

    public virtual int AnswerCommentId { get; set; }
    public virtual string Text { get; set; }
    public virtual string Poster { get; set; }
}

This is the INNER JOIN using LINQ:

var www = from x in q.All
          from y in x.Answers
          group y by new { x.QuestionId } into grp
          select new { grp.Key.QuestionId, Count = grp.Count() };

Output by Entity Framework:

SELECT 
[GroupBy1].[K1] AS [Question_QuestionId], 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
 [Extent1].[Question_QuestionId] AS [K1], 
 COUNT(1) AS [A1]
 FROM [dbo].[Answer] AS [Extent1]
 GROUP BY [Extent1].[Question_QuestionId]
)  AS [GroupBy1]

Output by NHibernate:

SELECT question0_.QuestionId AS col_0_0_, CAST(COUNT(*) AS INT) AS col_1_0_ 
FROM [Question] question0_ INNER JOIN [Answer] answers1_ ON question0_.QuestionId=answers1_.Question_QuestionId 
GROUP BY question0_.QuestionId   

This is the LEFT JOIN using LINQ:

var www = from x in q.All
          from y in x.Answers.DefaultIfEmpty()
          group y by new { x.QuestionId } into grp
          select new { grp.Key.QuestionId, AnswersCount = grp.Count() };

This is Entity Framework output:

SELECT 
[GroupBy1].[K1] AS [QuestionId], 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
 [Extent1].[QuestionId] AS [K1], 
 COUNT(1) AS [A1]
 FROM  [dbo].[Question] AS [Extent1]
 LEFT OUTER JOIN [dbo].[Answer] AS [Extent2] ON [Extent1].[QuestionId] = [Extent2].[Question_QuestionId]
 GROUP BY [Extent1].[QuestionId]
)  AS [GroupBy1]

NHibernate is currently lacking LEFT JOIN

System.NotSupportedException: The DefaultIfEmptyResultOperator result operator is not current supported

But as any programmers worth their salt, they can quickly discern that the query is not a proper LEFT JOIN. A Question's AnswersCount will still have a value of 1 even there's no matching answers on a given question


It should be constructed this way:

var www = from x in q.All
                      from y in x.Answers.DefaultIfEmpty()
                      group y by new { x.QuestionId } into grp
                      select new { grp.Key.QuestionId, Count = grp.Sum(x => x.Question.QuestionId != null ? 1 : 0 ) }; 

// similar to this: http://stackoverflow.com/questions/3789850/is-clean-sql-achievable-on-linq-to-sql

Entity Framework's Linq query generator is atrocious compared to other ORMs (even when compared to Linq-to-Sql):

SELECT 
[GroupBy1].[K1] AS [QuestionId], 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
 [Join1].[K1] AS [K1], 
 SUM([Join1].[A1]) AS [A1]
 FROM ( SELECT 
  [Extent1].[QuestionId] AS [K1], 
  CASE WHEN ([Extent2].[Question_QuestionId] IS NOT NULL) THEN 1 ELSE 0 END AS [A1]
  FROM  [dbo].[Question] AS [Extent1]
  LEFT OUTER JOIN [dbo].[Answer] AS [Extent2] ON [Extent1].[QuestionId] = [Extent2].[Question_QuestionId]
 )  AS [Join1]
 GROUP BY [K1]
)  AS [GroupBy1]

Contrast that with this Linq-to-Sql sample generated query (same scenario as above, i.e. using two tables too). This is very lean

SELECT SUM(
    (CASE
        WHEN [t1].[CustomerID] IS NOT NULL THEN @p0
        ELSE @p1
     END)) AS [N], [t0].[CustomerID] AS [Key]
FROM [Customers] AS [t0]
LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
GROUP BY [t0].[CustomerID]

// source: http://stackoverflow.com/questions/3789850/is-clean-sql-achievable-on-linq-to-sql

For NHibernate, you have to use QueryOver for LEFT JOIN functionality: http://www.ienablemuch.com/2012/12/nhibernates-non-stringly-typed-left-join.html