ASP.NET Core和EF Core 1.1-使用存储过程显示数据

问题描述

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

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

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

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代码?

谢谢！

解决方案

虽然Entity Framework Core ，您仍然可以使用FromSql来使用存储过程.

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

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

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

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

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

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

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

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

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

Note: These entities name are the same as entities on column name in sp_GetLoanDetails

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

Thanks!

解决方案

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 =>
    {
        // …
    });
}

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

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.

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-使用存储过程显示数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！