Doubt with join between two classes

Question

Doubt with join between two classes

Navigation

#1 by (4 votes)

1

I have the Classes:

    class Procedimento
    {
        public string CodProcedimento { get; set; }
        public string NomeProcedimento { get; set; }
        public string TpSexo { get; set; }
        public int IdadeMinima { get; set; }
        public int IdadeMaxima { get; set; }
        public string CodCbo { get; set; }
    }

    class RlProcedimentoRegistro
    {
        public string CodProcedimento { get; set; }
        public string Registro { get; set; }
    }

    class ProcedimentoCompleto
    {
        public string CodProcedimento { get; set; }
        public string NomeProcedimento { get; set; }
        public string TpSexo { get; set; }
        public int IdadeMinima { get; set; }
        public int IdadeMaxima { get; set; }
        public string CodCbo { get; set; }
        public string Registro { get; set; }
    }

I make a join between the first two Classes to generate the third (including the Record property to it) and include it in a list:

listaProcedimentoCompleto = (from procedimento in listaProcedimento
                                 join rlProcedimento in listaRlProcedimentoCbo on procedimento.CodProcedimento equals rlProcedimento.CodProcedimento
                                 join rlProcedimentoRegistro in listaRlProcedimentoRegistro on procedimento.CodProcedimento equals rlProcedimentoRegistro.CodProcedimento
                                 where rlProcedimentoRegistro.Registro.Equals("01")
                                 orderby procedimento.NomeProcedimento
                                 select new ProcedimentoCompleto()
                                 {
                                     CodProcedimento = procedimento.CodProcedimento,
                                     IdadeMaxima = Convert.ToInt32(procedimento.IdadeMaxima / 12),
                                     IdadeMinima = Convert.ToInt32(procedimento.IdadeMinima / 12),
                                     NomeProcedimento = procedimento.NomeProcedimento,
                                     TpSexo = procedimento.TpSexo,
                                     CodCbo = rlProcedimento.CodCbo,
                                     Registro = rlProcedimentoRegistro.Registro
                                 }).ToList();

The idea is that: The Class Procedure has the codes of a table and the Class RlProcedureRegister checks to see if the record is equal to 01, and creates the new Class ProcedureComplete.

It works fine here. What I want is the following:

The Class RlProcedureRegistration DOES NOT CONTAIN all the CodProcedimentos that exist in the Procedure Class, because there are procedures that have no registry, so you can not make the connection.

As you can see in the code above, I can get all the Procedure that has the "01" Registry (or another one I want), but I also want to get all that has "01" Registry + those that do not contain any registry , that is, those that do not exist in the RlProcedureRegistration table.

Let's say I have the following

Procedimento.CodProcedimento = 1;
Procedimento.CodProcedimento = 2;
Procedimento.CodProcedimento = 3;
Procedimento.CodProcedimento = 4;

RlProcedimentoRegistro.CodProcedimento = 1, Registro = 01
RlProcedimentoRegistro.CodProcedimento = 3, Registro = 02
RlProcedimentoRegistro.CodProcedimento = 4, Registro = 01

I need to return Procedures 1 and 4 (which has Registration "01") + Procedure 2 (not listed in the RlProcedureRegistration table).

I tried to detail as much as possible, I hope you can understand.

c# entity-framework linq linq-to-entities

asked by anonymous 30.01.2017 / 20:02

1 answer

Access row and column specific of a C # list [closed] How to insert arrays inside angular objects?

score 4 · Accepted Answer

Failed to use DefaultIfEmpty in join. This makes a left join between the lists. See how your query is

Note: I took some properties and that join that was not part of the scope of the question because it was too confusing, in fact, is a tip, try to give better names to your classes and also organize your ideas a little better in the code.

var lista = 

    (from procedimento in listaProcedimento

    join rlProcedimentoRegistro in listaRlProcedimentoRegistro 
        on procedimento.CodProcedimento equals rlProcedimentoRegistro.CodProcedimento
        into rlProcedimentoRegistro

    from procRegistro in rlProcedimentoRegistro.DefaultIfEmpty()

    where (procRegistro == null || procRegistro.Registro == "01")
    orderby procedimento.NomeProcedimento

    select new ProcedimentoCompleto
    {
        CodProcedimento = procedimento.CodProcedimento,
        TpSexo = procedimento.TpSexo,
        Registro = procRegistro?.Registro ?? "Nulo"
    }).ToList();

Here is a complete example, ready to run. You can see it working in .NET Fiddle

using static System.Console;
using System.Linq;

public class Program
{
    public static void Main()
    {
        var listaProcedimento = new [] 
        {
            new Procedimento { CodProcedimento = "Proc 01",},
            new Procedimento { CodProcedimento = "Proc 02" },
            new Procedimento { CodProcedimento = "Proc 03" },
            new Procedimento { CodProcedimento = "Proc 04" },
        };

        var listaRlProcedimentoRegistro = new [] 
        {
            new RlProcedimentoRegistro { CodProcedimento = "Proc 01", Registro = "01" },
            new RlProcedimentoRegistro { CodProcedimento = "Proc 02", Registro = "02" },
        };

        var lista = 

        (from procedimento in listaProcedimento

        join rlProcedimentoRegistro in listaRlProcedimentoRegistro 
            on procedimento.CodProcedimento equals rlProcedimentoRegistro.CodProcedimento
            into rlProcedimentoRegistro

        from procRegistro in rlProcedimentoRegistro.DefaultIfEmpty()

        where (procRegistro == null || procRegistro.Registro == "01")
        orderby procedimento.NomeProcedimento

        select new ProcedimentoCompleto
        {
            CodProcedimento = procedimento.CodProcedimento,
            TpSexo = procedimento.TpSexo,
            Registro = procRegistro?.Registro ?? "Nulo"
        }).ToList();

        foreach(var r in lista)
        {
            WriteLine($"{r.CodProcedimento} - {r.Registro}");
        }
    }
}

public class Procedimento
{
    public string CodProcedimento { get; set; }
    public string NomeProcedimento { get; set; }
    public string TpSexo { get; set; }
    public int IdadeMinima { get; set; }
    public int IdadeMaxima { get; set; }
    public string CodCbo { get; set; }
}

public class RlProcedimentoRegistro
{
    public string CodProcedimento { get; set; }
    public string Registro { get; set; }
}

public class ProcedimentoCompleto
{
    public string CodProcedimento { get; set; }
    public string NomeProcedimento { get; set; }
    public string TpSexo { get; set; }
    public int IdadeMinima { get; set; }
    public int IdadeMaxima { get; set; }
    public string CodCbo { get; set; }
    public string Registro { get; set; }
}