CreateCriteria and loading question

[fknebels]

I've got a iCriteria query that joins 7 different tables. The SQL it
generates is exactly what I want. I thought I would be able to
populate the entire object tree from this query, but as I iterate over
child objects I see queries getting sent to SQL server using NHProf.
How do I get this ICriteria to load the entire object tree? This is
the query:

return session.CreateCriteria(typeof(MatchRuleGroup))
.Add(Restrictions.Eq("Name", Name.Trim()))
.CreateAlias("GroupSets", "groupsSet",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateCriteria("groupsSet.RuleSet", "ruleset",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("ruleset.Comparisons", "comparison",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateCriteria("comparison.Rule", "rule",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateCriteria("rule.Field",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateCriteria("rule.Pattern",
NHibernate.SqlCommand.JoinType.InnerJoin)
.SetResultTransformer(new
NHibernate.Transform.RootEntityResultTransformer());

Thanks,
Fran

[Jason Meckley]

if you want to select the data you need to set the fetch mode, not the
join mode. however you may find that loading everything in a single
query may be sub-optimal. you will end up with a Cartesian result set
(lot of duplicated data). If that is the case you could try other
techniques: ado.net batching (this may only apply to writes, not
reads), multi-queries, sub-select batching, filters. Even though you
execute multiple remote db calls, the results sets are unique and the
throughput could increase.

also, if your collections use a List instead of a Set you may get
duplicate items in the collections. A Set is the only collection that
ensure uniqueness.

[fknebels]

FetchMode just adds an outer join to the query which will result in a
Cartesian project which I definitely don't want. The SQL that is
generated is exactly what I want. I have all the fields for every
object in the object graph. I thought that the RootEntityTransformer
would then populate the entire object graph with actually object and
not proxies, but when I iterate on the a one to many relationship, I
see each object in the graph calling out to sql server. I shouldn't
need other techniques because the generated SQL returns all the fields
I need. That's the whole point of writing this query and not just
walking through every object.

[BZaino]

I have a similar issue with my criteria. I have a Person entity that
has a sub object of a PersonAddress entity. For example, I want to
get all person records that have a Person.CustomerId = 1 and a
PersonAddress.State = "MA". I expect to get back one Person record
with one PersonAddress record in my sub-object. However, I have 2
records in the PersonAddress table, one with a state of MA, and
another with a state of CT. When I run my search using HQL and browse
the PersonAddress collection within my Person entity, I see only 1
address. However, if I use ICriteria to do my search and browse the
PersonAddress collection, I see both PersonAddress records. In
addition, I can confirm that a second fetch is done to the DB to
retrieve ALL of the PersonAddress records. Why is this so?

The code that I use to generate the ICriteria:

ICriteria criteria = Session.CreateCriteria(typeof
(Person), "pers");

DetachedCriteria sub = DetachedCriteria.For<PersonAddress>
("persAddr");
sub.Add(Expression.Conjunction()
.Add(Expression.Like("persAddr.State", "MA"))
.Add(Expression.EqProperty("persAddr.PersonID",
"pers.PersonID")));

sub.SetProjection(Projections.Id());

criteria.SetResultTransformer(new
NHibernate.Transform.DistinctRootEntityResultTransformer());

retObject = criteria.List<Person>() as List<Person>;

Many thanks in advance

On Nov 7, 11:05 am, fknebels <fkneb...@gmail.com> wrote:
> FetchMode just adds an outer join to the query which will result in a
> Cartesian project which I definitely don't want. The SQL that is

> generated is exactly what I want. I have all the fields for everyobjectin theobjectgraph. I thought that the RootEntityTransformer

> would then populate the entireobjectgraph with actuallyobjectand
> not proxies, but when I iterate on the a one to many relationship, I

> see eachobjectin the graph calling out to sql server. I shouldn't

> need other techniques because the generated SQL returns all the fields
> I need. That's the whole point of writing this query and not just
> walking through everyobject.
>
> On Nov 6, 5:15 pm, Jason Meckley <jasonmeck...@gmail.com> wrote:
>
> > if you want to select the data you need to set the fetch mode, not the
> > join mode. however you may find that loading everything in a single

> > query may besub-optimal. you will end up with a Cartesian result set

> > (lot of duplicated data). If that is the case you could try other
> > techniques: ado.net batching (this may only apply to writes, not

> > reads), multi-queries,sub-select batching, filters. Even though you

> > execute multiple remote db calls, the results sets are unique and the
> > throughput could increase.
>
> > also, if your collections use a List instead of a Set you may get
> > duplicate items in the collections. A Set is the only collection that
> > ensure uniqueness.
>
> > On Nov 6, 4:37 pm, fknebels <fkneb...@gmail.com> wrote:
>
> > > I've got a iCriteria query that joins 7 different tables. The SQL it
> > > generates is exactly what I want. I thought I would be able to

> > > populate the entireobjecttree from this query, but as I iterate over

> > > child objects I see queries getting sent to SQL server using NHProf.

> > > How do I get this ICriteria to load the entireobjecttree? This is

[Zeljko]

You could add:

.SetFetchMode("TableX", FetchMode.Join)

to your Criteria.So you can control when the whole object tree is
loaded based
on a specific query

[BZaino]

Thanks for the tip. However, that did not fix the issue. I added the
following line:

criteria.SetFetchMode("PersonAddress", FetchMode.Join);

When I view the PersonAddress collection within the Person record, I
still see the extra fetch in the SQL profilier, and I see 2
PersonAddress records in the collection.

> > > > join mode. however you may find thatloadingeverything in a single