Cartesian product with eager fetching

[Michael Teper]

I am trying a bunch of new (to me) feature of NHibernate and keep
running into problems. I am not sure whether this one is a bug or user
error, but when trying to eagerly fetch an entity with its collection
using the new Linq provider, I get what looks like a cartesian
product.

Parent entity -- "Operation":
public class Operation
{
public virtual Guid Id { get { return _id; } }
...
public virtual IList<OperationStep> Steps { get _steps; }
}

Child entity -- "OperationStep":
public abstract class OperationStep
{
public virtual Guid Id { get { return _id; } }
public virtual Operation Operation { get { return _operation; } }
...
}

public class InsertBatchStep : OperationStep
{
...
}

Linq query:
var operations = (from op in session.Query<Operation>
orderby op.DateCreated
select op).FetchMany(op =>
op.Steps).ToArray();

SQL:
select operation0_.Id as Id0_0_,
steps1_.Id as Id1_1_,
operation0_.DateCreated as DateCrea2_0_0_,
operation0_.Creator as Creator0_0_,
operation0_.Status as Status0_0_,
steps1_.OperationId as Operatio3_1_1_,
steps1_.StepOrder as StepOrder1_1_,
steps1_.ExecutionCount as Executio5_1_1_,
steps1_.DateOfNextExecution as DateOfNe6_1_1_,
steps1_.DateOfLastExecution as DateOfLa7_1_1_,
steps1_.Status as Status1_1_,
steps1_.LastError as LastError1_1_,
steps1_.Type as Type1_1_,
steps1_.OperationId as Operatio3_0__,
steps1_.Id as Id0__
from [Operation] operation0_
left outer join OperationStep steps1_
on operation0_.Id = steps1_.OperationId
order by operation0_.DateCreated asc


The Operation database table contains one operation, and the
OperationStep table contains four steps. The query returns 4
operations objects, each with 4 steps.

Mappings:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="FabricController" namespace="FabricController">
<class name="Operation" table="Operation">
<id name="Id">
<generator class="guid.comb" />
</id>

<property name="DateCreated" access="nosetter.camelcase-underscore" /
>
<property name="Creator" access="nosetter.camelcase-underscore" />
<property name="Status" access="nosetter.camelcase-underscore" />

<bag name="Steps" table="OperationStep" access="nosetter.camelcase-
underscore">
<key column="OperationId" />
<one-to-many class="OperationStep" />
</bag>
</class>
</hibernate-mapping>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="FabricController" namespace="FabricController">
<class name="OperationStep" table="OperationStep">
<id name="Id">
<generator class="guid.comb" />
</id>

<discriminator column="Type" />

<many-to-one name="Operation" column="OperationId"
access="nosetter.camelcase-underscore" />

<property name="StepOrder" access="nosetter.camelcase-underscore" />
<property name="ExecutionCount" />
<property name="DateOfNextExecution" />
<property name="DateOfLastExecution" />
<property name="Status" />
<property name="LastError" />

<subclass name="OperationSteps.ConfirmBatchIndexedStep">
</subclass>

<subclass name="OperationSteps.InsertBatchStep">
</subclass>

<subclass name="OperationSteps.LoadBatchDataStep">
</subclass>

<subclass name="OperationSteps.PurgeDataStep">
</subclass>
</class>
</hibernate-mapping>

So is this me? What am I doing wrong?

Thanks in advance!

-Michael

[Darren Kopp]

That is working correctly. Since you want to load all of the child items, you are getting all the child items, and you also have the data for the parent entity. nhibernate will rehydrate it into the correct form so that you'll have 1 operation with 4 steps using 1 query.

[Michael Teper]

Except that I don't. I end up with *four* operations, *each* with four
steps.

-Michael

[Ricardo Peres]

I think that's because you are using a bag. Try using a set instead,
it is a little change.
Otherwise, you will have to use the DistinctRootTransformer.

RP

[Michael Teper]

I have tried with both a set and a bag, no difference. How do you
apply a transformer to a LINQ query?

-Michael

> > > you'll have 1 operation with 4 steps using 1 query.- Hide quoted text -
>
> - Show quoted text -

[Ricardo Peres]

Can you sent your mappings? Using a set should have done it, it is a
known issue.

[Mohamed Meligy]

If by Cartesian product you mean the  join, then yes, this is how NHibernate do it.

Is there a way to change that? Not in NHibenate stuff.

