NHibernate - Duplicate Records with lazily mapped collection

Question

All,

I have an entity, that has several collections,- each collection is mapped lazily. When I run a criteria query, I get duplicate results for my root entity in the result set. How's that possible when all my collections are mapped lazily!

I verified, my collections, load lazily.

Here's my mapping:

Root entity 'Project':

[Bag(0, Lazy = CollectionLazy.True, Inverse = true, Cascade = "all-delete-orphan")]
    [Key(1, Column = "job_id")]
    [OneToMany(2, ClassType = typeof(ProjectPlan))]
    public virtual IList<ProjectPlan> PlanList
    {
        get { return _planList; }
        set { _planList = value; }
    }

The criteria query is:

    ICriteria criteria = session.Session.CreateCriteria<Entities.Project>()
                    .Add(Restrictions.Eq(Entities.Project.PROP_STATUS, !Entities.Project.STATUS_DELETED_FLAG));
                    .CreateAlias(Entities.Project.PROP_PLANLIST, "p")
                    .Add(Restrictions.Eq("p.County", 'MIDDLSEX'))
.setFirstResult(start).setMaxResults(pageSize)
                    .List<Entities.Project>();

I know, I can correct this problem w/ Distinct result transformer, I just want to know if this is normal behavior on lazy collections.

EDIT: I found the cause of this,- when looking at the raw SQL, the join, and where clause are correct but what baffles me is the generated Select clause,- it not only contains columns from the project entity (root entity) but also columns from the project plans entity which causes the issue I described above. I am not at work right now, but I'll try to do this: .SetProjection(Projections.RootEntity()), so I only get Project's columns in the select clause.

I think I found carbon copy of my problem: http://floledermann.blogspot.ca/2007/10/solving-hibernate-criterias-distinct.html — ActiveX, Jan 24 '14 at 05:16
Follow up to the above blog, looks like .setFirstResult(start).setMaxResults(pageSize) are EVIL! — ActiveX, Jan 24 '14 at 05:25

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

One way, how to solve this (I'd say so usual scenario) is: 1) not use fetching collections inside of the query and 2) use batch fetching, as a part of the mapping

So, we will always be querying the root entity. That will give us a flat result set, which can be correctly used for paging.

To get the collection data for each recieved row, and to avoid 1 + N issue (goign for collection of each record) we will use 19.1.5. Using batch fetching

The mapping would be like this

[Bag(0, Lazy = CollectionLazy.True
      , Inverse = true
      , Cascade = "all-delete-orphan"
      , BatchSize = 25)] // Or something similar to batch-size="25"
[Key(1, Column = "job_id")]
[OneToMany(2, ClassType = typeof(ProjectPlan))]
public virtual IList<ProjectPlan> PlanList
{
   ...

Some other similar QA (with the almost same details)

And we still can filter over the collection items! but we have to use subqueries, an example Query on HasMany reference

edited May 23 '17 at 11:49

Community

1
1

answered Jan 24 '14 at 06:36

Radim Köhler

122,561
47
239
335

I have to disagree w/ you,- the batch size only controls how many extra collections to pre-fetch when fetching current collection on given item. If you look at my mapping, I am using lazy loading,- w/out batch size it simply doesn't pre-fetch collections. If you also notice on the cirteria, I am fetching the root entity, .List(), which is the root entity,- I don't understand why it has other columns (other then root entity columns) in the select statement that don't get used when hydrating results. – ActiveX Jan 24 '14 at 18:12
The `.CreateAlias(Entities.Project.PROP_PLANLIST, "p")` is the answer. This is "the fetch" in fact this is the "JOIN", which makes your *root entity* selection multiplied by the amount of items in collections. What I am trying to explain you is: do not fetch (ie do not use `CreateAlias` `CreateCriteria` `SetFetchMode`). All of these will multiply the result. Instead change your mapping. Put **batch-size** on every collection mapping. At the end, you'll get the flat root entity in one SELECT and few more SQL select statements loading (lazy) collections. Is it more clear now? ;) – Radim Köhler Jan 24 '14 at 18:14
.CreateAlias(Entities.Project.PROP_PLANLIST, "p"), I thought, this should of altered only the from, where clauses, but I guess I am wrong. I thought, the batch-size only controlled the pre-fetching, but now your telling me that it also affects the select clause (I did not find that anywhere in hibernate docs)? I did NOT know that, I will definetly try this. Remember, my collections are already mapped lazy. I have other solutions to this like trying SetProjection(RootEntity()), or using subqueries w/ return Ids only that feed the main query on return a List of my root entities. Thanks for help. – ActiveX Jan 24 '14 at 18:55
Please, just a second: Let me clarify that: `CreateAlias`, `CreateCriteria`, `SetFetchMode` are ways how to extend the current SELECT **FROM** clause with more JOINed tables. But a **mapping attribute** `batch-size="25"` resp `BatchSize(25)` *in Fluent* is not effecting current SELECT clause at all. This setting *(the mapping with batch size)* is the way how to **optimize** the expost... lazy loading. Not to go for 1+N records but execute less 1+M statements ... in batches. Hope this helped ;) Good luck with NHibernate – Radim Köhler Jan 24 '14 at 19:03

NHibernate - Duplicate Records with lazily mapped collection

1 Answers1