NHibernate select top N with condition on children using fetch

[Michael Logutov]

This is crosspost from SO: http://stackoverflow.com/questions/10721633/nhibernate-select-top-n-with-condition-on-children-using-fetch/10724015

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?

[Alexander I. Zaytsev]

Hi, you have to split paging and fetching requests:

var userids = ( from user in session.Query<User> ()

where user.Name.StartsWith ("a") && user.Phones.Any ()

select user.Id)

.Take (100)

.ToArray ()

var usersWithPhones = session.Query<User>()

.Where(u=>userids.Contains(u.Id))

.Fetch(u=>u.Phones)

.ToArray();

[Alexander I. Zaytsev]

And probably it would work with inlined query: 

var userids = ( from user in session.Query<User> ()
where user.Name.StartsWith ("a") && user.Phones.Any ()
select user.Id)

.Take (100); // there is no .ToArray() call