CODEWITHSUNDEEP
c#sqllinq
iMSn20
iMSn20

Reputation: 255

Multiple Left Join and Inner Join with LinQ

I have this SQL code who works:

SELECT 
    FF.numero, 
    FF.cuo_pla, 
    FF.num_pla,
    PN.Descripcion,
    TN.Nombre
FROM encf EF
JOIN forf FF
    ON EF.numero = FF.numero 
    AND EF.[manual] = FF.[manual]
    AND EF.comproba = FF.comproba
    AND EF.tipo_fac = FF.tipo_fac
    AND EF.sucursal = FF.sucursal
LEFT JOIN Planes PN
    ON FF.num_pla = PN.Numero
LEFT JOIN Tarjetas TN
    ON PN.IdTarjeta = TN.ID
WHERE 
    EF.documento = 123456789

There are two LEFT JOIN and one INNER JOIN. I know how to do the inner join but don't know how to make that two left join who need an id obtained in the first inner join. I'm making something like this:

var queryEncFor = from enc in db.encf
                              join forF in db.forf on
                                new
                                {
                                    JoinProperty1 = enc.numero,
                                    JoinProperty2 = enc.manual,
                                    JoinProperty3 = enc.comproba,
                                    JoinProperty4 = enc.tipo_fac,
                                    JoinProperty5 = enc.sucursal
                                }
                                equals
                                new
                                {
                                    JoinProperty1 = forFact.numero,
                                    JoinProperty2 = forFact.manual,
                                    JoinProperty3 = forFact.comproba,
                                    JoinProperty4 = forFact.tipo_fac,
                                    JoinProperty5 = forFact.sucursal
                                }
                              where enc.documento == doc
                              select new UnionEncForFact
                              {
                                  numero = enc.numero,
                                  cuo_pla = forF.cuo_pla == null ? 0 : forF.cuo_pla.Value,
                                  num_pla = forF.num_pla
                              };

The inner join its working but I don't know how to translate the two left join, any suggestion?

Upvotes: 0

Views: 1666

Answers (1)

Mohammed Sajid
Mohammed Sajid

Reputation: 4903

According to Microsoft documentation, the query will be :

var queryEncFor = (from enc in db.encf
                   join forF in db.forf on
                   new
                   {
                       enc.numero,
                       enc.manual,
                       enc.comproba,
                       enc.tipo_fac,
                       enc.sucursal
                   }
                   equals
                   new
                   {
                       forF.numero,
                       forF.manual,
                       forF.comproba,
                       forF.tipo_fac,
                       forF.sucursal
                   }
                   join plane in db.Planes on forF.num_pla equals plane.Numero into leftedPlanes
                   from leftedPlane in leftedPlanes.DefaultIfEmpty()

                   join tarjeta in db.Tarjetas on leftedPlane?.IdTarjeta equals tarjeta.ID into leftedTarjetas
                   from leftedTarjeta in leftedTarjetas.DefaultIfEmpty()

                   where enc.documento == 1
                   select new UnionEncForFact
                   {
                       numero = enc.numero,
                       cuo_pla = forF.cuo_pla == null ? 0 : forF.cuo_pla.Value,
                       num_pla = forF.num_pla,
                       Descripcion = leftedPlane?.Descripcion,
                       Nombre = leftedTarjeta?.Nombre ?? 0
                   }).ToList();

I hope you find this helpful.

Upvotes: 1

Related Questions