9.9、存储过程、视图、函数操作

存储过程、视图、函数操作是关系型数据库处理复杂逻辑的强大辅助。

存储过程

在Hoa Framework v1.4.0 版本新增了 切面上下文功能，也就是下面的代码不再推荐使用，见 9.12、切面上下文（TangentDbContext）章节。

无需配置 DbSet 方式调用存储过程（推荐）

正常情况下，存储过程也需要和实体一样，需要配置 DbSet 和 无键实体类型，而且只能返回指定存储过程指定的返回实体模型，这样比较繁琐。（见 DbSet 方式）

所以，Hoa Framework 提供了更加简便的方式。支持返回任意数据类型。代码如下：

// 返回存储过程实体模型
_testRepository.SqlProcedureQuery<PROC_Entity>(DbConsts.Procedure.PROC_NAME_KEY, new (){});

// 返回任意类型
_testRepository.SqlProcedureQuery<int>(DbConsts.Procedure.PROC_NAME_KEY, new (){})

这种方式最大的好处就是可使用任何仓储对象进行执行存储过程，是极速开发推荐方式。

配置 DbSet 方式调用存储过程

在Hoa Framework v1.4.0 版本新增了 切面上下文功能，也就是下面的代码不再推荐使用，见 9.12、切面上下文（TangentDbContext）章节。

由于存储过程是无键实体类型，所以，需要手动配置 DbSet 和 创建存储过程返回值 Entity 模型。

在 Hoa Framework 框架中，需要手动配置在 Hoa.EntityFramework.Core.ManualHoaDbContext.cs 文件中，避免被 Database First 生成器每次生成后覆盖掉。代码如下：

using Microsoft.EntityFrameworkCore;
using System;
using Hoa.Core;

namespace Hoa.EntityFrameworkCore
{
    public partial class HoaDbContext
    {
        // PROC_Entity 是存储过程返回值对应的实体模型
        public virtual DbSet<PROC_Entity> PROC_Entities { get; set; }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
        {
            // 配置存储过程无键实体模型
            modelBuilder.Entity<PROC_Entity>(entity =>
            {
                entity.HasNoKey();
                // 通常我们需要将存储过程名称作为常量定义起来
                // 这里的存储过程名称配置在 Hoa.Core.DbConsts.cs 文件中
                entity.ToView(DbConsts.Procedure.PROC_NAME_KEY);
            });
        }
    }
}

通过上述配置后，即可通过 IRepository<PROC_Entity> 获得存储过程操作仓储对象，然后调用 FromSql 方式调用存储过程。如：

// DbConsts.Procedure.PROC_NAME_KEY 仓储过程名称
// 第二个参数为存储过程的参数
_procEntityRepository.FromSql(DbConsts.Procedure.PROC_NAME_KEY, new (){});

这种方式只能返回指定存储过程指定的返回实体模型。

存储过程参数说明

由于存储过程存在参数且参数名可能和参数模型不一致，所以 Hoa Framework 内置了强大存储过程参数配置特性。

默认情况下，我们可以通过 匿名对象 作为存储过程的参数，这种做法的好处就是无需创建一个 存储过程参数模型，但是缺点也显而易见，就是必须严格按照存储过程定义的参数排序进行传入。而且匿名对象属性名也必须和存储过程参数名一一对应。

所以，Hoa Framework 建议开发者使用强类型模型作为存储过程参数模型，这样无需考虑排序，又能指定存储过程参数名称。

🏴 示例一：参数模型和存储过程名字一致

_testRepository.SqlProcedureQuery<PROC_Entity>("存储过程名"
                , new ProcModel(){ Name = "Hoa", Age = 27 });

🏴 示例二：指定存储过程的实际参数，在存储过程参数模型中设置

using Hoa.DbManager.Attributes;
using System;
using System.Collections.Generic;
using System.Text;

namespace Hoa.Core.Utilities.Entities
{
    public class ProcModel
    {
        // 可以指定存储过程实际参数名
        [ProcedureParameter("ACCOUNT_NAME")]
        public string Name { get; set; }
        
        public string Age { get; set; }
    }
}

这种方式可维护性强，也利于拓展。

视图

视图是一张虚拟表，也是无键实体类型，所以，需要手动配置 DbSet 和 创建视图返回值 Entity 模型，这一点和存储过程配置无异。

配置视图DbSet

在 Hoa Framework 框架中，需要手动配置在 Hoa.EntityFramework.Core.ManualHoaDbContext.cs 文件中，避免被 Database First 生成器每次生成后覆盖掉。代码如下：

using Microsoft.EntityFrameworkCore;
using System;
using Hoa.Core;

