Map Procedure in Entityframeworkcore

Question

I am using Entityframeworkcore 3.0 together with Stored Procedure. The Procedure sp_login returns the following data:

cod_us  A   B   C   D   E
111342  1   1   1   0   0
111342  1   2   1   1   1
111342  1   3   1   1   1

Models:

public class Login
{
    [Key]
    [Column("cod_us")]
    public int CodUs { get; private set; }

    [Column("A")]
    public int iA { get; private set; }

    [ForeignKey("B")]
    public ICollection<LoginPer> LoginPers { get; set; }
}

public class LoginPer
{
    [Key]
    [Column("B")]
    public int iB { get; private set; }

    [Column("C")]
    public bool bC { get; private set; }

    [Column("D")]
    public bool bD { get; private set; }

    [Column("E")]
    public bool bE { get; private set; }
}

I want to map to a structure that looks like this:

{
    "cod_us": 111342,
    "A": 1,
    "LoginPers": [
        {"B": 1, "C": 1, "D": 0, "E": 0},
        {"B": 2, "C": 1, "D": 1, "E": 1},
        {"B": 3, "C": 1, "D": 1, "E": 1}
    ]
}

How would you do that?

Answer 1

Use the Line to change the data after performing your query.

You can group by cod_us with the GroupBy

var respostaAgrupada = respostaProcedure.GroupBy(x => x.cod_us);

And later use the .Select to create your new object.

var respostaMapeada = respostaProcedure
    .GroupBy(x => new { x.CodUs, x.iA })
    .Select(x => new Login
    {
        CodUs = x.Key.CodUs,
        iA = x.Key.iA,
        LoginPers = x.Select(l => new LoginPer { bC = l.bC, bD = l.bD, bE = l.bE, iB = l.iB }).ToList()
    });

Full example: https://dotnetfiddle.net/OTr2i1