Aprenda a fazer Left Join & Inner Join via LINQ

Quando deseja-se escrever consultas LINQ em C#, logo remete-se à pergunta, “e como escrever a mesma em SQL?”

Neste artigo eu vou abordar como fazer junções internas e externas à esquerda com LINQ. Estas junções são conhecidas como inner join e left join, respectivamente.

Uma junção é uma cláusula para selecionar elementos de uma tabela que possuam correspondência em outra(s). A mais comum é conhecida como inner join. Uma condição de igualdade é definida na consulta para retornar os elementos correspondentes nas tabelas.

Obs: Por padrão, o inner join pode ser escrito também omitindo o “inner”. Elementos correspondentes significa linhas de uma tabela que combinam com linhas de outra tabela segundo uma condição.

A notação de conjuntos facilita o entendimento ao passo que concebemos as tabelas como conjuntos e o resultado da junção é a intersecção deles.

SQL INNER JOIN

Figura retirada de: W3C Schools

 

Já o left join retorna todas as colunas que possuam ou não uma correspondência com um elemento da outra tabela. Quando não houver uma correspondência, o resultado será nulo para tal.

É possível realizar a operação de junção não apenas em consultas a uma base de dados SQL, mas em outras fontes de dados, como coleções de objetos (LINQ to Objects), assim como mostro no exemplo a seguir.

Nesse estudo de caso, escolhi contextualizar uma Concessionária que possui registros de carros armazenados em seu pátio. Uma concessionária Toyota, nesse exemplo, só possui registros de carros da mesma marca, e assim por diante. Há também carros que não possuem registro em concessionária alguma.

Primeiramente, instanciamos 5 objetos Concessionária:


Concessionaria csBmw = new Concessionaria() { ConcessionariaId = 1, Localizacao = "Maceió" };
Concessionaria csBmw2 = new Concessionaria() { ConcessionariaId = 2, Localizacao = "Rio de Janeiro" };
Concessionaria csToyota = new Concessionaria() { ConcessionariaId = 3, Localizacao = "São Paulo" };
Concessionaria csFiat = new Concessionaria() { ConcessionariaId = 4, Localizacao = "Curitiba" };
Concessionaria csCitroen = new Concessionaria() { ConcessionariaId = 5, Localizacao = "Salvador" };

Depois, uma lista de objetos Veiculo:

var lstVeiculos = new List<Veiculo>()
{
     new Veiculo(){ Marca = "Fiat", Modelo = "Palio Fire", Ano = DateTime.Today.AddYears(-3), Preco = 17900.0 },
     new Veiculo(){ Marca = "BMW", Modelo = "528i M Sport", Ano = DateTime.Today.AddYears(-1), Preco = 270750.0, ConcessionariaId = 2 },
     new Veiculo(){ Marca = "BMW", Modelo = "Série 2 Coupé", Ano = DateTime.Today.AddYears(1), Preco = 190000.0, ConcessionariaId = 1 },
     new Veiculo(){ Marca = "Toyota", Modelo = "Etios", Ano = DateTime.Today, Preco = 39950.0, ConcessionariaId = 3 },
     new Veiculo(){ Marca = "Fiat", Modelo = "Siena", Ano = DateTime.Today.AddYears(-2), Preco = 29990.0 },
     new Veiculo(){ Marca = "Citroen", Modelo = "Citroen C3", Ano = DateTime.Today.AddYears(1), Preco = 41990.0 },
     new Veiculo(){ Marca = "BMW", Modelo = "Série 3 Gran Turismo", Ano = DateTime.Today.AddYears(-6), Preco = 310000.0, ConcessionariaId = 4 },
     new Veiculo(){ Marca = "Fiat", Modelo = "Mille", Ano = DateTime.Today.AddYears(-9), Preco = 17990.0, ConcessionariaId = 5 },
     new Veiculo(){ Marca = "Toyota", Modelo = "Hilux", Ano = DateTime.Today.AddYears(-11), Preco = 57900.0, ConcessionariaId = 3 },
     new Veiculo(){ Marca = "Citroen", Modelo = "C4 Picasso", Ano = DateTime.Today.AddYears(-1), Preco = 63990.0 },
     new Veiculo(){ Marca = "Toyota", Modelo = "Novo Corolla", Ano = DateTime.Today.AddYears(1), Preco = 46900.0, ConcessionariaId = 1 }
};

 

E uma lista de Concessionárias:


var lstConcessionarias = new List<Concessionaria>() { csBmw, csBmw2, csToyota, csFiat, csCitroen };

 

Agora, o inner join retorna os veículos que possuem registro na concessionária. Cada carro pode ter zero ou um registro na concessionária:

Passo a passo:

  1. Definir a fonte de dados 1: lstVeiculos (from veiculo in lstVeiculos…)
  2. A junção: cria-se uma variável para referenciar a fonte de dados 2  (veiculosConcessionaria)
  3. Definir a condição de igualdade da junção: uma boa prática é fazer pelo ID de ambas tabelas/objetos (campo ConcessionariaId)
  4. O foreach imprime o resultado.

