我正在尝试将一个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
完全的加入和小组似乎是我最纠结的。 我已经审阅了这个SO答案，我理解了如何单独执行完整联接，但不知道如何将其集成到更大的整体查询中。
想知道答案。
试试这个
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
});
