ASP.NET Core:DbSet执行原始存储过程

问题描述：

我正在使用ASP.NET Core 2.0和剃须刀页面.SQL表没有主键，因此我将无法对SQL表进行更改.我正在尝试使用存储过程从表中检索数据并在UI中显示结果数据.

I am using ASP.NET Core 2.0 and razor pages. The SQL table does not have a primary key and I will not be able to make changes to the SQL table. I am trying to use a stored procedure to retrieve the data from the table and show the resultant data in UI.

由于没有可用的主键，因此出现错误-Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateNonNullPrimaryKeys.我想按照 https://www.learnentityframeworkcore.com的定义将代码从DBSet移到Raw sql./raw-sql 下面是我现有的代码:

Since there is no primary key available, I am getting error as - Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateNonNullPrimaryKeys. I would like to move the code from DBSet to Raw sql as defined in https://www.learnentityframeworkcore.com/raw-sql Below is my existing code :

//Data - myDbContext

 public class MyDbContext : DbContext
    {
        public DbSet<LOB> lobs { get; set; }

        public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
        {
        }

        }

// Model  
 public class LOB
    {
        public string Desc { get; set; }
    }

//Index.cshtml.cs

 public class IndexModel : PageModel
    {
        private readonly MyDbContext _dbContext;
        public IndexModel(MyDbContext dbContext)
        {
            _dbContext = dbContext;
        }
        public List<LOB> lOBs { get; set; } = new List<LOB>();

                [BindProperty]
        public string[] SelectedLOBs { get; set; }

        public SelectList LOBOptions { get; set; }


        public async Task OnGetAsync()
        {
            lOBs = await _dbContext.Set<LOB>().FromSql(
                              "EXECUTE sp")
                              .AsNoTracking()
                              .ToListAsync();

            LOBOptions = new SelectList(lOBs, "Desc1");
        }
    }

// Index.cshtml

  <select class="form-control" required multiple id="selLOB" asp-for="SelectedLOBs" asp-items="Model.LOBOptions"></select>

如何使用context.database属性填充下拉列表?谢谢

How to fill the dropdown using context.database property ? Thanks

答

对于 Asp.Net Core 和 EF Core 不同. Asp.Net Core 2.0 对应于 Microsoft.AspNetCore.All 2.0 和 EF Core 2.1 对应于 Microsoft.EntityFrameworkCore 2.1 ，则可以在 Microsoft.AspNetCore.All 2.0 中引用 Microsoft.EntityFrameworkCore 2.1 .

For Asp.Net Core and EF Core are different. Asp.Net Core 2.0 is corresponding to Microsoft.AspNetCore.All 2.0 and EF Core 2.1 is correspoinding to Microsoft.EntityFrameworkCore 2.1, you could refer Microsoft.EntityFrameworkCore 2.1 in Microsoft.AspNetCore.All 2.0.

请按照以下步骤解决您的问题.

Follow steps below to resolve your issue.

将包 Microsoft.EntityFrameworkCore 更新到V2.2.3，并将 Microsoft.EntityFrameworkCore.Tools 更新到V2.2.3
将 DbSet 更改为 DbQuery

Update package Microsoft.EntityFrameworkCore to V2.2.3 and Microsoft.EntityFrameworkCore.Tools to V2.2.3
Change DbSet to DbQuery

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
    public DbQuery<LOB> lobs { get; set; }
    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        // Customize the ASP.NET Identity model and override the defaults if needed.
        // For example, you can rename the ASP.NET Identity table names and more.
        // Add your customizations after calling base.OnModelCreating(builder);
    }
}

更改您的剃刀代码.

Change your razor code.

public async Task OnGetAsync()
{
    lOBs = await _dbContext.Query<LOB>().FromSql(
                      "EXECUTE sp")
                      .AsNoTracking()
                      .ToListAsync();

    LOBOptions = new SelectList(lOBs, "Desc", "Desc", "Desc1");
}

更改视图

Change your view

<select class="form-control" required multiple id="selLOB" 
    asp-for="SelectedLOBs" asp-items="Model.LOBOptions">
</select>

ASP.NET Core:DbSet执行原始存储过程

相关推荐