How select referenced entity in nhibernate queryover

Question

I Have a entity with a property referencing other entity (ReferenceEntity in examples).

With HQL i can do this:

select e.ReferenceEntity from Entity e where e.Id = :entityId

NHibernate will give me the ReferenceEntity instance without lazy.

With query over im trying do this:

Session.QueryOver<Entity>()
.Where(e => e.Id == entityId)
.Select(e => e.ReferenceEntity)
.SingleOrDefault<ReferenceEntity>()

With QueryOver Nhibernate is giving me the ReferenceEntity but lazy.

I wanna get ReferenceEntity with eager loading using queryover like i do with hql.

Thanks

Daniel Schilling · Accepted Answer · 2012-03-21T17:20:35.100

12

Suggestion #1

You could do a little bit of LINQ manipulation after you execute the query to grab the data you want.

var result = Session.QueryOver<Entity>()
    .Where(e => e.Id == entityId)        // Filter,
    .Fetch(e => e.ReferenceEntity).Eager // join the desired data into the query,
    .List()                              // execute database query,
    .Select(e => e.ReferenceEntity)      // then grab the desired data in-memory with LINQ.
    .SingleOrDefault();
Console.WriteLine("Name = " + result.Name);

It's simple and gets the job done.

In my test, it resulted in a single query. Here's the output:

SELECT
    this_.Id as Id0_1_, this_.Name as Name0_1_, this_.ReferenceEntity_id as Referenc3_0_1_,
    q5379349_r2_.Id as Id1_0_, q5379349_r2_.Name as Name1_0_
FROM
    [Entity] this_
    left outer join [ReferenceEntity] q5379349_r2_
        on this_.ReferenceEntity_id=q5379349_r2_.Id
WHERE this_.Id = @p0;

Suggestion #2

Another approach would be to use an EXISTS subquery, which would be slightly more complex, but would return the right result the first time without any need for post-database manipulation:

ReferenceEntity alias = null;
var result = Session.QueryOver(() => alias)
    .WithSubquery.WhereExists(QueryOver.Of<Entity>()
        .Where(e => e.Id == entityId)                 // Filtered,
        .Where(e => e.ReferenceEntity.Id == alias.Id) // correlated,
        .Select(e => e.Id))                           // and projected (EXISTS requires a projection).
    .SingleOrDefault();
Console.WriteLine("Name = " + result.Name);

Tested - results in single query:

SELECT this_.Id as Id1_0_, this_.Name as Name1_0_
FROM [ReferenceEntity] this_
WHERE exists (
    SELECT this_0_.Id as y0_
    FROM [Entity] this_0_
    WHERE this_0_.Id = @p0 and this_0_.ReferenceEntity_id = this_.Id);

edited Mar 21 '12 at 17:20

answered Mar 20 '12 at 03:26

Daniel Schilling

4,829
28
60

Your first suggestion leads to [n+1 selects](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem) and possible performance issues. The second one may yield only one DB query, but inside this you will have `WHERE EXISTS ( ... )`, which may cause the same undesired complexity (depending on the RDBMS). – Jørn Schou-Rode Mar 20 '12 at 17:12
I am hoping for a `QueryOver` expression resulting in a simple `SELECT` projecting all the fields from the already joined `ReferenceEntity` table. – Jørn Schou-Rode Mar 20 '12 at 17:15
Edited to include actual queries executed by NHibernate. Both approaches result in just a single query being executed. Can you post your test that resulted in a Select N+1? – Daniel Schilling Mar 21 '12 at 17:28
What database server are you targeting? SQL Server produces *almost* identical query plans for the two queries - it shows the EXISTS query actually using an INNER join under the covers, so it might actually be faster than the other query in some situations. – Daniel Schilling Mar 21 '12 at 17:32
You're obviously right. I didn't read your first query well enough, and missed the `Fetch().Eager` clause. Sorry about that. Thanks a lot for documenting the queries with generated SQL and for the Sql Server insights. Here's 110 rep - don't spend it all at once ;) – Jørn Schou-Rode Mar 21 '12 at 18:23
Hi Daniel! Your second query does exactly what I want. In my opnion it should be more simple like the example I did in my question, but your second query works very well. Thank you and sorry for my delay. – João Carlos Clementoni Jun 22 '12 at 23:30
I'm new to NHib and it feels weird that what looks like a simple use case is so convoluted. Looks like I'm missing something. – Boris Callens Dec 26 '13 at 13:30
I know I'm late to the party, but I want to point out an issue with the second solution. Caching will store the result of the query in the query space for `ReferenceEntity` even though it is ideally placed in the query space for `Entity`. The first solution doesn't have this issue. – Anthony Sep 18 '14 at 15:14

score 0 · Answer 2 · answered Mar 21 '11 at 17:27

0

If I understood You correctly, this is what You need:

Session.QueryOver<Entity>()
 .Where(e => e.Id == entityId)
 //!!!
 .Fetch(e=>e.ReferenceEntity).Eager
 .Select(e => e.ReferenceEntity)
 .SingleOrDefault<ReferenceEntity>()

answered Mar 21 '11 at 17:27

Arnis Lapsa

45,880
29
115
195

Hi. I did how you say, but it's not working. QueryOver is returning to me Castle.Proxies.GroupProxy. NHibernate is executing this query: SELECT this_.group_id as y0_ FROM publishes this_ WHERE this_.content_id = :p0;:p0 = 72. I wanna Group eager. With Hql i can get Entity eager. Thanks! – João Carlos Clementoni Mar 23 '11 at 23:32

score 0 · Answer 3 · answered Mar 15 '12 at 14:19

0

Try this:

Session.QueryOver<Entity>()
 .Where(e => e.Id == entityId)
 .Fetch(e=>e.ReferenceEntity).Eager
 .Select(e => e.ReferenceEntity)
 .TransformUsing(Transformers.AliasToBean<ReferenceEntity>())
 .SingleOrDefault<ReferenceEntity>()

answered Mar 15 '12 at 14:19

Anton

1,583
12
17

Doesn't seem to work for me. The generated SQL only projects the identifier of the referenced entity: `SELECT this_.ReferenceEntityId as y0_ FROM Entity this_ WHERE this_.Id = 'a67b894e-bc53-e011-bd88-22a08ed629e5'` – Jørn Schou-Rode Mar 19 '12 at 20:15

How select referenced entity in nhibernate queryover

3 Answers3

Suggestion #1

Suggestion #2