Dynamic attributes of an Entity Model

Question

I have a Procedure which returns a number of columns dynamic, because this precedent uses the function Pivot, then the question arises: How to create a Entity Model for this project ?. Example:

public class SProcedure_EF6Context : DbContext
{    
    public SProcedure_EF6Context() : base("name=SProcedure_EF6Context")
    {
    }

    public DbSet<Medico> Medicos { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Criar as Stored Procedures
        modelBuilder.Entity<Medico>().MapToStoredProcedures();
    }
}

Answer 1

.MapToStoredProcedures() is one thing. Mapping a stored Procedure with dynamic return is another.

We use .MapToStoredProcedures() when we want the Entity Framework to not generate SQL, but to generate stored procedures and call them in persistence operations. This is common when the database would have known performance problems, such as too large tables and intense data volume, in which it is worth committing devices from the database itself to optimize operations.

In your case, what you want is to get a dynamic feedback from a stored Procedure using the Entity Framework. I have already said that this is not possible, unless you take the column relation of a return and generate an object at runtime. It’s not very practical and the implementation is insane.

Therefore, to solve, you will need a schema that predicts a dynamic return. ADO.NET is an option. Dapper too. I will make a suggestion in this reply and you then decide which way to go.

ADO.NET

SqlDataReader reader = null;
using (SqlCommand cmd = new SqlCommand("MinhaProcedure", contexto.Database.Connection) 
{
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    reader = cmd.ExecuteReader();
}

Dapper

IEnumerable<dynamic> resultados = db.Database.Connection.Query("MinhaProcedure", new { Param1 = 1, Param2 = "2" }, commandType: CommandType.StoredProcedure)