How to call a stored procedure that returns dynamic columns in Entity Framework?

asp.net c# entity-framework linq sql

Question

There is a stored procedure that executes exec command and returns some columns. Columns and number of them are varied. How can I call this stored procedure from Entity Framework?

1
2
2/19/2014 6:13:36 AM

Accepted Answer

You need to create a model class that contains all stored procedure properties like below. Also because Entity Framework model class needs primary key, you can create a fake key by using Guid.

public class GetFunctionByID
{
    [Key]
    public Guid? GetFunctionByID { get; set; }

    // All the other properties.
}

then register the GetFunctionByID model class in your DbContext.

public class FunctionsContext : BaseContext<FunctionsContext>
{
    public DbSet<App_Functions> Functions { get; set; }
    public DbSet<GetFunctionByID> GetFunctionByIds {get;set;}
}

When you call your stored procedure, just see below:

var functionId = yourIdParameter;
var result =  db.Database.SqlQuery<GetFunctionByID>("GetFunctionByID @FunctionId", new SqlParameter("@FunctionId", functionId)).ToList());
42
1/7/2014 2:16:47 PM

Popular Answer

You can call a stored procedure using SqlQuery (See here)

// Prepare the query
var query = context.Functions.SqlQuery(
    "EXEC [dbo].[GetFunctionByID] @p1", 
    new SqlParameter("p1", 200));

// add NoTracking() if required

// Fetch the results
var result = query.ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow