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

Masquer

Answer 1

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);

Proffer

Your first suggestion leads to n+1 selects 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). – Fulani

I am hoping for a QueryOver expression resulting in a simple SELECT projecting all the fields from the already joined ReferenceEntity table. – Fulani

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? – Proffer

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. – Proffer

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 ;) – Fulani

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. – Trihedral

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. – Cautious

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. – Euhemerize

Answer 2

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>()

Halfhardy

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! – Trihedral

Answer 3

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>()

Housing

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' – Fulani