Working with multiple resultset in .net core

Question

While retrieving the results using stored procedure how can I retrieve and store multiple result set in view model in .net core

For e.g. from stored procedure I am returning records for below two queries

Select * LMS_Survey
Select * from LMS_SurveyQuestion
Select * from LMS_SurveyQuestionOptionChoice

and below is view model for two tables

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

This is how I am executing the stored procedure

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

How can stored the multiple result set using stored procedure in view model ?

Can you paste a full code snippet that would show how you are calling sproc to retrieve data at the moment? — Ignas, Jan 19 '17 at 12:38
If your issue is mapping the result of the stored procedure to an entity/a class, see http://stackoverflow.com/questions/9929113/how-to-use-entity-framework-to-map-results-of-a-stored-procedure-to-entity-with. — Uli, Jan 19 '17 at 21:33

Ricky Gummadi · Accepted Answer · 2022-12-08T23:57:10.827

Currently, EF Core doesn't not support this. There is an open issue to address this.

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

Update 12th Sep 2018: This is still not a priority for EF Core even for release 3.0; so best use Dapper or plain ADO.NET when you have multiple results scenario

Update 25th Jun 2020: still on the backlog for EF Core even for release 5.0; so best use Dapper or plain ADO.NET when you have multiple results scenario

Update 7th Feb 2021: still on the backlog for EF Core

Update 8th Aug 2022: still on the backlog for EF Core, looks like its not a high priority use-case. Recommend to follow alternatives like using straight ADO.NET or Dapr or the below workaround for this

In the interim an alternative solution can be achieved via extension method(s)

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

And the extension typed methods

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

@mehaboob looks like its still on the backlog and not targeted for any release — Ricky Gummadi, Feb 07 '21 at 09:47

score 1 · Answer 2 · answered Feb 13 '19 at 10:53

1

Currently, EF Core doesn't not support this. see this example for retrieve multiple result sets. https://github.com/nilendrat/EfCoreMultipleResults/

answered Feb 13 '19 at 10:53

Thushara Bandara

11
1

score 1 · Answer 3 · answered Jun 19 '21 at 23:51

It works with this tiny change on EF core 5 according to Ricky G answer

change

command.CommandType = CommandType.Text;

to

command.CommandType = CommandType.StoredProcedure;

and as sql parameter value for this extension method type your stored procedure name "dbo.testproc" example of usage:

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

works for me

Working with multiple resultset in .net core

3 Answers3

Linked