It crosses two tables with left join with two fields in linq

Question

I have a problem.

I have a query that contains:

left join tclientes b on b.codplaza=a.codplaza and b.codcli=a.codcli

In the "and" it crosses for second time table A with table B, and I want implements with linq:

 var ret = Context.tH
            .GroupJoin(
                    Context.t,
                    tH => tH.cod,
                    t => t.cod,
                    (x, y) => new { tH = x, t = y })
            .Where(R => R.tH.codplaza_usu == placeCode && R.tH.codusuario == codusuario && R.tH.codcli != -1)
            .SelectMany(
                xy => xy.t.DefaultIfEmpty(),
                (x, y) => new { tH = x.tH, t = y })
            .Select(s => new TClientesDMO
            {
                codplaza = s.tH.codplaza,
            }).ToList();

How can I add second condition of left join?

Answer 1

As far as I understand your question, the query and the mentioned "double crossing", a possible solution or approach could be:

var result = (from th in Context.tH
    join t in Context.t on new {CodPlaza = th.codplaza, CodCli = th.codcli} equals new { CodPlaza = t.codplaza, CodCli = t.codcli }
    where th.codplaza_usu == placeCode 
        && th.codusuario == codusuario 
        && th.codcli != -1
    select new TClientesDMO
    {
        codplaza = th.codplaza,
    }).ToList();

This query will return all codplaza from tH which join with t on the properties codplaza and codcli. Furthermore only tH objects will be considerered which have codplaza_usu == placeCode, codusuario == codusuario and codcli != -1. I hope it matches your needs.

Is it necessary to have the "empty" (DefaultIfEmpty()) entries too? Please give some more information if the query will not fit to your expected result.