asp.net-mvc 在实体框架中使用存储过程

using stored procedure in entity framework

提问by Toxic

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.

我正在使用asp.net mvc 5 和C# 与实体框架......我有模型和域类的功能......现在我需要使用存储过程......我在运动中挣扎。

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.

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

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; }
}

回答by Lin

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.

您需要创建一个包含所有存储过程属性的模型类，如下所示。也因为实体框架模型类需要主键，你可以使用 Guid 创建一个假键。

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

    // All the other properties.
}

then register the GetFunctionByIDmodel class in your DbContext.

然后GetFunctionByID在您的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());

回答by qujck

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

您可以使用SqlQuery（参见此处）调用存储过程

// 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();

回答by user2854731

After importing stored procedure, you can create object of stored procedure pass the parameter like function

导入存储过程后，可以创建存储过程的对象，传递参数，如函数

using (var entity = new FunctionsContext())
{
   var DBdata = entity.GetFunctionByID(5).ToList<Functions>();
}

or you can also use SqlQuery

或者你也可以使用 SqlQuery

using (var entity = new FunctionsContext())
{
    var Parameter = new SqlParameter {
                     ParameterName = "FunctionId",
                     Value = 5
            };

    var DBdata = entity.Database.SqlQuery<Course>("exec GetFunctionByID @FunctionId ", Parameter).ToList<Functions>();
}

回答by gsgsgs

// Add some tenants to context so we have something for the procedure to return! AddTenentsToContext(Context);

// 将一些租户添加到上下文中，这样我们就有了程序返回的内容！AddTenentsToContext(Context);

    // ACT
    // Get the results by calling the stored procedure from the context extention method 
    var results = Context.ExecuteStoredProcedure(procedure);

    // ASSERT
    Assert.AreEqual(expectedCount, results.Count);
}

回答by Jason Ebersey

Simple. Just instantiate your entity, set it to an object and pass it to your view in your controller.

简单的。只需实例化您的实体，将其设置为一个对象并将其传递给您控制器中的视图。

Entity

实体

VehicleInfoEntities db = new VehicleInfoEntities();

VehicleInfoEntities db = new VehicleInfoEntities();

Stored Procedure

存储过程

dbo.prcGetMakes()

dbo.prcGetMakes()

or

或者

you can add any parameters in your stored procedure inside the brackets ()

您可以在方括号 () 内的存储过程中添加任何参数

dbo.prcGetMakes("BMW")

dbo.prcGetMakes("宝马")

Controller

控制器

public class HomeController : Controller
{
    VehicleInfoEntities db = new VehicleInfoEntities();

    public ActionResult Index()
    {
        var makes = db.prcGetMakes(null);

        return View(makes);
    }
}

回答by Dib

Mindless passengerhas a project that allows you to call a stored proc from entity frame work like this....

Mindless 乘客有一个项目，它允许你从实体框架中调用一个存储过程，就像这样......

using (testentities te = new testentities())
{
    //-------------------------------------------------------------
    // Simple stored proc
    //-------------------------------------------------------------
    var parms1 = new testone() { inparm = "abcd" };
    var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
    var r1 = results1.ToList<TestOneResultSet>();
}

... and I am working on a stored procedure framework(here) which you can call like in one of my test methods shown below...

...我正在研究一个存储过程框架（这里），你可以像下面显示的我的测试方法之一一样调用它......

[TestClass]
public class TenantDataBasedTests : BaseIntegrationTest
{
    [TestMethod]
    public void GetTenantForName_ReturnsOneRecord()
    {
        // ARRANGE
        const int expectedCount = 1;
        const string expectedName = "Me";

        // Build the paraemeters object
        var parameters = new GetTenantForTenantNameParameters
        {
            TenantName = expectedName
        };

        // get an instance of the stored procedure passing the parameters
        var procedure = new GetTenantForTenantNameProcedure(parameters);

        // Initialise the procedure name and schema from procedure attributes
        procedure.InitializeFromAttributes();

        // Add some tenants to context so we have something for the procedure to return!
        AddTenentsToContext(Context);

        // ACT
        // Get the results by calling the stored procedure from the context extention method 
        var results = Context.ExecuteStoredProcedure(procedure);

        // ASSERT
        Assert.AreEqual(expectedCount, results.Count);
    }
}

internal class GetTenantForTenantNameParameters
{
    [Name("TenantName")]
    [Size(100)]
    [ParameterDbType(SqlDbType.VarChar)]
    public string TenantName { get; set; }
}

[Schema("app")]
[Name("Tenant_GetForTenantName")]
internal class GetTenantForTenantNameProcedure
    : StoredProcedureBase<TenantResultRow, GetTenantForTenantNameParameters>
{
    public GetTenantForTenantNameProcedure(
        GetTenantForTenantNameParameters parameters)
        : base(parameters)
    {
    }
}

If either of those two approaches are any good?

如果这两种方法中的任何一种都有好处？