LEFT JOIN using Linq

Asked

Viewed 2,951 times

6

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’d like to make a SELECT with INNER JOIN where it presented the following result:

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

That’s what I’m using it for Left Join in the SQL Server.

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

How can I do it using Linq?

c# sql sql-server linq
  • 1

    Does it have to be Linq exactly? Or can it be Extension methods of EF?

  • http://stackoverflow.com/questions/19356439/left-join-in-linq-to-entities I think you can help here.

  • Yes @Ciganomorrisonmendez , but what would that be Extension methods EF? I am using the same Entity Framework!

  • @Marconi does not know very well English friend... And Translate splinters everything.

  • 1

    Linq looks like an SQL. Extension Methods is that RU mode more or less like this: db.Entidade.Where(...).Select(...). Got it?

  • Yeah, great! Could be in Extension Methods same. Please consider db.Responsavel and db.Filho. I would be very grateful if you could help me @Ciganomorrisonmendez

  • @Jedaiasrodrigues ok, I think the Gypsy will help you better.

Show 2 more comments

1 answer

6


Considering Extension Methods about Entity Framework, there is not exactly the concept of LEFT JOIN. What there is is a charge of Responsavel who may or may not have Filho.

I mean, I imagine your Responsavel is modelled 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 be as follows:

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

Only this selects all Responsaveis and their Filhos. If you only want the three fields, you stay 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 note that this gets a little out of hand. You had already selected Filho in the first command, but without denormalizing the data. Using the Extension Methods, the way of thinking in selection is no longer like a join SQL, and yes as an entity and its dependent data.

Now, if you really wants to use Linq, so 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 has no optimization at all, and you don’t work with all the information of the entities, something I see as a disadvantage.

Browser other questions tagged c# sql sql-server linq

You are not signed in. Login or sign up in order to post.