使用Entity Framework Core生成和访问存储过程

问题描述

我正在使用Visual Studio 2017实现Asp.Net核心Web API，实体框架核心，数据库优先方法.我设法基于现有数据库生成上下文和类文件.我需要使用上下文访问存储过程.在早期版本的实体框架中，只需在向导中选择存储过程对象并生成一个包含这些对象的edmx，就很简单.然后，我可以通过实体框架公开的复杂类型对象访问存储过程.我如何在实体框架核心中做类似的事情.一个例子会有所帮助吗?

I am implementing Asp.Net core Web API , entity framework core, database first approach using Visual Studio 2017. I have managed to generate the context and class files based on an existing database. I need to access stored procedures using my context. In earlier version of entity framework it was simple by selecting the stored procedure objects in the wizard and generating an edmx that contains those objects. I could then access stored procedures via the complex type objects exposed by entity framework. How do I do a similar thing in entity framework core. An example would help ?

推荐答案

带有edmx文件的EF Core中没有数据库优先方法.相反，您必须使用Scaffold-DbContext

Database first approach is not there in EF Core with edmx files.Instead you have to use Scaffold-DbContext

安装Nuget包Microsoft.EntityFrameworkCore.Tools和Microsoft.EntityFrameworkCore.SqlServer.Design

Install Nuget packages Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.SqlServer.Design

Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

但是不会获得您的存储过程.它仍在工作中，跟踪问题#245

but that will not get your stored procedures. It is still in the works,tracking issue #245

但是，要执行存储过程，请使用 FromSql 执行RAW SQL查询的方法

But, To execute the stored procedures, use FromSql method which executes RAW SQL queries

例如

var products= context.Products
    .FromSql("EXECUTE dbo.GetProducts")
    .ToList();

与参数一起使用

var productCategory= "Electronics";

var product = context.Products
    .FromSql("EXECUTE dbo.GetProductByCategory {0}", productCategory)
    .ToList();

或

var productCategory= new SqlParameter("productCategory", "Electronics");

var product = context.Product
    .FromSql("EXECUTE dbo.GetProductByName  @productCategory", productCategory)
    .ToList();

执行RAW SQL查询或存储过程存在某些限制.您不能将其用于INSERT/UPDATE/DELETE.如果要执行INSERT，UPDATE，DELETE查询，请使用ExecuteSqlCommand

There are certain limitations to execute RAW SQL queries or stored procedures.You can’t use it for INSERT/UPDATE/DELETE. if you want to execute INSERT, UPDATE, DELETE queries, use the ExecuteSqlCommand

var categoryName = "Electronics";
dataContext.Database
           .ExecuteSqlCommand("dbo.InsertCategory @p0", categoryName);

这篇关于使用Entity Framework Core生成和访问存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！