CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworklinq
jthomas
jthomas

Reputation: 65

Converting complex SQL with FULL JOIN to Linq

I am trying to convert a SQL statement to Linq/Entity Framework, and am having a difficult time. Below is the 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

The FULL JOIN and the GROUP BY seem to be what I'm struggling most with. I've reviewed this SO answer and I understand how to execute a FULL JOIN on its own, but can't figure out how to integrate that into the larger overall query.

TYA for any answers.

Upvotes: 0

Views: 122

Answers (1)

Evangelos Tselepis
Evangelos Tselepis

Reputation: 86

Try this

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

Upvotes: 1

Related Questions