Even easiest join gets complicated in NHibernate

Question

Ok, So I have the following database schema Entity schema

And the relation is a one-to-many relation (one Album can have many Images)

The mapping looks something like this:

<property name="NumID"...>
  <.../>
</property>
<set name="Images" inverse="true" cascade="delete" generic="true">
  <key>
    <column name="AlbumID" />
  </key>
  <one-to-many class="Images" />
</set>

And the default fetching is set to lazy

Now to the problem: I want to load the album that holds a picture (to which I know the NumID)

And I would do this with the following SQL:

select * from Images img
join Albums alb
on alb.NumId = img.AlbumId
where img.NumID = 1026

But how would I do it in NHibernate? Of course I have tried in a bunch of different ways with different level of success, but nothing that is as simple as the SQL it selves.

As a aggravating factor I do need the object (Album) as a whole (or at least I need both the NumID and the Name, not lazy loaded)

I have tried this:

/* 1 */
var tmp1 = session.QueryOver<Images>()
   .Where(i => i.NumID == ImageID) //The ImageID is for example 1026
   .JoinQueryOver(i => i.Album)
   .Select(x => x.Album)
   .List<Album>();
// Decent to read, but this only loads the NumID from the album

/* 2 */
var tmp2 = session.Query<Images>()
    .Join(DBContext.GetQuery<Albums>(), i => i.Albums.NumID, a => a.NumID,
        (imgAlias, albAlias) => new {imgAlias, albAlias})
    .Where(x => x.imgAlias.NumID == ImageID)  //ImageID = 1026
    .Select(x => x.albAlias).ToList();
// This one actually works, but it's almost totally unreadable!

How is it supposed to be done?

You want ALL the images of the album you find, or only the image 1026 of the album? — xanatos, Feb 20 '15 at 13:19
@xantos No I only want to load the Album that holds the images wtih NumID = 1026 — Markus, Feb 20 '15 at 13:22
@xantos I don't need any Images, I just need the Name (and NumID) — Markus, Feb 20 '15 at 13:28

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

1

The way (I use mostly, if not only) - is to use subquery (inner SELECT)

 // subquery returning the ALBUM ID
 var sq = QueryOver.Of<Image>()
      .Where(i => i.NumID == ImageID) //The ImageID is for example 1026
      .Select(i => i.Album.Id);       // here we return the Album.ID (column AlbumID)

 // just Albums with searched Image
 var query = QueryOver.Of<Album>()
            .WithSubquery
               .WhereProperty(a => a.Id)
               .In(sq) 
            ...
            .List<Album>();

The biggest advantage is, that the Album result (SELECT) is flat (not joined with multi Images) - so we can easily use paging:

...
.Take(50)
.Skip(50)
.List<Album>();

Check some other subquery QueryOver: select columns from subquery

edited May 23 '17 at 11:50

Community

1
1

answered Feb 20 '15 at 13:19

Radim Köhler

122,561
47
239
335

What is the _Of_ in the query? – Markus Feb 20 '15 at 13:35
Well, that is what you should be able to quickly find out in VS right? This is a **static** method which returns Detached QueryOver... (Detached criteria). These are not related to any session. They are virtual. We can build whatever query we want this way.. but at the end, we have to either bind them to session, or use them as subquery... DO check the DOC: http://nhibernate.info/doc/nh/en/index.html#querycriteria-detachedqueries and http://nhibernate.info/doc/nh/en/index.html#queryqueryover-subqueries – Radim Köhler Feb 20 '15 at 13:36
Ok. I got it and it seem to work. Is this the way you would solve it even if you didn't have the requirement to load the whole object? I mean, isn't there any nicer way to write a simple join in NHibernate? – Markus Feb 20 '15 at 14:21
In the links I've sent you, you can find many ways.. But in general I would say *(and I DO use it this way)* : **1) many-to-one** should be joined, and even better should be projected (only columns from all joined tables are selected) **2) one-to-many** - never join. Always do filter via subqery and then play with plain/flat root object. NHibernate will load the collections ad needed. Check this: [transformer for projections](http://stackoverflow.com/a/24248309/1679310) and that [improve lazy loading](http://stackoverflow.com/a/28556429/1679310) ... Enjoy might NHibernate – Radim Köhler Feb 20 '15 at 14:25
Just a final question. Considering the two "rules" you gave now, how would you handle a many-to-many? Would that be a combined sq and a join? – Markus Feb 20 '15 at 15:32
1

Do you know what? try to search NHiberante many-to-many and do not use it... you will find my name everyhwere: http://stackoverflow.com/a/22563990/1679310, http://stackoverflow.com/a/16827671/1679310... so do not use many-to-many. To answer your question... it should be treated as one-to-many.. but.. (and here are even more links http://stackoverflow.com/a/21136089/1679310 to not use many to many ;) – Radim Köhler Feb 20 '15 at 15:49

Even easiest join gets complicated in NHibernate

1 Answers1

Linked