I do it (actually been experimenting this for a few days) by making the collection property settable, having two future queries (calling .ToFututre() on LINQ query or Future() on QueryOVer query) one for parent and another for child, then mapping the child entities to parent manually in a loop.

If you are worried about multiple fetch paths, then you should do one query per fetch path, this will make a cartisian product for the parent entity and child path in the query only. which is better than getting cartestian product of all paths at the same time. You'll not need to map the child paths from different queries to the parent as, since parent is already returned in each queries, and NH keeps only one instance of each object, the parent from any of the queries will already have all child collections populated from different queries loaded into it.

If you try to load the children only without the parent though, it will not map them to the parent coming from other queries then. Because it only does that when the return of the query requires creating new instance of the parent (or reusing existing if already there from another query).

 

Mohamed Meligy
Readify | Senior Developer

M:+61 451 835006 | W: readify.net

    

--
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.

[Michael Teper]

Ricardo, I included the mappings at the start of the thread. The
mapping is for a bag, but if I substitute "set" and update the class
definition accordingly with ISet / HashedSet, I end up with the same
result. (incidentally, why would set and bag behave differently? is
there an open JIRA case?)

Thank you!
-Michael

> > > - Show quoted text -- Hide quoted text -

[Mohamed Meligy]

Michael,

The reason he thinks so is that it is actually different in queries sometimes, like it helps avoiding some duplicates and so.

I tend to say it is the recommended default (but not only option) for collections.

 

However, I still agree it's not related to your case. Just add the test (with Set so that NH team doesn't have multiple suspects) to a gira ticket.

For the actual problem that produced this, you may try two queries with "Future" to batch them.and matching in DB, or a manual Join and then later grouping in code also.

Mohamed Meligy
Readify | Senior Developer

M:+61 451 835006 | W: readify.net

    

[CSharper]

Anyway I would say this is a bug. Don't you?

On 29 Mai, 18:43, Mohamed Meligy <eng.mel...@gmail.com> wrote:
> If by Cartesian product you mean the  join, then yes, this is how NHibernate
> do it.
>
> Is there a way to change that? Not in NHibenate stuff.
> I do it (actually been experimenting this for a few days) by making the
> collection property settable, having two future queries (calling
> .ToFututre() on LINQ query or Future() on QueryOVer query) one for parent
> and another for child, then mapping the child entities to parent manually in
> a loop.
>
> If you are worried about multiple fetch paths, then you should do one query
> per fetch path, this will make a cartisian product for the parent entity and
> child path in the query only. which is better than getting cartestian
> product of all paths at the same time. You'll not need to map the child
> paths from different queries to the parent as, since parent is already
> returned in each queries, and NH keeps only one instance of each object, the
> parent from any of the queries will already have all child collections
> populated from different queries loaded into it.
>
> If you try to load the children only without the parent though, it will not
> map them to the parent coming from other queries then. Because it only does
> that when the return of the query requires creating new instance of the
> parent (or reusing existing if already there from another query).
>

> *Mohamed Meligy
> *Readify | Senior Developer

>
> M:+61 451 835006 | W: readify.net
> [image: Description: Description: Description: Description:

> rss_16]<http://gurustop.net>

> [image: Description: Description: Description: Description:

> cid:image003....@01CAF81D.6A076510]
> <http://www.linkedin.com/in/meligy>  [image:
> Description: Description: Description: Description:
> cid:image005....@01CAF81D.6A076510] <http://twitter.com/meligy>
>  <http://www.greatplacetowork.com.au/best/best-companies-australia.php><http://www.readify.net/AboutUs/NewsItem.aspx?id=10>

[Michael Teper]

This would be the QueryOver equivalent of my LINQ, right? I get the
same result as described earlier:

var operations = session
.QueryOver<Operation>()
.OrderBy(o => o.DateCreated).Asc
.Fetch(o => o.Steps).Eager
.TransformUsing(Transformers.DistinctRootEntity)
.List<Operation>();

-Michael

> > >http://groups.google.com/group/nhusers?hl=en.- Hide quoted text -

[Michael Teper]

Good news, sort of. I tracked down the cause to the use of stateless
session (I get 1 result instead of 4 using the regular session). Is
there a reason not to do the same transformation in the stateless
session that's done in the regular session?

Should I submit to JIRA?

Thanks!
-Michael

> > > >http://groups.google.com/group/nhusers?hl=en.-Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -