NHibernate - selecting data from referenced objects (traversing many-to-one relationship) in a query

Question

i want to build a query that will select some columns from a joined table (many to one relationship in my data model).

var q = ses.QueryOver<Task>().Select(x => x.Id, x => x.CreatedDate, x => x.AssigneeGroup.Name, x => x.AssigneeGroup.IsProcessGroup);

Here i'm retrieving properties from AssigneeGroup which is a reference to another table, specified in my mapping. But when I try to run this query I get

Exception: could not resolve property: AssigneeGroup.Name of: Task

So it looks like NHibernate is not able to follow relations defined in my mapping and doesn't know that in order to resolve AssigneeGroup.Name we should do a join from 'Task' to 'Groups' table and retrieve Group.Name column. So, my question is, how to build such queries? I have this expression: x => x.AssigneeGroup.Name, how to convert it to proper Criteria, Projections and Aliases? Or is there a way to do this automatically? It should be possible because NHibernate has all the information...

If you wrote this as a Criteria query, you could use the `SetFetchMode` method with a Join, and you'd prolly be good. — Jeremy Holovacs, Mar 06 '14 at 17:57

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

1

Your query need association and should look like this:

// firstly we need to get an alias for "AssigneeGroup", to be used later
AssigneeGroup assigneeGroup = null;

var q = ses
    .QueryOver<Task>()
    // now we will join the alias
    .JoinAlias(x => x.AssigneeGroup, () => assigneeGroup) 
    .Select(x => x.Id
          , x => x.CreatedDate

          // instead of these
          // , x => x.AssigneeGroup.Name
          // , x => x.AssigneeGroup.IsProcessGroup

          // use alias for SELECT/projection (i.e. ignore "x", use assigneeGroup)
          , x => assigneeGroup.Name
          , x => assigneeGroup.IsProcessGroup
     );

More and interesting reading:

NHibernate - CreateCriteria vs CreateAlias, to get more understanding when to use JoinAlias (CreateAlias) and when JoinQueryOver (CreateCriteria)
Criteria API for: 15.4. Associations
QueryOver API for 16.4. Associations

edited May 23 '17 at 11:49

Community

1
1

answered Mar 07 '14 at 05:16

Radim Köhler

122,561
47
239
335

This is probably the correct answer. Unfortunately, NHIbernate is not able to follow property chains like x.AssigneeGroup.Name, you have to make an alias to tell it that AssigneeGroup is a referenced entity. But NH has this information, it's right there in the mapping, so it's a shame you have to do this by hand. – nightwatch Mar 07 '14 at 12:49
I know what you are talking about. I was missing it also... (if this is a part of the "x.y.z" chain... why NHibernate does not create the association? I agree.. ;) But that is how NHibernate works right now... r – Radim Köhler Mar 07 '14 at 15:20

Cole W · Answer 2 · 2014-03-06T20:43:07.390

You have to join the two tables if you wish to select columns from something other than the root table/entity (Task in our case).

Here is an example:

IQueryOver<Cat,Kitten> catQuery =
    session.QueryOver<Cat>()
        .JoinQueryOver<Kitten>(c => c.Kittens)
            .Where(k => k.Name == "Tiddles");

or

Cat catAlias = null;
Kitten kittenAlias = null;

IQueryOver<Cat,Cat> catQuery =
    session.QueryOver<Cat>(() => catAlias)
        .JoinAlias(() => catAlias.Kittens, () => kittenAlias)
        .Where(() => catAlias.Age > 5)
        .And(() => kittenAlias.Name == "Tiddles");

Alternatively you could use the nhibernate linq provider (nh > 3.0):

var q = ses.Query<Task>()
           .Select(x => new 
           { 
               Id = x.Id, 
               CreatedDate = x.CreatedDate, 
               Name = x.AssigneeGroup.Name, 
               IsProcessGroup = x.AssigneeGroup.IsProcessGroup
           });

NHibernate - selecting data from referenced objects (traversing many-to-one relationship) in a query

2 Answers2