将带完全联接的复杂sql转换为linq

piwo6bdm  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(397)

我正在尝试将sql语句转换为linq/entity框架,但遇到了困难。下面是sql。

SELECT
trust.FName,
trust.MName,
trust.LName
tla.Address,
tma.Address,
at.Descr
FROM CLIENTSSUITS cs
INNER JOIN TRUSTS trust ON trust.SSN = cs.CLIENT
FULL JOIN ADV_TYPES at ON at.CODE = trust.AT
LEFT OUTER JOIN CLIENTADDRESSES tla ON tla.SSN = trust.SSN AND tla.ID = 'L'
LEFT OUTER JOIN CLIENTADDRESSES tma ON tma.SSN = trust.SSN AND tma.ID = 'M'
WHERE cs.PRIMARY = w AND SecondaryRole = x AND cs.ID = y AND cs.Rev = z AND cs.DELETED = 0
GROUP BY trust.FName,
trust.MName,
trust.LName,
tla.Address,
tma.Address,
at.Descr

我最纠结的似乎是全员加入和小组讨论。我已经回顾了这个答案,我知道如何单独执行一个完整的连接,但是不知道如何将它集成到更大的整体查询中。
谢谢你的回答。

7xzttuei

7xzttuei1#

试试这个

using(var ctx = new Dbcontext()) 
{
    var list = (
        from cs in ctx.CLIENTSSUITS
        join trust in ctx.TRUSTS on cs.CLIENT equals trust.CLIENT
        join at in ctx.ADV_TYPES on at.CODE equals trust.AT into temp from temp.DefaultIfEmpty()
        join tla1 in ctx.CLIENTADDRESSES on tla.SSN equals trust.SSN && tla.ID = 'L' into temp2 from subtla1 in temp2.DefaultIfEmpty()
        join tla2 in ctx.CLIENTADDRESSES on tla2.SSN equals trust.SSN && tla2.ID = 'M' into temp3 from subtla2 in temp3.DefaultIfEmpty()             
        where (cs.PRIMARY = w && ?.SecondaryRole = x && cs.ID = y && cs.Rev = z && cs.DELETED = 0)
        select new
        {
        FName = trust.FName,
        MName = trust.MName,
        LName = trust.LName
        LAddress = tla.Address,
        MAddress = tma.Address,
        Descr = at.Descr
        }).ToList();
}

    //if the list contains the right result then you can easily group it with this code

    var results = list.GroupBy(x => new {
     x.FName, x.MName, x.LName, x.LAddress, x.MAddress, Descr
    });

相关问题