NHibernate N+1 fetch problem
I have a entity and fluent mapping that look like this.
public class Client : EntityWithTypedId<long>
{
[Length(Max=50)]
public virtual string GivenName { get; set; }
public virtual IList<Address> Addresses { get; set; }
}
public class ClientMap : ClassMap<Client>
{
public ClientMap()
{
Schema("dbo");
Table("Client");
Id(x => x.Id, "ClientId").GeneratedBy.Identity();
Map(x => x.GivenName, "GivenName");
HasManyToMany(x => x.Addresses)
.FetchType.Join()
.Cascade.AllDeleteOrphan()
.Table("ClientAddress")
.ParentKeyColumn("ClientId")
.ChildKeyColumn("AddressId")
.AsBag();
}
}
I then execute an ICriteria query like this
return Session.CreateCriteria<Client>()
.CreateAlias("Organisation", "o").SetFetchMode("o", FetchMode.Join)
.CreateAlias("Addresses", "a").SetFetchMode("a", FetchMode.Join)
.Add(expression)
.AddOrder(Order.Asc("Surname")).AddOrder(Order.Asc("GivenName"))
.SetResultTransformer(new DistinctRo开发者_JAVA百科otEntityResultTransformer())
.SetMaxResults(pageSize)
.SetFirstResult(Pagination.FirstResult(pageIndex, pageSize))
.Future<Client>();
Using NHProf I can see it executes a query like this which should return all client details and addresses
SELECT top 20 this_.ClientId as ClientId5_2_,
this_.GivenName as GivenName5_2_,
addresses4_.ClientId as ClientId,
a2_.AddressId as AddressId,
a2_.AddressId as AddressId0_0_,
a2_.Street as Street0_0_,
a2_.Suburb as Suburb0_0_,
a2_.State as State0_0_,
a2_.Postcode as Postcode0_0_,
a2_.Country as Country0_0_,
a2_.AddressTypeId as AddressT7_0_0_,
a2_.OrganisationId as Organisa8_0_0_,
o1_.OrganisationId as Organisa1_11_1_,
o1_.Description as Descript2_11_1_,
o1_.Code as Code11_1_,
o1_.TimeZone as TimeZone11_1_
FROM dbo.Client this_
inner join ClientAddress addresses4_
on this_.ClientId = addresses4_.ClientId
inner join dbo.Address a2_
on addresses4_.AddressId = a2_.AddressId
inner join dbo.Organisation o1_
on this_.OrganisationId = o1_.OrganisationId
WHERE (o1_.Code = 'Demo' /* @p4 */
and (this_.Surname like '%' /* @p5 */
or (this_.HomePhone = '%' /* @p6 */
or this_.MobilePhone = '%' /* @p7 */)))
ORDER BY this_.Surname asc,
this_.GivenName asc
Which returns all the records as expected
However if i then write code like
foreach(var client in clients)
{
if (client.Addresses.Any())
{
Console.WriteLn(client.Addresses.First().Street);
}
}
I still get an N+1 issue where it does a select on each address. How can I avoid this?
I think you're misunderstanding what's going on here...it's almost always incorrect to use the distinct result transformer in conjunction with paging. Think about it, you're only getting the first 20 rows of a cross-product given that query above. I'm guessing that several of your clients at the end of the list aren't having their collections populated because of this, leading to your N+1 issue.
If you need to do the paging operation, consider using the batch-size
hint on your collection mapping to help minimize the N+1 issue.
Note: if your typical use case is to show pages of 20 at a time, set the batch-size
to this value.
When you use CreateAlias(collection)
, SetFetchMode(collection)
has no effect.
For a better approach to eager loading of collections, see http://ayende.com/Blog/archive/2010/01/16/eagerly-loading-entity-associations-efficiently-with-nhibernate.aspx
精彩评论