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
}
}
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();