FromSql: Support multiple resultsets

[Eilon]

From @rpundlik on March 22, 2017 12:9

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 queries

Select * LMS_Survey
Select * from LMS_SurveyQuestion
Select * from LMS_SurveyQuestionOptionChoice

and below is view model

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 ?

Right now I need to call the three separate stored procedures to get data and merging them into one view model

Thanks for the help !

Copied from original issue: aspnet/Mvc#6011

[roji]

@Nefcanto with 48 votes, this issue doesn't make it into the first page of EF's most wanted features. We're a small team and have to prioritize our work.

[Nefcanto]

@roji you might be a small team, but Microsoft is not a small company. This issue is on the second page. Are you telling me that Microsoft can't handle two pages of issues?

[roji]

@Nefcanto everyone has their feature which they think is the most important thing - unfortunately we can't always satisfy all users all the time. It's very easy to say "just do more work" or "get more engineers", but that's simply not how things work. We work through the highly-requested issues to the best of our ability, like any engineering team in the world.

[Nefcanto]

@roji, that's not true. This feature is not my most important feature. It's already on the second page of the list of priorities. So it's important to many people. It's an objective priority. I didn't mean it personally. I just wanted you to know that we are a small team is not a valid argument for a Microsoft-backed technology. However, We work through the highly-requested issues to the best of our ability is valid. Thank you so much for your great work.

[DarkAurore]

Based on what @mwgolden suggested, here is a working solution for dotnet core 6:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;

public static class DbContextExtensions
{
    public static async Task<IList<IList>> QueryStoredProcedureWithMultipleResults(
        this DbContext dbContext,
        List<Type> resultSetMappingTypes,
        string storedProcedureName,
        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);
        }

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

        if (connection.State == ConnectionState.Closed)
            await connection.OpenAsync();

        await using var reader = await command.ExecuteReaderAsync();

        int resultIndex = 0;
        do
        {
            Type type = resultSetMappingTypes[resultIndex];

            var resultSetValues = (IList) Activator.CreateInstance(typeof(List<>).MakeGenericType(type));
            var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
            while (reader.Read())
            {
                var obj = Activator.CreateInstance(type);
                if (obj == null)
                {
                    throw new Exception($"Cannot create object from type '{type}'");
                }

                foreach (var column in columns)
                {
                    var value = reader[column] == DBNull.Value ? null : reader[column];
                    obj!.GetType().GetProperty(column)?.SetValue(obj, value);
                }

                resultSetValues!.Add(obj);
            }

            resultSets.Add(resultSetValues);
            resultIndex++;
        } while (reader.NextResult());

        return resultSets;
    }

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

        var resultSets =
            await QueryStoredProcedureWithMultipleResults(dbContext, resultSetMappingTypes, storedProcedureName,
                parameters);

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

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

        var resultSets =
            await QueryStoredProcedureWithMultipleResults(dbContext, resultSetMappingTypes, storedProcedureName,
                parameters);

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

Usage:

var (result1, result2, result3) = await dbContext.QueryStoredProcedureWithMultipleResults<Model1, Model2, Model3>("StoredProcedureName");

Thanks @mwgolden and @ArnaudValensi for this solution.

Just adding a small comment, if ever you have nullable enums in your models, you will get an exception, and the following code will help to cast and set the value properly in that case :

 foreach (var column in columns)
  {
      var value = reader[column] == DBNull.Value ? null : reader[column];
      var property = obj!.GetType().GetProperty(column);
      if (property == null)
      {
          continue;
      }
      var valueType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
      if (valueType.IsEnum && value != null)
      {
          value = Enum.ToObject(valueType, value);
      }
      property.SetValue(obj, value);
  }

[Misiu]

Another variation that allows getting paginated data and total count using a single query:

internal static class DbContextExtensions
{
    public static async Task<(int count, IReadOnlyCollection<T> data)> Paginated<T>(this DbContext dbContext, string query, IEnumerable<object>? parameters, CancellationToken cancellationToken = default)
    {
        var entityProperties = dbContext.Model.FindEntityType(typeof(T))?.GetProperties().ToList();
        var connection = dbContext.Database.GetDbConnection();
        var parameterGenerator = dbContext.Database.GetService<IParameterNameGeneratorFactory>().Create();
        var commandBuilder = dbContext.Database.GetService<IRelationalCommandBuilderFactory>().Create();

        //iterate over parameters and generate names
        var @params = parameters as object[] ?? (parameters ?? Array.Empty<object>()).ToArray();

        foreach (var parameter in @params)
        {
            var name = parameterGenerator.GenerateNext();
            //check if parameter is DBParameter
            if (parameter is DbParameter dbParameter)
            {
                //add parameter to command
                commandBuilder.AddRawParameter(name, dbParameter);
            }
            else
            {
                //add parameter to command
                commandBuilder.AddParameter(name, name);
            }
        }

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

        if (connection.State == ConnectionState.Closed)
            await connection.OpenAsync(cancellationToken);

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);

        var count = 0;
        //get count
        if (await reader.ReadAsync(cancellationToken))
        {
            count = reader.GetInt32(0);
        }

        //get next result
        await reader.NextResultAsync(cancellationToken);

        //get data
        var type = typeof(T);
        var resultSetValues = (IList)Activator.CreateInstance(typeof(List<>).MakeGenericType(type));
        var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
        while (await reader.ReadAsync(cancellationToken))
        {
            var obj = Activator.CreateInstance(type);
            if (obj == null)
            {
                throw new Exception($"Cannot create object from type '{type}'");
            }

            foreach (var column in columns)
            {
                var value = reader[column] == DBNull.Value ? null : reader[column];

                var propertyName = column;

                //search for single property with data annotation column
                var entityProperty = entityProperties!.FirstOrDefault(x => x.GetColumnName() == column);
                if (entityProperty != null)
                {
                    propertyName = entityProperty.Name;
                }

                var property = obj!.GetType().GetProperty(propertyName);
                if (property == null)
                {
                    continue;
                }

                var valueType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                if (valueType.IsEnum && value != null)
                {
                    value = Enum.ToObject(valueType, value);
                }

                property.SetValue(obj, value);
            }

            resultSetValues!.Add(obj);
        }

