在实体框架中使用存储过程

Question

我正在将asp.net mvc 5和C＃与Entity Framework一起使用...我具有函数的模型和领域类...现在我需要使用存储过程....

我在先编写现有数据库的代码，并在其中编写存储过程。我的问题是如何在Web应用程序中调用该存储过程。

存储过程：

ALTER PROCEDURE [dbo].[GetFunctionByID](

    @FunctionId INT

)

AS

BEGIN

    SELECT * 

    FROM Functions As Fun

    WHERE Function_ID = @FunctionId

END

域类：

 public class Functions

 {

    public Functions()

    {

    }

    public int Function_ID { get; set; }

    public string Title { get; set; }

    public int Hierarchy_level { get; set; }

}

功能型号：

[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")

    { }

}

函数上下文：

public class FunctionsContext : BaseContext<FunctionsContext>

{

    public DbSet<App_Functions> Functions { get; set; }

}

Answer 1

您需要创建一个包含所有存储过程属性的模型类，如下所示。另外，由于Entity Framework模型类需要主键，因此可以使用Guid创建假密钥。public class GetFunctionByID{&nbsp; &nbsp; [Key]&nbsp; &nbsp; public Guid? GetFunctionByID { get; set; }&nbsp; &nbsp; // All the other properties.}然后在中注册GetFunctionByID模型类DbContext。public class FunctionsContext : BaseContext<FunctionsContext>{&nbsp; &nbsp; public DbSet<App_Functions> Functions { get; set; }&nbsp; &nbsp; public DbSet<GetFunctionByID> GetFunctionByIds {get;set;}}调用存储过程时，请参见以下内容：var functionId = yourIdParameter;var result =&nbsp; db.Database.SqlQuery<GetFunctionByID>("GetFunctionByID @FunctionId", new SqlParameter("@FunctionId", functionId)).ToList());

Answer 2

您可以使用SqlQuery（参见此处）调用存储过程。// Prepare the queryvar query = context.Functions.SqlQuery(&nbsp; &nbsp; "EXEC [dbo].[GetFunctionByID] @p1",&nbsp;&nbsp; &nbsp; new SqlParameter("p1", 200));// add NoTracking() if required// Fetch the resultsvar result = query.ToList();

Answer 3

导入存储过程后，可以通过函数之类的参数创建存储过程的对象using (var entity = new FunctionsContext()){&nbsp; &nbsp;var DBdata = entity.GetFunctionByID(5).ToList<Functions>();}或者你也可以使用 SqlQueryusing (var entity = new FunctionsContext()){&nbsp; &nbsp; var Parameter = new SqlParameter {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ParameterName = "FunctionId",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Value = 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; };&nbsp; &nbsp; var DBdata = entity.Database.SqlQuery<Course>("exec GetFunctionByID @FunctionId ", Parameter).ToList<Functions>();}