QueryOver order by joined alias: The multi-part identifier could not be bound

Question

I am having problems with a query:

Company thirdParty = null;
var thirdParties = Session.QueryOver<ThirdPartyCompany>()
                          .JoinAlias(x => x.Company, () => thirdParty)
                          .WhereRestrictionOn(x => x.Domain.Id)
                          .IsIn(domainIds)
                          .OrderBy(() => thirdParty.Name).Asc
                          .Select(x => x.Company)
                          .List<Company>();

The ThirdpartyCompany entity looks like this and is essentially a mapped many-to-many table:

public class ThirdPartyCompany
{
    public virtual Domain Domain { get; set; }
    public virtual Company Company { get; set; }

    // ... removed equality overrides for readability
}

The problems I'm having is with the order part. If I skip the ordering, the query works. But with the ordering I get an exception that says {"The multi-part identifier \"thirdparty1_.Name\" could not be bound."}. I have also tried with .OrderByAlias( ) but not success there either...

EDIT (adding mappings):

public class ThirdPartyCompanyMapping : ClassMapping<ThirdPartyCompany>
{
    public ThirdPartyCompanyMapping()
    {
        Table("ThirdPartyCompany");
        ComposedId(mapping => mapping.ManyToOne(x => x.Domain, m =>
        {
            m.Column("DomainId");
            m.NotNullable(true);
        }));
        ComposedId(mapping => mapping.ManyToOne(x => x.Company, m =>
        {
            m.Column("CompanyId");
            m.NotNullable(true);
        }));
    }
}
public class CompanyMapping : ClassMapping<Company>
{
    public CompanyMapping()
    {
        Property(x => x.Name, m => m.NotNullable(true));
        Property(x => x.Type, m => m.NotNullable(true));
        ManyToOne(x => x.Domain, m => m.NotNullable(false));
    }
}
public class DomainMapping : ClassMapping<Domain>
{
    public DomainMapping()
    {
        Property(x => x.Name, m =>
        {
            m.NotNullable(true);
            m.Unique(true);
        });
        Set(x => x.ThirdParties, m =>
        {
            m.Table("ThirdPartyCompany");
            m.Key(x => x.Column("DomainId"));
        }, c => c.ManyToMany());

        //... removed other mappings for readability
    }
}

Don't be mad, but here's an "is it plugged in" question: does the `Name` column exist on the `Company` table? That is, does this SQL query, `SELECT TOP 1 Name FROM Company;`, run without error? — Daniel Schilling, Mar 18 '14 at 21:23
@DanielSchilling, Yup, the query returns one row (with one name). — carl, Mar 19 '14 at 13:20
@DanielSchilling, the sql looks like this: `SELECT this_.CompanyId as y0_ FROM ThirdPartyCompany this_ WHERE this_.DomainId in (@p0) ORDER BY thirdparty1_.Name asc` — carl, Mar 19 '14 at 13:23
Try forcing the missing join of your sql with Fetch(x => x.Company).Eager see: http://stackoverflow.com/questions/4072811/nhibernate-queryover-not-loading-eagerly-with-a-many-to-many-joinalias — flo scheiwiller, Mar 22 '14 at 21:52
No change using fetch... I also tried using JoinQueryOver instead of JoinAlias, but no change. — carl, Mar 24 '14 at 08:19

score 0 · Accepted Answer · answered Mar 24 '14 at 09:10

I found a workaround that works for me. I split up the query into two (where one is detached):

var thirdPartiesInDomain = QueryOver.Of<ThirdPartyCompany>()
                                    .WhereRestrictionOn(x => x.Domain.Id)
                                    .IsIn(domainIds)
                                    .Select(x => x.Company.Id);

var thirdParties = Session.QueryOver<Company>()
                          .WithSubquery.WhereProperty(x => x.Id)
                          .In(thirdPartiesInDomain)
                          .OrderBy(x => x.Name).Asc
                          .List();

QueryOver order by joined alias: The multi-part identifier could not be bound

1 Answers1