Calling Stored Procedure From Entity Framework that returns table with variable columns

Question

I am using ASP.NET MVC 3 and EF 4.x

I have a procedure that returns a result set but the columns are not fixed (it may return 25 columns or may be 40 or 50).

How can I call this stored procedure from Entity Framework?

When I use function import it asks for an Entity. But I cannot select it as none of the columns is fixed.

Try to create entity that contains all of the possible columns that can be returned and have all of the fields non required. Use that entity when you import it and it should work. — ssimeonov
– ssimeonov, Commented Feb 14, 2014 at 11:00
How this returned data will be used to create new object? Will you choose the correct entity by counting the number of columns? — Alberto
– Alberto, Commented Feb 14, 2014 at 11:25

Community · Accepted Answer · 2017-05-23 10:28:35Z

1

Entity Framework is not the right tool for this. It is good at statically defined data structures, not at dynamic ones.

There are better tools for this. I would recommend Dapper, created by Marc Gravell. It's easy as pie. Just get the NuGet package and type something like

using Dapper;

using (var cnn = new SqlConnection(myConnectionString))
{
    cnn.Open();
    var p = new DynamicParameters();
    p.Add("@params", "Id=21");
    var results = cnn.Query(sql:"GetMyData",
                            param: p, 
                            commandType: CommandType.StoredProcedure);
    foreach(IDictionary<string, object> result in results)
    {
        // Do something here.
    }
}

Query is a Dapper extension method, result is a DapperRow, which is a private class that implements IDictionary<string, object>, so you can just access your data as a dictionary per record.

Aside from being easy to use, it's lightning fast too.

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered Feb 14, 2014 at 12:39

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gert Arnold Over a year ago

Hmm, I don't understand why anyone would downvote this. Is it because I don't answer the question literally? Well, there is no way to do this with EF.

Collectives™ on Stack Overflow

Calling Stored Procedure From Entity Framework that returns table with variable columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related