CODEWITHSUNDEEP
nhibernatequeryover
Piers Lawson
Piers Lawson

Reputation: 747

Getting started with complex projection in nHibernate

I am looking at nHibernate and Entity Framework as possible ORMs to introduce into a system. I am trying to perform the equivalent to the following SQL using nHibernate:

SELECT
   a.Id,
   a.Name,
   a.Etc,
   MAX(CASE WHEN (c.dId IS NOT NULL) THEN 1 ELSE 0 END) As IsLinked
FROM
   tA a
   INNER JOIN tAB ab ON a.Id = ab.aId
   INNER JOIN tB b ON ab.bId = b.Id
   LEFT OUTER JOIN tC c ON b.cId = c.Id
   AND c.dId = 'x'
WHERE
   a.Id = 'y'
GROUP BY
   a.Id,
   a.Name,
   a.Etc,

Basically I want to select from table tA and also check whether the entry in tA is linked or not to data in table tC. The linkage between the two tables is complex:

Using the Entity Framework Code First I can't use Include as it does not allow filtering but I can use projection so I get a functionally correct query using:

var query = from a in db.As
            where a.Id == 'y'
            select new
            {
               a,
               IsLinked = a.Bs
                           .Select(b => b.Cs)
                           .Select(c => c.Where(n => n.dId == 'x'))
                           .Select(h => h.Count()).Max() > 0
             };

The SQL generated is complex and slow, but works. I'm struggling to achieve the same functionality in nHibernate starting from a QueryOver:

var query = session.QueryOver<A>().Where(a => a.Id == 'y');

How do I create an equivalent projection that works across the multiple / nested join types (one being an OUTER join) without resorting to HQL? Or am I best off (since performance is likely to be poor) either:

Thanks

Upvotes: 3

Views: 529

Answers (1)

Firo
Firo

Reputation: 30803

this query should be equivalent, can you try it?

var query = from a in db.As
        where a.Id == 'y'
        select new
        {
           a,
           IsLinked = a.Bs.SelectMany(b => b.Cs).Any(c => c.dId == 'x')
           // or
           IsLinked = a.Bs.Any(b => b.Cs.Any(c => c.dId == 'x'))
         };

Upvotes: 1

Related Questions