using stored procedure in entity framework

asp.net-mvc entity-framework repository-pattern stored-procedures

Question

I am using asp.net mvc 5 and C# with Entity Framework... I have model and domain classes for function... now I need to use stored procedure.... which I am struggling at the movement.

I am following code first existing database and I have stored procedure written there. My question is how I can call that stored procedure in my web application.

Stored procedure:

ALTER PROCEDURE [dbo].[GetFunctionByID](
    @FunctionId INT
)
AS
BEGIN
    SELECT * 
    FROM Functions As Fun
    WHERE Function_ID = @FunctionId
END

Domain class:

 public class Functions
 {
    public Functions()
    {
    }

    public int Function_ID { get; set; }
    public string Title { get; set; }
    public int Hierarchy_level { get; set; }
}

Function model:

[Table("Functions")]
public class App_Functions
{
    public App_Functions()
    {
    }

    [Key]
    public int Function_ID { get; set; }

    [StringLength(50)]
    [Required]
    public string Title { get; set; }

    public int Hierarchy_level { get; set; }
    //public virtual ICollection<App_Controllers> App_Controllers { get; set; }*/
}

BaseContext:

public class BaseContext<TContext> : DbContext where TContext : DbContext
{
    static BaseContext()
    {
        Database.SetInitializer<TContext>(null);
    }

    protected BaseContext()
        : base("name = ApplicationDbConnection")
    { }
}

Function context:

public class FunctionsContext : BaseContext<FunctionsContext>
{
    public DbSet<App_Functions> Functions { get; set; }
}
1
35
1/7/2014 11:46:15 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