c# – Dynamic Attributes of an Entity Model

Question:

I have a procedure that returns a number of dynamic columns, because this procedure uses the Pivot function, then the question arises: How to create an Entity Model for this procedure?. 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:

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

We use .MapToStoredProcedures() when we want the Entity Framework not to generate SQL, but to generate stored procedures and call them in persistence operations. This is common when the database would have known performance issues such as tables that are too large and heavy data volume, where it is worthwhile to commit database devices to optimize operations.

In your case, what you want is to get a dynamic return from a stored procedure using the Entity Framework. I have already said beforehand 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 this, you will need a scheme that predicts a dynamic return. ADO.NET is an option. Dapper too . I will make a suggestion in this answer and you will then decide the best way forward.

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)
Scroll to Top