var queryVeiculosEmConcessionarias = from veiculo in lstVeiculos
                                     join veiculosConcessionaria in lstConcessionarias
                                     on veiculo.ConcessionariaId equals veiculosConcessionaria.ConcessionariaId
                                     select veiculo;

foreach (var veiculo in queryVeiculosEmConcessionarias)
{
    Console.WriteLine(veiculo.Modelo);
}

 

Por fim, o left join retorna os veículos que possuem registro ou não na concessionária. Quando não houver registro, teremos uma referência nula. Mas, ao utilizar o método DefaultIfEmpty na consulta, previne-se o disparo de uma NullReferenceException, pois este método retorna um valor default se a coleção é vazia. Na projeção (select new…) faço o tratamento da mensagem para quando o modelo do carro não tiver um registro naquela concessionária.

Aqui eu mudei alguns nomes de variáveis.

– “concessionaria” referencia a fonte de dados (lstConcessionarias)

– “veiculosConcessionaria” referencia o resultado da junção (into veiculosConcessionaria). É a partir dessa variável que chamamos o método DefaultIfEmpty para checar se há registros nulos.


var queryVeiculosConcessionarias = from veiculo in lstVeiculos
                                   join concessionaria in lstConcessionarias
                                   on veiculo.ConcessionariaId equals concessionaria.ConcessionariaId into veiculosConcessionaria
                                   from carros in veiculosConcessionaria.DefaultIfEmpty()
                                   select new
                                   {
                                       Modelo = carros == null ? string.Concat(veiculo.Modelo, " não possui registro na concessionária")
                                         : veiculo.Modelo
                                   };

foreach (var veiculo in queryVeiculosConcessionarias)
{
    Console.WriteLine(veiculo.Modelo);
}

 

Nesse exemplo, eu defini que se houver um registro nulo resultante desse join, a mensagem exibida seria “O carro x não possui registro na concessionária”. É possível fazer essa validação no foreach, mas não bagunce o código. Com mais variáveis no select, teríamos uma miscelânea.

Poderíamos usar o operador null-coalescing (??) para simplificar ainda mais, mas não nesse caso. Este operador é incompatível nessa implementação pois o tipo anônimo “Modelo” é uma string; para usar o coalesce é preciso que ambos os lados do operador sejam do mesmo tipo. Leia mais sobre esse operador aqui.

No SQL Server, os exemplos não diferem muito sintaticamente:


--inner join

select Modelo from Veiculo
inner join Concessionaria
on Veiculo.ConcessionariaId = Concessionaria.ConcessionariaId

--left join

--retorna só os carros que não possuem registro na Concessionária

select Modelo from Veiculo
left join Concessionaria
on Veiculo.ConcessionariaId = Concessionaria.ConcessionariaId
where Veiculo.ConcessionariaId is null

--OU: retorna todos os carros que possuem ou não registro na Concessionária.

--O registro será zero para os carros que não possuem um registro

select Modelo, COALESCE(Veiculo.ConcessionariaId, '0') as 'ID da Concessionária'
from Veiculo
left join Concessionaria
on Veiculo.ConcessionariaId = Concessionaria.ConcessionariaId

 

No meu repositório eu tenho um exemplo completo de como fazer joins com LINQ to SQL. Também anexo o projeto completo aqui.

 

Ajudou? Deixe sua dúvida no comentário.

Até a próxima,

Thiago.

Anúncios

2 Respostas para “Aprenda a fazer Left Join & Inner Join via LINQ

  1. Ola,
    gostaria de saber como faço essa relação entre tabelas conforme tabelas abaixo:

    Usuarios
    iduser, nome, telefon

    Publicacoes
    idpub, iduser, data, mensagem

    amigos
    ida, iduser, idamigo

    a ideia é tipo: Usuario ter varios amigos, ter varios amigos, e o proprio usuario ter suas publicações e seus a igos também.
    Como fazer para mostrar as publicações feitas por seus amigos em sua linha de tempo. COMO NO FACEBOOK.
    Obs: eu fiz um INNE JOIN mais ele repete os registros.

    SELECT *
    FROM publicacoes
    INNER JOIN amigos ON publicacoes.iduser = amigos.iduser
    WHERE publicacoes.iduser =5

    .
    .

    • Marcus, o inner join traz o resultado da igualdade entre colunas de uma tabela com colunas de outra tabela.

      A sua query está correta, porém você deve verificar duas coisas:

      1) Como você fez o insert desses dados no banco? Os id’s que você declarou (iduser, idamigo, idpub) estão batendo? Se quiser eu dou um exemplo.

      2) Tire o filtro where da sua query. Pois, ela não vai retornar todas as publicações de seus amigos, apenas as publicações cujo iduser = 5.

      []’s

      Thiago

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s