SQL Server 从存储过程.NET核心与实体框架返回多个返回集

to94eoyn  于 2022-11-21  发布在  .NET
关注(0)|答案(1)|浏览(123)

我想从返回3个不同表的SQL Server存储过程加载3个不同的模型,例如:

select A.id, A.Name from tableA A

 select B.id, B.Age from tableB B

 select C.Test, C.Param from tableC C

通常,我会使用Entity Framework Core处理单个结果存储过程,如下所示:
背景:

public virtual DbQuery<StoredProcedureModel> spModel{ get; set; }

 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
        modelBuilder.Query<StoredProcedureModel>(entity =>
        {
            entity.Property(e => e.Id)
                    .HasColumnName("Id");
            entity.Property(e => e.Name)
                    .HasColumnName("Name");
        }
 }

储存库:

return _context.StoredProcedureModel
        .FromSql<StoredProcedureModel>(
            "GET_ID_NAME @ID,@NAME",
            new SqlParameter("@ID", ID),
            new SqlParameter("@NAME", Name))
        .ToList();

这只是一个虚拟示例,但我想知道是否有办法将所有3个表加载到3个不同的模型中(返回的一些表具有相同的列名,如“id”)。

xiozqbni

xiozqbni1#

目前,实体框架无法实现这一点,但存在FeatureRequest。作为一种解决方法,根据此blog post,有一个使用ADO.Net的解决方案:
首先编写类代码:

public class TableA 
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class TableB
{
    public int Id { get; set; }
    public int Age { get; set; }
}

public class TableC
{
    public string Test { get; set; }
    public string Param { get; set; }
}

public class StoredProcedureResult
{
    public List<TableA> TableAEntries { get; set; }
    public List<TableB> TableBEntries { get; set; }
    public List<TableC> TableCEntries { get; set; }
}

然后,将以下函数编码到CustomDbContext类中:

public async Task<StoredProcedureResult> GetStoredProcedureResult(int id, string name)
{
    var connection = Database.GetDbConnection();
    await connection.OpenAsync();

    var command = connection.CreateCommand();
    command.CommandText = "GET_ID_NAME @ID, @NAME";
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter("@ID", id));
    command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter("@NAME", name));

    var reader = await command.ExecuteReaderAsync();
    var tableAEntries = new List<TableA>();
    var tableBEntries = new List<TableB>();
    var tableCEntries = new List<TableC>();

    while (await reader.ReadAsync())
    {
        tableAEntries.Add(new TableA
        {
            Id = reader.GetInt32("id"),
            Name = reader.GetString("name"),
        });
    }

    await reader.NextResultAsync();

    while (await reader.ReadAsync())
    {
        tableBEntries.Add(new TableB
        {
            Id = reader.GetInt32("id"),
            Age = reader.GetInt32("Age"),
        });
    }

    await reader.NextResultAsync();

    while (await reader.ReadAsync())
    {
        tableCEntries.Add(new TableC
        {
            Test = reader.GetString("Test"),
            Param = reader.GetString("Param"),
        });
    }

    var storedProcedureResult = new StoredProcedureResult();

    storedProcedureResult.TableAEntries = tableAEntries;
    storedProcedureResult.TableBEntries = tableBEntries;
    storedProcedureResult.TableCEntries = tableCEntries;

    await reader.CloseAsync();

    return storedProcedureResult;
}

相关问题