如何用Entity Framework Core调用存储过程

江小编

7 年前

如何用Entity Framework Core调用存储过程，在这里，您将学习如何在Entity Framework Core中执行数据库存储过程。EF Core提供以下方法来执行存储过程：

DbSet<TEntity>.FromSql();
DbContext.Database.ExecuteSqlCommand();

Entity Framework Core调用存储过程

在EF Core2中使用FromSql或ExecuteSqlCommand方法执行数据库存储过程有一些限制：

结果必须是实体类型。这意味着存储过程必须返回实体的相应表的所有列。
结果不能包含相关数据。这意味着存储过程无法执行JOIN来表示结果。
Insert，Update和Delete过程无法与实体映射，因此该SaveChanges方法无法为CUD操作调用存储过程。

在我们在EF Core中执行它之前，让我们在MS SQL Server中创建我们的存储过程。

如果您遵循数据库优先方法，则在本地SQL Server数据库中执行以下脚本：

USE [SchoolDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetStudents]
            @FirstName varchar(50)
        AS
        BEGIN
            SET NOCOUNT ON;
            select * from Students where FirstName like @FirstName +'%'
        END
GO

如果您遵循代码优先方法，请按照以下步骤操作：

1.通过在NPM（NuGet包管理器）中执行以下命令添加空迁移：

PM> Add-migration sp-GetStudents

2.在以下的空迁移类的Up方法中编写以下代码<DateTime>_sp-GetStudents.cs：

public partial class spGetStudents : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sp = @"CREATE PROCEDURE [dbo].[GetStudents]
                    @FirstName varchar(50)
                AS
                BEGIN
                    SET NOCOUNT ON;
                    select * from Students where FirstName like @FirstName +'%'
                END";

        migrationBuilder.Sql(sp);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}

3.现在，通过在NPM中执行以下命令在数据库中创建上述存储过程：

PM> Update-database

这将GetStudents在SQL Server数据库中创建存储过程。

使用FromSql执行存储过程

如前一章所述，该FromSql方法DbSet可用于执行对底层数据库的原始SQL查询。以同样的方式，它可以用于执行返回实体数据的存储过程，但有一些限制。

在数据库中，我们可以GetStudents使用如下的INPUT参数值执行存储过程：

GetStudents "Bill"
-- 或
exec GetStudents "Bill"

您可以使用FromSqlEF Core中的方法以与上面相同的方式执行SP ，如下所示。

var context = new SchoolContext(); 
var students = context.Students.FromSql("GetStudents 'Bill'").ToList();

您还可以使用C＃字符串插值语法传递参数值，如下所示。

var name = "Bill";
var context = new SchoolContext(); 
var students = context.Students
                      .FromSql($"GetStudents {name}")
                      .ToList();

//or
//var students = context.Students.FromSql($"exec GetStudents {name}").ToList();

使用SqlParameter实例指定IN或OUT参数的值如下：

var context = new SchoolContext(); 
var param = new SqlParameter("@FirstName", "Bill");
//or
/*var param = new SqlParameter() {
                    ParameterName = "@FirstName",
                    SqlDbType =  System.Data.SqlDbType.VarChar,
                    Direction = System.Data.ParameterDirection.Input,
                    Size = 50,
                    Value = "Bill"
};*/

var students = context.Students.FromSql("GetStudents @FirstName", param).ToList();

您还可以指定@p0第一个参数，@p1第二个参数，依此类推。

var context = new SchoolContext(); 
var students = context.Students.FromSql("GetStudents @p0","Bill").ToList();

在上面的示例中，@p0用于第一个参数，因为EF Core中尚不支持命名参数。

注意：DbContext默认情况下，将跟踪结果中的所有实体。如果多次执行具有相同参数的相同存储过程，则每次都会执行相同的SQL语句，但它只会跟踪一个结果集。例如，以下示例将执行GetStudents存储过程三次，但它将缓存并仅跟踪结果的一个副本。

var context = new SchoolContext(); 
var list1 = context.Students.FromSql("GetStudents 'Bill'").ToList();
var list2 = context.Students.FromSql("GetStudents 'Bill'").ToList();
var list3 = context.Students.FromSql("GetStudents 'Bill'").ToList();

使用ExecuteSqlCommand（）执行存储过程

该ExecuteSqlCommand()方法用于以字符串形式执行数据库命令。它返回一个整数，表示通过指定命令影响的行数。

var context = new SchoolContext(); 
var rowsAffected = context.Database.ExecuteSqlCommand("Update Students set FirstName = 'Bill' where StudentId = 1;")

在上面的示例中，更新命令在ExecuteSqlCommand方法中传递。值为rowsAffected1，因为只有1行受指定的更新命令影响。

以同样的方式，我们可以执行create，update和delete命令的存储过程。请考虑以下存储过程，该过程Students在数据库的表中插入记录：

CREATE PROCEDURE CreateStudent
    @FirstName Varchar(50),
    @LastName Varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    Insert into Students(
           [FirstName]
           ,[LastName]
           )
 Values (@FirstName, @LastName)
END
GO

现在，您可以执行以下SP。

var context = new SchoolContext(); 
context.Database.ExecuteSqlCommand("CreateStudents @p0, @p1", parameters: new[] { "Bill", "Gates" });

以同样的方式，您可以执行Update和Delete命令的存储过程。