Converting complex SQL with FULL JOIN to Linq

Question

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.

@GordonLinoff I don't know. I didn't write this SQL (I don't know who wrote it either, I'm just doing this for a client). I am pretty new to SQL, if there is a different way to write the SQL that's equivalent and easier to convert to Linq then that would be helpful too — jthomas, Jun 10 '20 at 13:33
@ThomasKoelle, my bad. That was a typo on my part. Fixed now — jthomas, Jun 10 '20 at 13:34
EF is an ORM. It will generate the JOINs itself from the entity relations. You don't need JOINs in LINQ, in fact they're a sign of missing relations — Panagiotis Kanavos, Jun 10 '20 at 13:36
My advice would be that you make the first query on trust, and then decorate the result with a few extra queries for tla etc. — Thomas Koelle, Jun 10 '20 at 13:40

Evangelos Tselepis · Accepted Answer · 2020-06-10T14:47:17.877

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

Converting complex SQL with FULL JOIN to Linq

1 Answers1