LEFT JOIN GROUP BY HAVING on NHibernate

EmployeePayHistory payHistory = null;
   
var persons =
    session.QueryOver<Person>()
    .Left.JoinAlias(p => p.PayHistories, () => payHistory)                        
    .Where(
        Restrictions.Eq(
            Projections.Count(() => payHistory.EmployeePayHistoryCompositePK.BusinessEntityId)
        , 0
        )
    )    
    .SelectList(x => x
        .SelectGroup(p => p.BusinessEntityId)
        .SelectGroup(p => p.FirstName)
        .SelectGroup(p => p.LastName)                            
        .SelectCount(p => payHistory.EmployeePayHistoryCompositePK.BusinessEntityId)
    )
    .List<object[]>()
    .Select(cols => new
    {
        Id = (int)cols[0],
        FirstName = (string)cols[1],
        LastName = (string)cols[2],
        Count = (int)cols[3]
    });

persons.ToList();

Generated SQL:

SELECT
         this_.BusinessEntityId as y0_,
         this_.FirstName as y1_,
         this_.LastName as y2_,
         count(payhistory1_.BusinessEntityId) as y3_
     FROM
         Person.Person this_
     left outer join
         HumanResources.EmployeePayHistory payhistory1_
             on this_.BusinessEntityId=payhistory1_.BusinessEntityId
     GROUP BY
         this_.BusinessEntityId,
         this_.FirstName,
         this_.LastName
     HAVING
         count(payhistory1_.BusinessEntityId) = @p0;
     @p0 = 0 [Type: Int32 (0)]

One-stop shop code, complete working code. Just install AdventureWorks2008R2 database:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using NHibernate.Cfg;
using NHibernate.Cfg.MappingSchema;
using NHibernate.Criterion;
using NHibernate.Mapping.ByCode.Conformist;
 
using NHibernate.Linq;

 
namespace Craft
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var session = Mapper.SessionFactory.OpenSession())
            {
                
         EmployeePayHistory payHistory = null;

         var persons =
                     session.QueryOver<Person>()
                    .Left.JoinAlias(p => p.PayHistories, () => payHistory)
                    .Where(
                         Restrictions.Eq(
                             Projections.Count(() => payHistory.EmployeePayHistoryCompositePK.BusinessEntityId)
                             , 0
                         )
                     )

                    .SelectList(x => x
                        .SelectGroup(p => p.BusinessEntityId)
                        .SelectGroup(p => p.FirstName)
                        .SelectGroup(p => p.LastName)                            
                        .SelectCount(p => payHistory.EmployeePayHistoryCompositePK.BusinessEntityId)
                    )
                   .List<object[]>()
                   .Select(cols => new
                   {
                      Id = (int)cols[0],
                      FirstName = (string)cols[1],
                      LastName = (string)cols[2],
                      Count = (int)cols[3]
                   });

                persons.ToList();



                Console.ReadLine();
           } // using
        } // Main
    } //class Program
 
 
    public static class Mapper
    {
        static NHibernate.ISessionFactory _sessionFactory = Mapper.GetSessionFactory();
 
 
        public static NHibernate.ISessionFactory SessionFactory
        {
            get { return _sessionFactory; }
        }
 
        static NHibernate.ISessionFactory GetSessionFactory()
        {
            var mapper = new NHibernate.Mapping.ByCode.ModelMapper();
 
            mapper.AddMappings(
                new[] { 
                    typeof(PersonMapping) , typeof(EmployeePayHistoryMapping)
                });
 
 
            var cfg = new NHibernate.Cfg.Configuration();
 
            cfg.DataBaseIntegration(c =>
            {
                c.Driver<NHibernate.Driver.Sql2008ClientDriver>();
                c.Dialect<NHibernate.Dialect.MsSql2008Dialect>();
                c.ConnectionString = "Server=.;Database=AdventureWorks2008R2;Trusted_Connection=True;";
 
                c.LogFormattedSql = true;
                c.LogSqlInConsole = true;
            });
 
 
 
            HbmMapping domainMapping = mapper.CompileMappingForAllExplicitlyAddedEntities();
 
            cfg.AddMapping(domainMapping);
 
 
 
            var sf = cfg.BuildSessionFactory();
 
            return sf;
        }
    }
 
 
    public class PersonMapping : ClassMapping<Person>
    {
        public PersonMapping()
        {
            Table("Person.Person");
            Id(x => x.BusinessEntityId);
 
            Property(x => x.FirstName);
            Property(x => x.LastName);
 
            Bag(list => list.PayHistories, rel => rel.Key(k => k.Column("BusinessEntityId")), relType => relType.OneToMany());
 
        }
    }
 
    public class EmployeePayHistoryMapping : ClassMapping<EmployeePayHistory>
    {
        public EmployeePayHistoryMapping()
        {
            Table("HumanResources.EmployeePayHistory");
 
            ComponentAsId(x => x.EmployeePayHistoryCompositePK, x =>
            {
                x.Property(y => y.BusinessEntityId);
                x.Property(y => y.RateChangeDate);
            });
          
            ManyToOne(x => x.Person, k => 
            { 
                k.Column("BusinessEntityId"); 
                k.Insert(false); 
                k.Update(false); 
            });

            Property(x => x.Rate);
        }
    }
 
    
 
    public class Person
    {
        public virtual int BusinessEntityId { get; set; }
        public virtual string FirstName { get; set; }
        public virtual string LastName { get; set; }
 
        public virtual IList<EmployeePayHistory> PayHistories { get; set; }
    }
 
 
    public class EmployeePayHistory
    {
        EmployeePayHistoryCompositePK _pk = new EmployeePayHistoryCompositePK();
        public virtual EmployeePayHistoryCompositePK EmployeePayHistoryCompositePK 
        { 
            get { return _pk; } set { _pk = value; }
        }
 
        Person _person;
        public virtual Person Person 
        { 
            get { return _person; }
            set 
            {
                _person = value;
                _pk.BusinessEntityId = _person.BusinessEntityId;
            }
        }        
 
        public virtual decimal Rate { get; set; }
    }
 
 
    // AdventureWorks is fond of composite primary key. Composite unique is better
    public class EmployeePayHistoryCompositePK
    {
        public virtual int BusinessEntityId { get; set; }
        public virtual DateTime RateChangeDate { get; set; }
 
        public override bool Equals(object obj)
        {
            if (obj == null)
                return false;
 
            var t = obj as EmployeePayHistoryCompositePK;
            if (t == null)
                return false;
 
            if (BusinessEntityId == t.BusinessEntityId && RateChangeDate == t.RateChangeDate)
                return true;
 
            return false;
        }
 
        public override int GetHashCode()
        {
            return (BusinessEntityId + "|" + RateChangeDate).GetHashCode();
        }
    }
}

Happy Coding! ツ


Related: http://www.ienablemuch.com/2014/07/orm-expectations.html