Help with Join in lambda query

Question

I have the classes:

profissional
{
    int id;
    int idUnidade;
    string nome;
}

unidade
{
    int id;
    string nome;
}

profissionalUnidade
{
    string profissionalNome;
    string unidadeNome;
}

I’m making an appointment like this:

listaProfissionalUnidade = (from p in profissionais
join u in unidades on p.idUnidade = u.id
select new profissionalUnidade()
{
    profissionalNome = p.nome,
    unidadeNome = u.nome
}).ToList();

So he lists me all the professionals who have some connection with a unit. However, I also need to be returned to me, professionals who have no ties.

How should I do?

Answer 1

Use the left join, it would look like this.

listaProfissionalUnidade = (from p in profissionais
join u in unidades on p.idUnidade = u.id into u1
from u2 in u1.DefaultIfEmpty()
select new profissionalUnidade()
{
    profissionalNome = p.nome,
    unidadeNome = u2 == null ? "" : u2.nome
}).ToList();

That is, the Infinite play the result of your u in U1 and if it does not find a corresponding value in the other table it create a null object using the DefaultIfEmpty.

As commented below, you practically only need to repeat the excerpt below.

join u in unidades on p.idUnidade = u.id into u1
from u2 in u1.DefaultIfEmpty()

that is to say;

join u in unidades on p.idUnidade = u.id into u1
from u2 in u1.DefaultIfEmpty()
join l in unidades on u.idOutraUnidade = l.id into l1
from l2 in l1.DefaultIfEmpty()