Entity Framework教程-存储过程、视图、函数（Stored Procedures, Views, and Functions）

更新记录
转载请注明出处：
2022年10月20日发布。
2022年10月10日从笔记迁移到博客。

存储过程（Stored Procedures）

在EFCore中定义存储过程#

直接调用sql命令定义存储过程和移除存储过程#

using(PandaDbContext db = new PandaDbContext())
{
    //创建存储过程
    db.Database.ExecuteSqlRaw("创建存储过程的T-SQL代码");
    //移除存储过程
    db.Database.ExecuteSqlRaw("移除存储过程的T-SQL代码");
}

在EF Core 的Migrations中的迁移文件中定义迁移代码#

具体的代码在Up和Down方法中定义即可

这样的好处是可以利用EF Core的迁移功能管理代码的版本

public partial class init2 : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        //创建存储过程的代码，用于创建存储过程使用
        migrationBuilder.Sql(@"创建存储过程的T-SQL代码");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        //删除存储过程的代码，用于回滚使用
        migrationBuilder.Sql("移除存储过程的T-SQL代码");
    }
}

实例：

protected override void Up(MigrationBuilder migrationBuilder)
{
    //第一个存储过程的创建代码
    var proc1 = @"
    IF OBJECT_ID('GetPersonsByState', 'P') IS NOT NULL
    DROP PROC UpdateProfilesCountry
    GO
    CREATE PROCEDURE [dbo].[GetPersonsByState]
        @State varchar(255)
    AS
    BEGIN
        SELECT p.*
        FROM Persons p
        INNER JOIN Addresses a on p.Id = a.PersonId
        WHERE a.State =  @State
    END";

    //第二个存储过程的创建代码
    var proc2 = @"
    IF OBJECT_ID('AddLookUpItem', 'P') IS NOT NULL
    DROP PROC AddLookUpItem
    GO
    CREATE PROCEDURE [dbo].[AddLookUpItem]
        @Code varchar(255),
        @Description varchar(255),
        @LookUpTypeId int
    AS
    BEGIN
        INSERT INTO LookUps (Code, Description, LookUpType) VALUES (@Code, @Description, @LookUpTypeId)
    END";

    //执行定义第1个创建存储过程的代码
    migrationBuilder.Sql(proc1);
    //执行定义第2个创建存储过程的代码
    migrationBuilder.Sql(proc2);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
    //删除存储过程
    migrationBuilder.Sql(@"DROP PROC GetPersonsByState");
    //删除存储过程
    migrationBuilder.Sql(@"DROP PROC AddLookUpItem");
}

在EF Core中使用执行存储过程#

直接使用T-SQL调用存储过程#

执行存储过程（返回指定的实体类型）（无参数形式）

var result2= dbContext.Posts.FromSqlRaw("EXEC [dbo].[GetLatestNews]");

执行存储过程（返回指定的实体类型）（有参数形式）（简单参数形式）

int id = 666;
var result = dbContext.Posts
  .FromSqlRaw("EXEC [dbo].[GetBlogs] @categoryId = {0}", id)
  .ToList();

执行存储过程（返回指定的实体类型）（有参数形式）（参数绑定形式）

int id = 666;
var result = dbContext.Posts
   .FromSqlRaw("EXEC [dbo].[GetBlogs] @categoryId = @Id", new SqlParameter("id", id))
    .ToList();

执行存储过程（返回指定的实体类型）（有参数形式）（参数绑定形式）（多参数）

int id = 666;
var result = dbContext.Posts
   .FromSqlRaw("EXEC [dbo].[GetBlogs] @categoryId = @Id", new{ id })
    .ToList();

实例：

using(PandaDbContext db = new PandaDbContext())
{
   //调用没有返回值的存储过程!!!
   int v = db.Database.ExecuteSqlRaw("调用存储过程的代码");
   int v = db.Database.ExecuteSqlRaw("调用存储过程的代码"，参数parameter[]);
   
//调用返回指定表对应Model的存储过程!!!
   var reuslt = db.Products.FromSqlRaw("T-SQL代码");
   var reuslt = db.Products.FromSqlRaw("T-SQL代码"，参数parameter[]);
}

使用Fluent API将存储过程的结果映射结果集实体#

为了能够将结果映射到结果集，需要先建立一个结果集Model来表示结果集的形

public class GetItemsForListingDto
{
    public string Name { get; set; } = "";
    public string Description { get; set; } = "";
    public string Notes { get; set; } = "";
}

然后添加到DbContext中

public DbSet<GetItemsForListingDto> ItemsForListing { get; set; }
在DbContext中的OnModelCreating方法中使用Fluent API添加描述
modelBuilder.Entity<GetItemsForListingDto>(x =>
{
    x.HasNoKey();
    x.ToView("ItemsForListing");
});

查询数据使用原始的T-SQL

var minDate = new SqlParameter("minDate", new DateTime(2020, 1, 1));
var maxDate = new SqlParameter("maxDate", new DateTime(2021, 1, 1));
var results = db.ItemsForListing.FromSqlRaw("EXECUTE dbo.GetItemsForListing @minDate, @maxDate", minDate, maxDate)