Show null results through LEFT JOIN with Linq!

Question

Show null results through LEFT JOIN with Linq!

Navigation

#1 by (1 votes)

2

I have a SQL query:

SELECT A.Codplano, A.Secao, A.Setor,A.Subsetor,A.Contato, ISNULL(B.Subord,'NÃO
LANÇADA')AS Situacao 
FROM vwPLANODIN A LEFT JOIN LANCADA B
ON A.Codplano = B.Subord
and B.Data = '2014-06-10'
WHERE B.ID IS NULL and A.Sitio = 7341

that translated into Linq:

public static IEnumerable CobrancaCF(string data, int local)
    {
        WebDataDataContext dataClass = new WebDataDataContext();

        var dia = data;
        var sitio = local;

    var cob = from A in dataClass.vwPLANODINs
                  join B in dataClass.LANCADAs on new { A.Codplano, Data = data }
                  equals new { Codplano = B.Subord, Data = Convert.ToString(B.Data) }  into B_join
                  from B in B_join.DefaultIfEmpty()
                  where
                       B.Data == null &&
                       A.Sitio == local
                  select new
                  {
                      A.Codplano,
                      A.Secao,
                      A.Setor,
                      A.Subsetor,
                      A.Contato,
                      Situacao = (B.Subord ?? "NÃO LANÇADA")
                  };    

IEnumerable cobranca = cob.AsEnumerable();

return cobranca;

}

I have to show in a Gridview the unpublished data, the SQL query returns what I need, but the query in Linq returns exactly the opposite.

sql c# linq visual-studio

asked by anonymous 14.06.2014 / 17:21

1 answer

Amazon SES, send unchecked emails Manage iframe activity

score 1 · Accepted Answer

I'll try to respond with an example:

See image below:

ThePeopletableisrelatedtoPhone,anditsrelationisnotmandatory,ieIcanwritePhonewithoutPeople(Phone.PeopleIdcancontainNULLvalues).FormetogetallPhoneindependentofPeopleIhavetouseLEFTJOIN.

Linq( Linq To SQL ) looks like this: / em>

using (cboEntities db = new cboEntities())
{
    var result = db.Phone
            .GroupJoin(db.People, ph => ph.PeopleId, pe => pe.PeopleId, (ph, pe) => new { ph, pe })
            .SelectMany(s => s.pe.DefaultIfEmpty(), (ph, pe) => new
            {
               ph.ph.PhoneId,
               ph.ph.Ddd, 
               ph.ph.Number, 
               ph.ph.PeopleId, 
               ph.ph.People.FirstName                           
            });

    var resultToList = result.ToList();
}

or

using (cboEntities db = new cboEntities())
{   
    var result = (from ph in db.Phone
               join pe in db.People on ph.PeopleId equals pe.PeopleId into peph
               from pe_ph in peph.DefaultIfEmpty()
                  select new
                  {
                      ph.PhoneId,
                      ph.Ddd, 
                      ph.Number, 
                      ph.PeopleId, 
                      ph.People.FirstName
                  });

    var resultToList = result.ToList();
}

SQL Generated:

SELECT 
    [Extent1].[PhoneId] AS [PhoneId], 
    [Extent1].[Ddd] AS [Ddd], 
    [Extent1].[Number] AS [Number], 
    [Extent1].[PeopleId] AS [PeopleId], 
    [Extent2].[FirstName] AS [FirstName]
    FROM  [dbo].[Phone] AS [Extent1]
    LEFT OUTER JOIN [dbo].[People] AS [Extent2] ON [Extent1].[PeopleId] = [Extent2].[PeopleId]

Result:

Notethat%withand5,doesnothavePhoneId,thenallrecordsinthe%People

Foryourproblem,trytoseetheresult.

DateTimedata=DateTime.Parse("10/06/2014");
String local = null;

var result = dataClass.vwPLANODINs
    .Where(x => 
        !dataClass.LANCADA.Where(la => la.Data == data).Select(g => g.Subord).Contains(x.Codplano) &&
        x.Sitio == local
        )
    .Select(s => new
    {
        s.Codplano,
        s.Secao,
        s.Setor,
        s.SubSetor,
        s.Contato,
        Situacao = "NÃO LANÇADA"
    });

var resultToList = result.ToList();

Explanation: When I think of doing a Lambda Expression , first thought about SQL that the Entity Framework should generate. As you said you want to know "Teams that did not have document released on a certain date" and looked so much at their SQL I thought it was not going to do what you wanted. I then decided to create a SQL like this:

SELECT 
    [Extent1].[Codplano] AS [Codplano], 
    [Extent1].[Secao] AS [Secao], 
    [Extent1].[Setor] AS [Setor], 
    [Extent1].[SubSetor] AS [SubSetor], 
    [Extent1].[Contato] AS [Contato]    
    FROM [dbo].[vwPLANODINs] AS [Extent1]
    WHERE ( NOT EXISTS (SELECT 1 AS [C1] FROM [dbo].[LANCADA] AS [Extent2]
        WHERE ([Extent2].[Data] = "2014-06-10") AND ([Extent2].[Subord] = [Extent1].[Codplano]))
        ) AND [Extent1].[Sitio] = 7341)

Using NOT EXITS and with the Search fronts it became more efficient in that way, and with that I became Linq To SQL as already posted just above.

Tip: Always think about SQL to then think about Entity Framework .

Obs: I have an example here of how to test the SQL generated by the Entity Framework.