NHibernate select top N with condition on children using fetch

linq, nhibernate, one-to-many, sql-server, top-n

Solution

You have to split queries to subselects. Where inner subselect should do pagination and outer should do fetching:

var top100users =
(
    from user in session.Query<User>()
    where user.Name.StartsWith("a") &&
          user.Phones.Any()
    select user
)
.Take(100);

var users =
(
    from user in session.Query<User>()
    where top100users.Contains(user)
    select user
)
.Fetch (x => x.Phones)
.ToArray();

And this will generate single sql query which will behave as you expect.

Problem

I have simple User entity: ``` public class User { public virtual int Id { get; set; } public virtual DateTime CreationDate { get; set; } public virtual DateTime ModifiedDate { get; set; } public virtual string Email { get; set; } public virtual string Name { get; set; } public virtual IList<Phone> Phones { get; set; } } public class Phone { public virtual string CountryCode { get; set; } public virtual string Code { get; set; } public virtual string Number { get; set; } public virtual string Comment { get; set; } } ``` My mappings defined as this: ``` public class UserMap : ClassMap<User> { public UserMap () { this.Table ("Users"); this.Id (x => x.Id).CustomSqlType ("bigint").GeneratedBy.HiLo ("1000"); this.Map (x => x.CreationDate); this.Map (x => x.ModifiedDate).Column ("LastUpdatedDate"); this.Map (x => x.Email).Length (255).Not.Nullable ().Unique (); this.Map (x => x.Name).Column ("UserName").Length (255); this.HasMany (x => x.Phones).Inverse (); } } public class PhoneMap : ClassMap<Phone> { public PhoneMap () { this.Table ("Phones"); this.Id ().GeneratedBy.Identity (); this.Map (x => x.CountryCode).Length (5); this.Map (x => x.Code).Length (10); this.Map (x => x.Number).Length (50).Not.Nullable (); this.Map (x => x.Comment).Length (255); } } ``` Additional conventions here: ``` PrimaryKey.Name.Is (x => "Id"), ForeignKey.EndsWith ("Id"), DefaultAccess.Property (), DefaultCascade.All () ``` I need to select top 100 users with Phones and whose name starts with "A". But I need to load user objects with Phones in them. So I do this query: ``` var users = ( from user in session.Query<User> () where user.Name.StartsWith ("a") && user.Phones.Any () select user ) .Fetch (x => x.Phones) .Take (100) .ToArray (); ``` And I only got 72 users. Why? Well, because NHibernate generates single TOP N select with left outer join and SQL returns several records for the same user entity because some users do have more that one phone. But it's all counts against TOP N - so I get 100 records of users joined with phones, but only 72 of them are unique entities. Is there a proper way to do it?

Original source