.NET Core(C#) Entity Framework Core (ef Core) 执行存储过程或函数获取返回值

1、数据表和列

示例数据表myTable包含列为Id, Date, SetId, SubsetId, PSubsetId 和 Order。Lvl是查询时用户自定义列，不是实际存在于myTable中。

2、存储过程和函数

CREATE PROCEDURE [dbo].[tree_structure] @vSet bigint, @vDate varchar(10) AS
IF ISDATE(@vDate) = 1
BEGIN
WITH Hierarchy (Id, Date, SetId, SubsetId, PSubsetId, Order, Lvl) 
AS (
    SELECT Id, Date, SetId, SubsetId, PSubsetId, Order, 0 AS Lvl
    FROM [myTable]
    WHERE Set IN (@vSet)
    AND Date = @vDate
    UNION ALL 
        SELECT t2.Id, t2.Date, t2.SetId, t2.SubsetId, t2.PSubsetId, t2.Order, Lvl + 1
        FROM [myTable] t2 
    INNER JOIN hierarchy h 
        ON t2.PSubsetId = h.SubsetId
        AND t2.Date = h.Date 
        AND t2.SetId = h.SetId
)
SELECT * FROM Hierarchy 
ORDER BY SetId, Order
END

或者

CREATE FUNCTION dbo.TreeStructure(@vSet bigint, @vDate datetime) RETURNS TABLE AS
RETURN
(
    WITH Hierarchy
    AS (
        SELECT Id, Date, SetId, SubsetId, PSubsetId, Order, 0 AS Lvl
        FROM [myTable]
        WHERE Set IN (@vSet)
        AND Date = @vDate
        UNION ALL 
            SELECT t2.Id, t2.Date, t2.SetId, t2.SubsetId, t2.PSubsetId, t2.Order, Lvl + 1
            FROM [myTable] t2 
            INNER JOIN hierarchy h 
            ON t2.PSubsetId = h.SubsetId
            AND t2.Date = h.Date 
            AND t2.SetId = h.SetId
    )
    SELECT * FROM Hierarchy
)

3、EF的Model实体

myTable表对应的Model：

public partial class MyTreeModel
{
    public DateTime Date { get; set; }
    public long SetId { get; set; }
    public long SubsetId { get; set; }
    public long? PSubsetId { get; set; }
    public int? Order { get; set; }
}

用于获取自定义列数据的视图Model：

public class MyTreeModelView : MyTreeModel
{
    public int Lvl { get; set; }
}

4、Entity Framework Core (ef Core)调用存储过程(SP)

在OnModelCreating方法中添加以下配置：

modelBuilder.Entity<MyTreeModelView>(builder =>
{
    builder.HasBaseType((Type)null); // 不认为MyTreeModelView作为数据库继承的一部分
    builder.ToView(null); // 说明它没有关联表的最简单方法
    builder.HasKey(e => e.SubsetId); //或使用HasNoKey()将其视为无主键
});

调用存储过程(SP):

public IQueryable<MyTreeModelView> TreeProcedure(long vSet, DateTime vDate)
{
    return Set<MyTreeModelView>() // <-- 这就是通过显式的' DbSet '属性访问它的方式
        .FromSqlRaw("EXEC dbo.tree_structure @vSet, @vDate",
            new SqlParameter("vSet", vSet),
            new SqlParameter("vDate", vDate.ToString("yyyy-MM-dd"))
        );
}

5、Entity Framework Core (ef Core)调用函数

在OnModelCreating方法中添加以下配置：

modelBuilder.HasDbFunction(typeof(myDbContext).GetMethod(nameof(TreeProcedure), new[] { typeof(long), typeof(DateTime) }));

调用函数：

public IQueryable<MyTreeModelView> TreeProcedure(long vSet, DateTime vDate) => FromExpression(() => TreeProcedure(vSet, vDate));