Nhibernate query with Distinct

Question

I have a problem with a query that return 2 same customer record in a table,, so in my database I got a customer table, a CustomerProduct table and I got a Product table, customer can have many product.

public IPagedList<Customer> SearchCustomer(string product, string address, string county)
        {
            ICriteria criteria = Session.CreateCriteria<Customer>()

            .CreateAlias("CustomerProducts", "cp")
            .CreateAlias("cp.Product", "p");

            if (!string.IsNullOrEmpty(product))
            {
                criteria.Add(Restrictions.Like("p.Name", product));
            }
            if (!string.IsNullOrEmpty(address))
            {
                criteria.Add(Restrictions.Like("Address1", address, MatchMode.Anywhere));
            }
            if (!string.IsNullOrEmpty(county))
            {
                criteria.Add(Restrictions.Like("County", county, MatchMode.Anywhere));
            }

            return criteria.Future<Customer>();
}

above query return a customer record two time, because of customer have many record!! any thought/idea how to fix this, it will be great
thank you

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

There are in fact two approaches:

1) Use the ex post Distinct result transformer:

criteria.SetResultTransformer(NHibernate.Transform.Transformers.DistinctRootEntity);

But I would strongly suggest: do NOT go this way. You'll never be able to apply the paging.

2) Do not JOIN the collections, use BATCH loading for (all) collections

So, instead of JOINing the collections and creating the cartesion product - let's use batch loading:

19.1.5. Using batch fetching

Small cites:

NHibernate can make efficient use of batch fetching, that is, NHibernate can load several uninitialized proxies if one proxy is accessed (or collections. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can tune batch fetching: on the class and the collection level.

Batch fetching for classes/entities is easier to understand. Imagine you have the following situation at runtime: You have 25 Cat instances loaded in an ISession, each Cat has a reference to its Owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call cat.Owner on each, NHibernate will by default execute 25 SELECT statements, to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

or for a collection:

<class name="Person">
    <set name="Cats" batch-size="3">
        ...
    </set>
</class>

Please, for more details, DO observe these:

edited May 23 '17 at 12:05

Community

1
1

answered Aug 20 '14 at 13:12

Radim Köhler

122,561
47
239
335

hmmm i am new to nhiberate would you able to show me how to do it ?? – Sameul.T Aug 20 '14 at 13:28
Well taht is not complicated. Exactly as the code snippet shows. Just mark your collection with `batch-size="25"`. That's it. From that moment do not use CreateAlias for this collection.. just query the root entity. Once you recieve the list of root entities, you can access their collections, and these will be loaded in very few batches... Try to read about batch-size ... really cool feature. I am 1) using batch-size everywhere... 2) never querying root and its collection.. NHiberante then works very fast and effectively (no 1+N selects) – Radim Köhler Aug 20 '14 at 13:31
... ... ,, do i add this XML tag in to Nhibernate.config ? do you have any good example/ link ? thank – Sameul.T Aug 20 '14 at 13:40
Are you using fluent? use this: http://stackoverflow.com/a/20999028/1679310 Are you using mapping by `.hbm.xml` files? use this: http://stackoverflow.com/questions/20970680/ - we are talking about mapping so this setting is in your case related to collection `CustomerProducts` – Radim Köhler Aug 20 '14 at 13:43
still dont get this ... where it should he go in nhernate – Sameul.T Aug 20 '14 at 14:01
Have you seen NHibernate xml mapping? If yes, then you should no `` element. And that element could have an attribute, saying: if possible do load me in batches == `batch-size="25"` so put that almost on every of your class mappings. Nothing else... – Radim Köhler Aug 20 '14 at 14:03
it is not working: mapping.HasMany(x => x.Customers) .KeyColumn("ProductID") .Inverse() .BatchSize(25) .Cascade.AllDeleteOrphan(); – Sameul.T Aug 20 '14 at 14:15
i added .BatchSize(25) in every mapping.cs, customer record is still duplicated – Sameul.T Aug 20 '14 at 14:16
We do have a progress ;) That is great! Have you narrowed the criteria? Have you removed the `CreateAlias`? Do it. Then you will recieve just a root - clear resultset... Once you start to access the hasMany collections, they will be loaded in batches – Radim Köhler Aug 20 '14 at 14:21
1

Great to see that, sir! ;) Enjoy NHibernate... amazing tool ;) – Radim Köhler Aug 20 '14 at 14:42
i have a question for you, now i removed createAlias(.CreateAlias("Products", "cp") .CreateAlias("cp.Product", "p");) and how am i able to do this ? criteria.Add(Restrictions.Like("p.Name", product)); – Sameul.T Aug 20 '14 at 14:56
Yes, expectable question ;) not a problem. But solution will be a bit more complex - using subquery. **Strongly suggest find some articles about it**, also there is my the mostcomplex example, how powerfull it is http://stackoverflow.com/questions/20528760/ (but you will find more examples here, not only by me ...;) – Radim Köhler Aug 20 '14 at 15:00
can u help me out here !! can i add the subquery to criteria ? – Sameul.T Aug 20 '14 at 15:58
I would suggest: please, do not hesitate and issue new query. really. Not only me, but also others NHibernate experts can observe what you have... and show you what to do next. Would you agree? ;) If I will know, I wll give you my answeer! ;) – Radim Köhler Aug 20 '14 at 16:00

Nhibernate query with Distinct

1 Answers1