        //return count and data as IReadOnlyCollection
        return (count, resultSetValues!.Cast<T>().ToList());
    }
}

With the above method I can query database like this:

(int count, IReadOnlyCollection<Menu> data) m2 = await _context.Paginated<Menu>("select COUNT(*) FROM dbo.menu_items; SELECT TOP 10 * FROM dbo.menu_items", null, cancellationToken);

The extensions method uses data annotation on the model, so database columns are matched to the suitable properties.

The missing part is the usage of converters declared per property and globally. Improvements are more than welcome!

[dlwennblom]

The solutions above seem like a lot of extra work to me, and limits things to 1, 2 or 3 result sets.

Instead, I first created a simplified extension method (based upon what I saw above, except without the asynchronous logic):

    public static class TranslateResultSet
    {
        public static List<T> LoadListFromDbReader<T>(this DbDataReader reader)
        {
            List<T> resultSetValues = (List<T>)Activator.CreateInstance(typeof(List<>).MakeGenericType(typeof(T)));
            List<String> columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
            while (reader.Read())
            {
                var obj = Activator.CreateInstance(typeof(T));
                if (obj == null)
                {
                    throw new Exception(@"Cannot create object from type '" + typeof(T).Name + "'");
                }
                foreach (String column in columns)
                {
                    var value = reader[column] == DBNull.Value ? null : reader[column];
                    obj!.GetType().GetProperty(column)?.SetValue(obj, value);
                }
                resultSetValues!.Add((T)obj);
            }
            return resultSetValues;
        }
    }

and then called it in a foreach loop for each result set (4 results in my case):

    public class QueryStoredProcedureWithMultipleResults
    {
        public Query_Result1 result1 { get; set; } = new Query_Result1();
        public List<Query_Result2> result2 { get; set; } = new List<Query_Result2>();
        public List<Query_Result3> result3 { get; set; } = new List<Query_Result3>();
        public List<Query_Result4> result4 { get; set; } = new List<Query_Result4>();

        public QueryStoredProcedureWithMultipleResults(DbContext dbContext,
            String parm1,
            DateTime parm2,
            String parm3)
        {
            DbCommand cmd = null;
            DbDataReader reader = null;
            try
            {
                cmd = dbContext.Database.GetDbConnection().CreateCommand();
                if (cmd.Connection.State != System.Data.ConnectionState.Open)
                {
                    cmd.Connection.Open();
                }
                cmd.CommandText = @"SprocName";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@parm1", parm1));
                cmd.Parameters.Add(new SqlParameter("@parm2", parm2));
                cmd.Parameters.Add(new SqlParameter("@parm3", parm3));
                reader = cmd.ExecuteReader();

                // first, load a list of count values from various tables in the database (Query_Result0 is the same as
                // Query_Result1, except every property is nullable, and the .Val() extension methods return a non-null
                // value for every data type after checking to see if it is null or not).

                foreach (var result in TranslateResultSet.LoadListFromDbReader<Query_Result0>(reader))
                {
                    result1.p_num_count1 = result.p_num_count1.Val();
                    result1.p_num_count2 = result.p_num_count2.Val();
                    result1.p_dollar_amount1 = result.p_dollar_amount1.Val();
                    // etc.
                    break;
                }
                reader.NextResult();
                foreach (var result in TranslateResultSet.LoadListFromDbReader<Query_Result2>(reader))
                {
                    result2.Add(result);
                }
                reader.NextResult();
                foreach (var result in TranslateResultSet.LoadListFromDbReader<Query_Result3>(reader))
                {
                    result3.Add(result);
                }
                reader.NextResult();
                foreach (var result in TranslateResultSet.LoadListFromDbReader<Query_Result4>(reader))
                {
                    result4.Add(result);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Abend in QueryStoredProcedureWithMultipleResults", ex);
            }
            finally
            {
                reader.DisposeAsync();
                cmd.Connection.Close();
                cmd.DisposeAsync();
            }
        }
    }

Usage:

var dashboard = new QueryStoredProcedureWithMultipleResults(dbContext, parm1, parm2, parm3);

It works for me in Entity Framework Core 7.0.11 and is very similar to the ((IObjectContextAdapter)dbContext).ObjectContext.Translate approach from Entity Framework 6.2 (and the .Net Framework).

[RickSandstrom]

@dlwennblom This seems like a more elegant solution. I will maybe change to this one in the future

[Misiu]

@dlwennblom thank you for your version. Indeed, it looks cleaner. My version allows me to handle complex pagination scenarios. In my case, I'm using CTE and temporary tables to filter data according to permissions, so I wanted to avoid doing the same quest two times to get the total number of records and then get paginated records. My code allows getting both results at once, but I'm sure the same can be done using your approach.

[Charles113]

obj!.GetType().GetProperty(column)?.SetValue(obj, value);

Is a very slow Solution. Also it would trigger all Property Setters, including prop changed and other stuff. EF Internally uses Cached Delegates, IndexMaps and the mysterious _shaper method. I would suggest to use IRuntimePropertyBase.GetSetter() for the IClrPropertySetter. Which is probably a lot faster.

An official Solution would be nice. Or just a method call to map a DbDataReader to a simple list of objects. We don't need a full solution. Just open the API a little bit.

The advantage of ctx.Database.SqlQuery is that it also does auto includes, tracking and other stuff.