c# - 在 .net 核心中使用多个结果集

Question

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

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

Select * LMS_Survey
Select * from LMS_SurveyQuestion
Select * from LMS_SurveyQuestionOptionChoice

下面是两个表的 View 模型

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;
            }
        }

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

Answer 1

目前，EF Core 不支持此功能。有一个 Unresolved 问题可以解决这个问题。

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]);
}