在.NET Core中处理多个结果集

Question

在使用存储过程检索结果时，如何在 .net core 中检索和存储多个结果集到视图模型中?

例如，我从存储过程中返回以下两个查询的记录：

Select * LMS_Survey
Select * from LMS_SurveyQuestion
Select * from LMS_SurveyQuestionOptionChoice

以下是两个表的视图模型：

public class LMS_SurveyTraineeViewModel
{
    public LMS_SurveyDetailsViewModel SurveyDetailsViewModel { get; set; }
    public LMS_SurveyQuestionsViewModel SurveyQuestionsViewModel { get; set; }
    public LMS_SurveyQuestionOptionChoiceViewModel SurveyQuestionOptionChoiceViewModel { get; set; }
}

这是我执行存储过程的方式

public List<LMS_SurveyTraineeViewModel> GetTraineeSurvey(int surveyID)
        {
            try
            {
                List<LMS_SurveyTraineeViewModel> modelList = new List<LMS_SurveyTraineeViewModel>();

                modelList = dbcontext.Set<LMS_SurveyTraineeViewModel>().FromSql("LMSSP_GetTraineeSurvey @surveyID = {0},@LanguageID = {1}", surveyID, AppTenant.SelectedLanguageID).ToList();

                return modelList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

如何在视图模型中使用存储过程存储多个结果集？

- XamDev

---

你能粘贴一个完整的代码片段，展示一下你目前是如何调用存储过程来检索数据的吗？ - Ignas

如果您的问题是将存储过程的结果映射到实体/类，请参考以下链接： https://dev59.com/02kw5IYBdhLWcg3wZJl9. - Uli

@Ignas 我已经更新了代码！ - XamDev

Answer 1

目前，EF Core不支持此功能。已经有一个未解决的问题。

https://github.com/aspnet/EntityFramework/issues/8127

更新于2018年9月12日：即使在3.0版本中，这仍不是EF Core的重点，因此最好在需要多个结果场景时使用Dapper或纯ADO.NET

更新于2020年6月25日：即使在5.0版本中，EF Core仍然在待办事项列表中，因此最好在需要多个结果场景时使用Dapper或纯ADO.NET

更新于2021年2月7日：EF Core仍在待办事项列表中

更新于2022年8月8日：EF Core仍在待办事项列表中，看起来这不是一个高优先级的用例。建议采用替代方案，如直接使用ADO.NET或Dapr，或使用以下的解决方法

在此期间，可以通过扩展方法实现替代解决方案

public static async Task<IList<IList>> MultiResultSetsFromSql(this DbContext dbContext, ICollection<Type> resultSetMappingTypes, string sql, params object[] parameters)
{
    var resultSets = new List<IList>();

    var connection = dbContext.Database.GetDbConnection();
    var parameterGenerator = dbContext.GetService<IParameterNameGeneratorFactory>()
                                        .Create();
    var commandBuilder = dbContext.GetService<IRelationalCommandBuilderFactory>()
                                    .Create();

    foreach (var parameter in parameters)
    {
        var generatedName = parameterGenerator.GenerateNext();
        if (parameter is DbParameter dbParameter)
            commandBuilder.AddRawParameter(generatedName, dbParameter);
        else
            commandBuilder.AddParameter(generatedName, generatedName);
    }

    using var command = connection.CreateCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = sql;
    command.Connection = connection;
    for (var i = 0; i < commandBuilder.Parameters.Count; i++)
    {
        var relationalParameter = commandBuilder.Parameters[i];
        relationalParameter.AddDbParameter(command, parameters[i]);
    }

    var materializerSource = dbContext.GetService<IEntityMaterializerSource>();
    if (connection.State == ConnectionState.Closed)
        await connection.OpenAsync();

    using var reader = await command.ExecuteReaderAsync();
    foreach (var pair in resultSetMappingTypes.Select((x, i) => (Index: i, Type: x)))
    {
        var i = pair.Index;
        var resultSetMappingType = pair.Type;
        if (i > 0 && !(await reader.NextResultAsync()))
            throw new InvalidOperationException(string.Format("No result set at index {0}, unable to map to {1}.", i, resultSetMappingType));

        var type = resultSetMappingType;
        var entityType = dbContext.GetService<IModel>()
                                    .FindEntityType(type);
        if (entityType == null)
            throw new InvalidOperationException(string.Format("Unable to find a an entity type (or query type) matching '{0}'", type));
        var relationalTypeMappingSource = dbContext.GetService<IRelationalTypeMappingSource>();
        var columns = Enumerable.Range(0, reader.FieldCount)
                                .Select(x => new
                                {
                                    Index = x,
                                    Name = reader.GetName(x)
                                })
                                .ToList();
        var relationalValueBufferFactoryFactory = dbContext.GetService<IRelationalValueBufferFactoryFactory>();
        int discriminatorIdx = -1;
        var discriminatorProperty = entityType.GetDiscriminatorProperty();
        var entityTypes = entityType.GetDerivedTypesInclusive();

        var instanceTypeMapping = entityTypes.Select(et => new
        {
            EntityType = et,
            Properties = et.GetProperties()
                            .Select(x =>
                            {
                                var column = columns.FirstOrDefault(y => string.Equals(y.Name,
                                                                                        x.GetColumnName() ?? x.Name, StringComparison.OrdinalIgnoreCase)) ?? throw new InvalidOperationException(string.Format("Unable to find a column mapping property '{0}'.", x.Name));

                                if (x == discriminatorProperty)
                                    discriminatorIdx = column.Index;
                                return new TypeMaterializationInfo(x.PropertyInfo.PropertyType, x, relationalTypeMappingSource, column.Index);
                            })
                            .ToArray()
        })
        .Select(x => new
        {
            EntityType = x.EntityType,
            Properties = x.Properties,
            ValueBufferFactory = relationalValueBufferFactoryFactory.Create(x.Properties)
        })
        .ToDictionary(e => e.EntityType.GetDiscriminatorValue() ?? e.EntityType, e => e)
        ;

        var resultSetValues = (IList)Activator.CreateInstance(typeof(List<>).MakeGenericType(type));
        while (await reader.ReadAsync())
        {
            var instanceInfo = discriminatorIdx < 0 ? instanceTypeMapping[entityType] : instanceTypeMapping[reader[discriminatorIdx]];

            var valueBuffer = instanceInfo.ValueBufferFactory.Create(reader);

            var materializationAction = materializerSource.GetMaterializer(instanceInfo.EntityType);
            resultSetValues.Add(materializationAction(new MaterializationContext(valueBuffer, dbContext)));
        }

        resultSets.Add(resultSetValues);
    }

    return resultSets;
}

扩展类型方法

public static async Task<(IReadOnlyCollection<T1> FirstResultSet, IReadOnlyCollection<T2> SecondResultSet)> MultiResultSetsFromSql<T1, T2>(this DbContext dbContext, string sql, params object[] parameters)
{
    var resultSetMappingTypes = new[]
                                {
                                        typeof(T1), typeof(T2)
                                };

    var resultSets = await MultiResultSetsFromSql(dbContext, resultSetMappingTypes, sql, parameters);

    return ((IReadOnlyCollection<T1>)resultSets[0], (IReadOnlyCollection<T2>)resultSets[1]);
}

public static async Task<(IReadOnlyCollection<T1> FirstResultSet, IReadOnlyCollection<T2> SecondResultSet, IReadOnlyCollection<T3> ThirdResultSet)> MultiResultSetsFromSql<T1, T2, T3>(this DbContext dbContext, string sql, params object[] parameters)
{
    var resultSetMappingTypes = new[]
                                {
                                        typeof(T1), typeof(T2), typeof(T3)
                                };

    var resultSets = await MultiResultSetsFromSql(dbContext, resultSetMappingTypes, sql, parameters);

    return ((IReadOnlyCollection<T1>)resultSets[0], (IReadOnlyCollection<T2>)resultSets[1], (IReadOnlyCollection<T3>)resultSets[2]);
}

- Ricky Gummadi

---

这个还在待办事项列表上吗？ - Mehaboob

@mehaboob 看起来这个问题仍在待办事项列表中，没有针对任何版本进行定位。 - Ricky Gummadi

Answer 2

目前，EF Core不支持此功能。请参阅此示例以检索多个结果集。 https://github.com/nilendrat/EfCoreMultipleResults/

- Thushara Bandara

Answer 3

根据Ricky G的回答，在EF Core 5上进行微小更改即可使其正常工作。

更改

command.CommandType = CommandType.Text;

到

command.CommandType = CommandType.StoredProcedure;

作为该扩展方法的SQL参数值，请输入您的存储过程名称“dbo.testproc”。 使用示例：

var t1 = await _context.MultiResultSetsFromSql(new [] {typeof(proctestprocResult) },"dbo.testproc", sqlParameters);

适用于我。

- ZaradosDB