Question

我正在研究尽可能采用“自由实体框架”制度。我正试图以同样方式使用“自由”来做如下工作:

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,

基本上,我想从表tA中挑选,并检查数字A的条目是否与表tC中的数据有关。两个表格之间的联系很复杂:

tA has a many to many relationship to tB (using table tAB)
tB has a one to many relationship with the table tC using the Foreign Key tB.cId
tC has a Foreign Key tc.dId to another table tD

采用实体框架法我可以不使用,因为它不允许过滤,但我可以使用预测,使我能够使用功能上正确的询问:

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

由此产生的结构复杂而缓慢,但行之有效。从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:

Making two queries (one that retrieves the data and one that establishes the linkage)
Introducing views that encapsulate the complexity of the joins

增编

Answer 1

问题应当等同,你能否尝试?

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

友情链接