How to make Join with more than two lists?

Question

I have three classes:

class Cbo
{
    public string ProfId { get; set; }
    public string Cbo { get; set; }
    public string Descricao { get; set; }
}

class Profissional
{
    public string ProfId { get; set; }
    public string NomeProf { get; set; }
}

class Vinculo
{
    public string UnidadeId { get; set; }
    public string ProfId { get; set; }
    public string Cbo { get; set; }
    public string NomeProf { get; set; }
    public string Descricao { get; set; }
}

I need to create a list using LINQ:

select * from Cbo, Profissional, Vinculo where
Cbo.ProfId = Profissional.ProfId and
Vinculo.ProfId = Profissional.ProfId

I can do it with two tables like this:

List<Vinculo> result = (from vinculo in listaVinculo join profissional in listaProfissional on vinculo.ProfId equals profissional.ProfId
select new Vinculo()
{
Cbo = vinculo.Cbo,
NomeProf = profissional.NomeProf,
ProfId = profissional.ProfId,
UnidadeId = vinculo.UnidadeId
}).ToList();

With this code I unite the classes Profissional and Vinculo, but I still don’t know how to include the class Cbo to take the field descricao.

Answer 1

Linq has the command join which allows you to join tables or lists according to an established criterion, in this case the criteria used are the Ids of your lists Cbo, Profissional and Vinculo.

I used the ProfId relationship, but other can be used.

Take this example:

var joinRes = (
                from Item1 in profs
                join Item2 in cbos on Item1.ProfId equals Item2.ProfId
                join Item3 in vins on Item2.ProfId equals Item3.ProfId
                select new
                {
                    Item1,
                    Item2,
                    Item3
                }
              ).ToList();

joinRes.ForEach(x => 
{
    Console.WriteLine(x.Item1.NomeProf);
    Console.WriteLine(x.Item2.Descricao);
    Console.WriteLine(x.Item3.Descricao);
});

Exit

Pussycat
Here is the description of the Cbo
Cat Unit

Follow the full code:

using System;
using System.Collections.Generic;
using System.Linq;

namespace JoinExemploLinq
{
    class Cbo
    {
        public string ProfId { get; set; }
        public string CboId { get; set; }
        public string Descricao { get; set; }
    }

    class Profissional
    {
        public string ProfId { get; set; }
        public string NomeProf { get; set; }
    }

    class Vinculo
    {
        public string UnidadeId { get; set; }
        public string ProfId { get; set; }
        public string CboID { get; set; }
        public string NomeProf { get; set; }
        public string Descricao { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            List<Cbo> cbos = new List<Cbo>
            {
                new Cbo
                {
                    ProfId = "1",
                    CboId = "1",
                    Descricao = "Aqui e a descricao do Cbo"
                }
            };

            List<Profissional> profs = new List<Profissional>
            {
                new Profissional
                {
                    ProfId = "1",
                    NomeProf = "Gato"
                }
            };

            List<Vinculo> vins = new List<Vinculo>
            {
                new Vinculo
                {
                    UnidadeId = "1",
                    ProfId = "1",
                    CboID = "1",
                    NomeProf = "Gato",
                    Descricao = "Unidade do gato"
                }
            };

            var joinRes = (
                            from Item1 in profs
                            join Item2 in cbos on Item1.ProfId equals Item2.ProfId
                            join Item3 in vins on Item2.ProfId equals Item3.ProfId
                            select new
                            {
                                Item1,
                                Item2,
                                Item3
                            }
                          ).ToList();

            joinRes.ForEach(x => 
            {
                Console.WriteLine(x.Item1.NomeProf);
                Console.WriteLine(x.Item2.Descricao);
                Console.WriteLine(x.Item3.Descricao);
            });

            Console.ReadKey();
        }
    }
}

It’s worth the read.

Source.