使用实体框架核心生成和访问存储过程 [英] Generating and accessing stored procedures using 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 ?

推荐答案

EF Core 中没有使用 edmx 文件的数据库优先方法.相反，您必须使用 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);

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