Eager Loading Using Fluent NHibernate/Nhibernate & Automapping

[Nabeel]

Hi guys,

I have a requirement to load a complex object called Node...well its
not that complex...it looks like follows:-

A Node has a reference to EntityType which has a one to many with
Property which in turn has a one to many with PorpertyListValue

public class Node
{
public virtual int Id
{
get;
set;
}

public virtual string Name
{
get;
set;
}

public virtual EntityType Etype
{
get;
set;
}

}


public class EntityType
{
public virtual int Id
{
get;
set;
}

public virtual string Name
{
get;
set;
}

public virtual IList<Property> Properties
{
get;
protected set;
}

public EntityType()
{
Properties = new List<Property>();
}
}

public class Property
{
public virtual int Id
{
get;
set;
}

public virtual string Name
{
get;
set;
}

public virtual EntityType EntityType
{
get;
set;
}

public virtual IList<PropertyListValue> ListValues
{
get;
protected set;
}

public virtual string DefaultValue
{
get;
set;
}

public Property()
{
ListValues = new List<PropertyListValue>();
}
}


public class PropertyListValue
{
public virtual int Id
{
get;
set;
}

public virtual Property Property
{
get;
set;
}

public virtual string Value
{
get;
set;
}

protected PropertyListValue()
{
}
}

What I a trying to do is load the Node object with all the child
objects all at once. No Lazy load. The reason is I have thousands of
Node objects in the database and I have to send them over the wire
using WCF Service.I ran into the classes SQL N+ 1 problem. I am using
Fluent Nhibernate with Automapping and NHibernate Profiler suggested
me to use FetchMode.Eager to load the whole objects at once. I am
using the following qyuery

Session.CreateCriteria(typeof (Node))
.SetFetchMode( "Etype", FetchMode.Join )
.SetFetchMode( "Etype.Properties", FetchMode.Join )
.SetFetchMode( "Etype.Properties.ListValues",
FetchMode.Join )

OR using NHibernate LINQ

Session.Linq<NodeType>()
.Expand( "Etype")
.Expand( "Etype.Properties" )
.Expand( "Etype.Properties.ListValues" )

When I run any of the above query, they both generate one same single
query with all the left outer joins, which is what I need. However,
for some reason the return IList from the query is not being loaded
property into the objects. Infact the returned Nodes count is equal to
the number of rows of the query, so the Nodes objects are
repeated.Moreover, the properties within each Node are repeated, and
so do the Listvalues.

So I would like to know how to modify the above query to return all
unique Nodes with the properties and list values within them.

Thanks
Nabeel

[Fabio Maulo]

which is the strange thing ?

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo

[Nabeel]

Hi Fabio,

As I said, the query that is generated is a single query with all the
left outer joins, which is what I need. However, the thing that is
strange is:

"The returned IList<Node>.Count from the query is equal to the number
of rows of the query. So e.g. if I have 1000 rows in the Node table,
all of them having a reference to one entity type row in entity type
table. The entity type row has two properties in the Property table,
the IList returned by the query has nearly 2000 Nodes, and the entity
type object in each node has nearly 2000 properties and so on.

I would like to know how to tell NHibernate not to load duplicate
Nodes and Properties within them?

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com­>
> > .

> > For more options, visit this group at
> >http://groups.google.com/group/nhusers?hl=en.
>
> --

> Fabio Maulo- Hide quoted text -
>
> - Show quoted text -

[Nabeel]

My main goal is to avoid Sql N+1 issue. Because I am sending thousands
of Node objects to the client via wcf, I do not want to load 1000s of
nodes objects first and then for each node load entityType and then
for each entity type load properties and so on. I want to load the
nodes and all child objects using as less queries as possible ideally
one single query.

Awaiting
Nabeel

[Nabeel]

In short I would like to resolve SQL N+1 issue here and I don't want
NHiberneate to load duplicate nodes and duplicate child objects within
it.

Nabeel Farid

On Jun 30, 9:33 am, Nabeel <nabeelfa...@gmail.com> wrote:

[Nabeel]

On google I found out about DistinctRootEntityResultTransformer but
that only resolve the issue for the Root objects. I am still getting
duplicates in the child collections. Every root object in the
returned list has some weird Cartesian product mess in the child
collections with multiple instances of the same entity. Any idea?

Awaiting
Nabeel

[Fabio Maulo]

Try it using HQL and you will see the exception.

You can use eager fetch with multiple collections when one of those collection is a bag

Nabeel

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo

[Nabeel]

Hi Fabio,

I am not sure what you meant by using hql and using eager fetch with
multiple collections here?

I think I have found the solution but I would like to know if its the
correct one.

The child collections (EType.Properties, Etype.Properties.ListValues)
inside root object (Node) are IList. And i read in the documentation
that IList can contain duplicates, so if i change IList to ISet/
ICollection, then the query does not load duplicate instances within
the child collections.

But this solution requires alot of refactoring. I would like to know
if there is a way to achieve the same using IList for child
collections?

Awaiting,
Nabeel

On Jun 30, 12:48 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> Try it using HQL and you will see the exception.
> You can use eager fetch with multiple collections when one of those
> collection is a bag
>
>
>
> On Wed, Jun 30, 2010 at 8:37 AM, Nabeel <nabeelfa...@gmail.com> wrote:
> > On google I found out about DistinctRootEntityResultTransformer but
> > that only resolve the issue for the Root objects. I am still getting
> > duplicates in the child collections. Every root object in the
> > returned  list has some weird Cartesian product mess in the child
> > collections with multiple instances of the same entity. Any idea?
>
> > Awaiting
> > Nabeel
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>
> > .

[Nabeel]

Hi Fabio,

Could you please let me know if my solution is correct? Or is there a
way that I can implement my query using IList instead of ISet/
ICollection on the child collections ?

Awaiting

Nabeel


On Jun 30, 12:48 pm, Fabio Maulo <fabioma...@gmail.com> wrote:

> Try it using HQL and you will see the exception.
> You can use eager fetch with multiple collections when one of those
> collection is a bag
>
>
>
>
>
>
>
> On Wed, Jun 30, 2010 at 8:37 AM, Nabeel <nabeelfa...@gmail.com> wrote:
> > On google I found out about DistinctRootEntityResultTransformer but
> > that only resolve the issue for the Root objects. I am still getting
> > duplicates in the child collections. Every root object in the
> > returned  list has some weird Cartesian product mess in the child
> > collections with multiple instances of the same entity. Any idea?
>
> > Awaiting
> > Nabeel
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>
> > .