Show null results through LEFT JOIN with Linq!

Question

I have an 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 data not released, the SQL query returns what I need, but the query in Linq, returns exactly the opposite.

Answer 1

I will try to answer with an example:

See image below:

The table People is related to Phone, and your relationship is not mandatory, I mean, I can record Phone without People (Phone.Peopleid may contain values NULL). So I can take all the Phone independent of People I have to use LEFT JOIN.

No Linq (Linq To SQL) will look like this:

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]

Upshot:

Realize that the PhoneId 1 and 5, does not have People, then, was listed all the record of the table Phones independent of the table records People

---

Regarding your problem, try so to see what the result.

DateTime data = 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 about making one Lambda Expression, first think about SQL that the Entity Framework should generate. How did you say you want to know "Undocumented teams launched on a given date" and I looked so at your SQL I thought it would not do what you wanted. I decided to create a SQL being 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 Papers the research became more efficient in this way, and with it I transformed into Linq To SQL as already posted just above.

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

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