ASP.NET Core 和 EF Core 1.1 - 使用存储过程显示数据 [英] ASP.NET Core and EF Core 1.1 - Display data using Stored Procedure

问题描述

我的查询有问题.我想在视图上显示结果.

I have problem with my query. I want to display the result on a view.

[HttpGet]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Index()
{
    return View(await _Context.Employee
                              .FromSql("EXEC sp_GetLoanDetails")
                              .ToArrayAsync());
}

这是我想查看的项目列表:

Here is my list of items I want to view:

public class StoredProcRow
{
    [Key]
    public int empID { get; set; }
    public string empFullName { get; set; }
    public double EducationalLoan { get; set; }
    public double PettyCash { get; set; }
    public double BusinessLoan { get; set; }
    public double ApplianceLoan { get; set; }
    public double EmergencyLoan { get; set; }
    public double AllPurposeLoan { get; set; }
    public double KAPUSOIILoan { get; set; }
    public double FiestaLoan { get; set; }
    public double SalaryLoan { get; set; }
    public double Pledge { get; set; }
    public double PagIbigLoan { get; set; }
    public double SSSLoan { get; set; }
    public double AllAroundLoan { get; set; }
    public double Total { get; set; }
}

注意:这些实体名称与sp_GetLoanDetails

现在可以在 EF Core 1.1 上实现吗?还是我需要回到手动 ADO.NET 代码?

Is this achievable right now on EF Core 1.1? Or do I need to go back to manual ADO.NET code?

谢谢！

推荐答案

虽然 Entity Framework Core 并不完全支持存储过程 还没有，你仍然可以使用FromSql来使用它来消费存储过程.

While support for stored procedures isn’t completely there with Entity Framework Core yet, you still can use FromSql to consume stored procedures with it.

为了做到这一点，数据库上下文需要知道您从存储过程映射到的实体.不幸的是，现在唯一的方法是将其实际定义为数据库上下文中的实体:

In order to do that, the database context needs to know the entity you are mapping to from the stored procedure. Unfortunately, the only way to do that right now is to actually define it as an entity in the database context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<StoredProcRow>(entity =>
    {
        // …
    });
}

然后，您可以通过在该实体的集合上运行 FromSql 方法来使用存储过程:

Then, you can consume the stored procedure by running the FromSql method on a set for that entity:

public virtual IQueryable<StoredProcRow> GetLoanDetails()
{
    return Set<StoredProcRow>().FromSql("[sp_GetLoanDetails]").AsNoTracking();
}

请注意，我在这里使用了 AsNoTracking 来避免数据上下文跟踪对来自存储过程的实体的更改(因为无论如何您都无法更新它们).此外，我在方法中使用 Set<T>() 以避免必须将类型公开为数据库上下文中的成员，因为无论如何您都无法在没有存储过程的情况下使用该集合.

Note that I’m using a AsNoTracking here to avoid the data context to track changes to entities that come from the stored procedure (since you don’t have a way to update them anyway). Also I’m using Set<T>() inside the method to avoid having to expose the type as a member on the database context since you cannot use the set without the stored procedure anyway.

顺便说一句.在传递给 FromSql 的 sql 语句中，您不需要(不确定它是否有效)EXEC.只需将存储过程名称和任何参数传递给它，例如:

Btw. you don’t need (not sure if that even works) EXEC in the sql statement you pass to FromSql. Just pass the stored procedure name and any arguments to it, e.g.:

Set<MyEntity>().FromSql("[SomeStoredProcedure]");
Set<MyEntity>().FromSql("[SProcWithOneArgument] @Arg = {0}");
Set<MyEntity>().FromSql("[SProcWithTwoArguments] @Arg1 = {0}, Arg2 = {1}");

这篇关于ASP.NET Core 和 EF Core 1.1 - 使用存储过程显示数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！