Entity Framework Core系列教程-24-使用存储过程

迈不过友情╰ 2021-07-26 21:54 565阅读 0赞

# 在Entity Framework Core中使用存储过程 #

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

1.  DbSet<TEntity>.FromSql()
2.  DbContext.Database.ExecuteSqlCommand()

在EF Core2中使用FromSql或ExecuteSqlCommand方法执行数据库存储过程存在一些限制：  
结果必须是实体类型。这意味着存储过程必须返回实体对应表的所有列。  
结果不能包含相关数据。这意味着存储过程无法执行JOIN来表示结果。  
插入，更新和删除过程无法与该实体映射，因此SaveChanges方法无法为CUD操作调用存储过程。  
在EF Core中执行存储过程之前，让我们在MS SQL Server中创建存储过程。  
如果遵循database-first 方法，则在本地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

如果您遵循code-first方法，请按照以下步骤操作：

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

Add-migration sp-GetStudents

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

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)
        {
    
        }
    }

1.  在控制台中执行以下命令，在数据库中创建以上存储过程：

Update-database

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

## 使用FromSql执行存储过程 ##

如上一章所述，DbSet的FromSql方法可用于对基础数据库执行原始SQL查询。同样，它可用于执行返回实体数据的存储过程，但有一些限制。  
在数据库中，我们可以使用如下INPUT参数值执行GetStudents存储过程：

GetStudents "Bill"
    -- or
    exec GetStudents "Bill"

您可以按照与上述相同的方式在EF Core中使用FromSql方法执行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存储过程3次，但将仅缓存和跟踪结果的一个副本。

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;");

在上面的示例中，update命令在ExecuteSqlCommand方法中传递。 rowsAffected的值将为1，因为只有1行受指定的update命令影响。  
同样，我们可以执行存储过程来创建，更新和删除命令。考虑以下存储过程，该存储过程将一条记录插入数据库的Student表中：

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

现在，您可以执行上面的存储过程：

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

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