namespace Hoa.EntityFrameworkCore
{
    public partial class HoaDbContext
    {
        // VIEW_Entity 是视图返回值对应的实体模型
        public virtual DbSet<VIEW_Entity> VIEW_Entities { get; set; }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
        {
            // 配置视图无键实体模型
            modelBuilder.Entity<PROC_Entity>(entity =>
            {
                entity.HasNoKey();
                // 通常我们需要将视图名称作为常量定义起来
                // 这里的视图配置在 Hoa.Core.DbConsts.cs 文件中
                entity.ToView(DbConsts.TableView.VIEW_NAME_KEY);
            });
        }
    }
}

通过上述配置后，即可通过 IRepository<VIEW_Entity> 获得视图仓储对象，之后就可以使用仓储中除了 增删改 以外的操作了！通常视图只用作查询和权限控制。如：

视图使用

// 查询操作
_tableViewRepository.Entity.Where(u => u.Id == 1 && u.Name.Contains("Hoa"));

_tableViewRepository.Entity
    .Where(u => u.Void == 0)
    .WhereIf(!string.IsNullOrEmpty(name),u => u.Name.Contains(name))
    .WhereIf(age > 18,u => u.Age == age)
    .Where(u => u.Field > 20);

更多视图仓储的操作可查看 9.5、查询操作 文档。

函数

在Hoa Framework v1.4.0 版本新增了 切面上下文功能，也就是下面的代码不再推荐使用，见 9.12、切面上下文（TangentDbContext）章节。

数据库中函数的调用非常简单，无需配置 DbSet 模型，也无需创建特定的 IRepository<> 仓储对象。

函数直接调用

_repository.SqlScalarFunctionQuery<返回值类型>("方法名称",new FunctionModel{});

// 还可以指定返回值列名
_repository.SqlScalarFunctionQuery<返回值类型>("方法名称",new FunctionModel{},"指定结果集列名");

注意：这种方式只能单纯的输出函数的返回值，并不能联合 sql 进行查询。

数据库内置函数使用

EF Core 为我们提供了很多常用的内置函数，可以在 Lambda 条件中使用，主要是通过 EF.Functions 调用，如：

_testRepository.Entity
    .Where(u => EF.Functions.DateDiffHour(u.CreatedDt, DateTime.Now) > 8)
    .FirstOrDefault();

这个语句使用了 EF.Functions.DateDiffHour 最终生成的 Sql 如下：

SELECT TOP(1) [a].*
FROM [dbo].[TEST] AS [a]
WHERE DATEDIFF(HOUR, [a].[CREATED_DT], GETDATE()) > 8

也就是生成了对应 DATEDIFF 函数。

更多内置的EF函数可查看官方文档。

自定义标量函数

这个有区别与上面的 函数调用，上面的 函数调用 只能用于单纯的调用函数，并不能在 Lambda 语句中使用。

而通过自定义标量函数的方式，可以实现在 Lambda 和 Linq 中调用。

假设我们数据库有一个自定义标量函数：

CREATE FUNCTION FN_GET_YEAR
(
	@DATE DATETIME
)
RETURNS VARCHAR(100)
AS
BEGIN
	RETURN YEAR(@DATE)
END

接下来

第一步：我们需要在 Hoa.Core.DbScalarFunctions.cs 中申明：

using Microsoft.EntityFrameworkCore;
using System;

namespace Hoa.Core
{
    public static class DbScalarFunctions
    {
        // Name 为 数据库标量函数中真实的名称（必填）
        // Schema 为 数据库标量函数所在的 Schema（必填）
        [DbFunction(Name = "FN_GET_YEAR", Schema = "dbo")]
        public static string GetYear(DateTime date) => throw new NotSupportedException();
    }
}

第二步：需要在 Hoa.EntityFramework.Core.ManualHoaDbContext.cs 中配置中初始化：

using Microsoft.EntityFrameworkCore;
using System;
using Hoa.Core;

namespace Hoa.EntityFrameworkCore
{
    public partial class HoaDbContext
    {
        partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
        {
            // 注意：如果你的函数有多少个参数，就填写多少个 default 
            modelBuilder.HasDbFunction(() => DbScalarFunctions.GetYear(default));
        }
    }
}

配置好上面两步后，就可以使用该函数了：

_testRepository.Entity
    .Where(u => DbScalarFunctions.GetYear(u.CreatedDt) == "2020")
    .FirstOrDefault();

最终生成的 Sql 如下：

SELECT TOP(1) [a].*
FROM [dbo].[TEST] AS [a]
WHERE [dbo].[FN_GET_YEAR]([a].[CREATED_DT]) = N'2020'

这种自定义标量函数除了可以在 Where 条件中调用，还能在 Select 结果集中使用。如：

_testRepository.Entity
    .Where(u => DbScalarFunctions.GetYear(u.CreatedDt) == "2020")
    .Select(u => new { Year = DbScalarFunctions.GetYear(u.CreatedDt) })
    .FirstOrDefault();