I have a table fill query, which have to get all references entities.
I have client:
public class Client
{
public virtual int Id { get; set; }
public virtual ICollection<Address> Addresses { get; protected set; }
public virtual Address CurrentAddress { get; set; }
}
Address
public class Address
{
public virtual int Id { get; set; }
public virtual string Address1 { get; set; }
public virtual string City { get; set; }
public virtual string ZipCode { get; set; }
}
and DB diagram:
I want get all users Addresses by Linq to Nhibernate:
ClientRepository.Where(x => x.Id == clientId).SelectMany(c => c.Addresses ).Where(x => x.Address1.Contains("Comp")).ToList();
But I got this query:
SELECT TOP (20 /* @p0 */) Id31_,
Address2_31_,
Address3_31_,
City31_,
ZipCode31_
FROM (select address2_.Id as Id31_,
address2_.Address1 as Address2_31_,
address2_.Address2 as Address3_31_,
address2_.City as City31_,
address2_.ZipCode as ZipCode31_,
ROW_NUMBER() OVER( ORDER BY address2_.Address1) as __hibernate_sort_row
from CLIENTS client0_
inner join AddressToClient addresshis1_
on client0_.Id = addresshis1_.ClientId
inner join ADDRESSES address2_
on addresshis1_.AddressId = address2_.Id
where address2_.Id = 2 /* @p1 */) as query
WHERE query.__hibernate_sort_row > 0 /* @p2 */
ORDER BY query.__hibernate_sort_row
EDITED My repository:
/// <summary>
/// Gets the repository query.
/// </summary>
/// <value>The repository query.</value>
protected override IQueryable<Client> RepositoryQuery
{
get
{
return Session.Query<Client>();
}
}
Where query filters by id = address2_.Id
not client id.
Why search appears by address Id not client?
Try changing one of your two "x" variable names to something different. There is a bug in the current NHibernate release version that could cause this.