LEFT JOIN using Linq

Question

LEFT JOIN using Linq

Navigation

#1 by (5 votes)

5

I have the following tables:

Responsavel             |     Filho
Id      Nome            |     Id    Nome           ResponsavelId
1       Ana             |     1     Aninha         1
2       Maria           |     2     Ana Júlia      1
3       Pedro           |     3     Mariazinha     2

I would like to make a SELECT with INNER JOIN where it would display the following result:

Id    Responsavel     Filho
1     Ana             Aninha
1     Ana             Ana Júlia
2     Maria           Mariazinha
3     Pedro           NULL

For this I am using Left Join in SQL Server .

SELECT 
    Responsavel.Id
    Responsavel.Nome,
    Filho.Nome
FROM Responsavel
LEFT JOIN Filho
    ON Responsavel.Id = Filho.ResponsavelId

How can I do this using Linq ?

sql sql-server c# linq

asked by anonymous 04.08.2015 / 18:52

1 answer

Image inside round div How to disable field in form using JQuery

score 5 · Accepted Answer

Considering Extension Methods on Entity Framework, there is not exactly the concept of LEFT JOIN . What exists is a load of Responsavel that may or may not have Filho .

That is, I imagine your Responsavel is modeled as follows:

public class Responsavel 
{
    ...
    public virtual ICollection<Filho> Filhos { get; set; }
}

public class Filho
{
    ...
    public virtual Responsavel Responsavel { get; set; }
}

In this case, the selection would look like this:

var responsaveisEFilhos = db.Responsavel
                          .Include(r => r.Filhos)
                          .ToList();

Only this selects all Responsaveis and its Filhos . If you just want the three fields, it looks like this:

var responsaveisEFilhos = db.Responsavel
                          .Include(r => r.Filhos)
                          .ToList();
var listaComoJoin = responsaveisEFilhos 
                          .SelectMany(r => r.Filhos)
                          .Select(f => new 
                          {
                              ResponsavelId = f.ResponsavelId,
                              ResponsavelNome = f.Responsavel.Nome,
                              FilhoNome = f.Nome
                          })
                          .ToList();

But notice that this is kind of out of purpose. You have already selected Filho in the first command, but without denormalizing the data. Using the Extension Methods , the way of thinking about the selection is no longer as a join of SQL, but as an entity and its dependent data.

Now, if you really want to use Linq, it looks like this:

var responsaveisEFilhos = (
    from responsaveis in db.Responsaveis
    from filhos in db.Filhos
        .Where(f => f.ResponsavelId == responsaveis.Id)
    select new {
        ResponsavelId = responsaveis.Id,
        ResponsavelNome = responsaveis.Nome,
        FilhoNome = filhos.Nome
    }
).ToList();

But this does not have any optimization, and you do not work with all entity information, which I see as a disadvantage.