Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Working with multiple resultset in .net core

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 ?

like image 438
XamDev Avatar asked Jan 19 '17 11:01

XamDev


People also ask

How can I get multiple result sets from a stored procedure in Entity Framework Core?

In order to get multiple result sets working we need to drop to the ObjectContext API by using the IObjectContextAdapter interface. Once we have an ObjectContext then we can use the Translate method to translate the results of our stored procedure into entities that can be tracked and used in EF as normal.

Can a stored procedure return multiple result sets?

Most stored procedures return multiple result sets. Such a stored procedure usually includes one or more select statements. The consumer needs to consider this inclusion to handle all the result sets.

What is Multiple Active result Set?

Multiple Active Result Sets (MARS) is a feature that allows the execution of multiple batches on a single connection. In previous versions, only one batch could be executed at a time against a single connection. Executing multiple batches with MARS does not imply simultaneous execution of operations.


1 Answers

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

In the interim there is an excellent solution being posted on the GitHub issue for any one looking for an alternative 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]);
}
like image 164
Ricky Gummadi Avatar answered Sep 18 '22 14:09

Ricky Gummadi