NHibernate Forge
The official new home for the NHibernate for .NET community

Get unique results from joined queries

Attachments

Wiki Page Hierarchy

Pages

Page Details

First published by:
Ken Egozi
on 09-12-2008
Last revision by:
John Davidson
on 09-07-2011
9 people found this article useful.
Article
Comments (12)
History (6)
90% of people found this useful

Get unique results from joined queries

Filed under: [Edit Tags]

Problem:

A recurring issue that new NHibernate users keep asking about, is the fact that they sometimes get duplicate results from their queries

For example, take a look at the next model:

where Comment.Name refer to the name of the person who wrote the comment.

If you'd want to issue a query on Post that joins to Comment, say "all of the posts that were commented by 'Ken Egozi'", the next hql query should do the trick:

   select  p
   from    Post p
   join    p.Comments c
   where   c.Name = 'Ken Egozi'";
 
So, you go ahead and
   var hql = @"
      select  p
      from    Post p
      join    p.Comments c
      where   c.Name = 'Ken Egozi'";
   var posts = session.CreateQuery(hql)
      .List<post>();

Assuming the next data is in the DB:

  Post#1
Comment by 'Ken Egozi' Comment by 'Fabio Maulo' Comment by 'Ken Egozi' Post#2 Comment by 'Fabio Maulo' Post#3 Comment by 'Fabio Maulo' Comment by 'Ken Egozi'

you'd expect the list 'posts' to include two posts: Post#1 and Post#3.
However, if you'd look into posts, you'll see that it has three entries, two entries pointing to Post#1 and one entry pointing to Post#3 

Note that NHibernate is smart enough not to create two separate instances for Post#1, but rather a single instance will be created, and the two entries in the list will point to the same instance in memory.

Explanation:

The said hql query (or an equivalent Criteria) is translated to the joined SELECT clause

   SELECT  {p.*}
   FROM    Posts p
     JOIN  Comments c ON c.PostId = p.Id
   WHERE   c.[Name] = 'Ken Egozi'
 

where {p.*} is substituted by the list of fields needed to satisfy a Post object. Running this SQL query directly on the data detailed above, will result with three lines in the resultset.

Solutions

There are three solutions to that in SQL world:

  1. The DISTINCT keyword, when added on the fields of Posts, will ensure that there will be no duplicate lines. there are a few problems with that. Some RDBMS systems will not allow DISTINCT over certain data types, like the blob types in SQL Server. And if you also need to retrieve the joined data, then the DISTINCT keyword will be useless.
  2. Using a subselect
       SELECT *
       FROM   Posts p
       WHERE EXISTS
       (
          SELECT 1
          FROM   Comments c
          WHERE  c.PostId = p.Id
            AND  c.[Name] = 'Ken Egozi'
       )
  3. Remove duplicates in the client code.

The solutions in NHibernate's world, in accordance to the SQL solutions:

  1. Use the 'distinct' keyword.
    The problems that exists in the SQL world still apply, and it can only be used in HQL, not it Criteria API.
  2. Use subselects.
    The syntax in HQL:
       from   Post p
       where exists
       (
          from Comment c
          where c.Post = p
          and c.Name = 'Ken Egozi'
       )
    and in Criteria API:
     var commentsByKenEgozi = DetachedCriteria.For<comment>()
         .Add(Restrictions.Eq("Name", "Ken Egozi"))
         .Add(Restrictions.EqProperty("Post.Id", "p.Id"))
         .SetProjection(NHibernate.Criterion.Projections.Id());
     
      var posts = session.CreateCriteria(typeof (Post), "p")
         .Add(Subqueries.Exists(commentsByKenEgozi))
         .List<post>();</comment>
  3. Remove duplicates in client code.
    That's actually pretty easy with NHibernate, thanks to the result transformers. all you need to do is add .SetResultTransformer(new DistinctRootEntityResultTransformer()) to either your query, or criteria, and NHibernate will remove duplicate entries from the resulted list.
    so, using the same hql as in the first example, you can write
     var hql = @"
          select  p
          from    Post p
          join    p.Comments c
          where   c.Name = 'Ken Egozi'";
       var posts = session.CreateQuery(hql)
          .SetResultTransformer(new DistinctRootEntityResultTransformer())
          .List<post>();

Recent Comments

By: Steven Posted on 04-10-2012 16:44

Agree, very cool... this tutoriual was awesome by they way. It helped me a lot.

Best Regards,

www.odkryjjakschudnac.pl

By: monaq Posted on 01-17-2012 5:06

[url=http://www.coolweblinkx.com]Web Directory[/url]

I enjoy reading it. I have need of to learn more on this subject.. Thanks in return theme this marvellous post.. Anyway, I am gonna subscribe to your silage and I desire you post again soon.

By: monaq Posted on 01-17-2012 5:05

I enjoy reading it. I have need of to learn more on this subject.. Thanks in return theme this marvellous post.. Anyway, I am gonna subscribe to your silage and I desire you post again soon.

<a href="http://www.coolweblinkx.com">Web Directory</a>

By: suzettech Posted on 01-04-2012 6:26

This was a great help and resource. Thanks, I bookmarked.

vacationhome-rentals.com

By: Carsten Alder Posted on 09-12-2011 9:17

Hi jbs,

how can I use the DistinctRootEntityResultTransformer when using Linq-Queries. I use the eager fetching extension methods they will be used automatically, but how can I apply it when using the following query

var query = from master in testObject.Queryable

                from detail in master.Details

                select master;

Master and Detail have got a one to many relationship.

Should I use distinct as follows?

              IList<Master> result = query.ToList();

              IEnumerable<Master> distinctResult = result.Distinct();

              result = distinctResult.ToList();

Furthermore, how can I restrict on details when fetching eager:

var query = from master in testObject.Queryable

                                                   .FetchMany(m => m.Details)

// how to do this?                           where detail.Value == 'value1'

                                                   select master;

Thanks a lot in advance

Carsten

View All
Powered by Community Server (Commercial Edition), by Telligent Systems