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

[Eilon]

I think this question is more about retrieving multiple result sets from a sproc, and less so about MVC view models.

[Ricky-G]

@Eilon it looks there was an issue raised in the past #6026 but it was closed for some reason. Is there any current plan to include this feature in the 2.0 milestone.

[Eilon]

@RickyG-Akl , the friendly folks in this repo will give you an answer 😄

[ajcvickers]

@rpundlik @RickyG-Akl There isn't currently any support for this when using EF and FromSql. The workaround is to drop down to ADO.NET and use the DbConnection directly to run the query and handle the multiple resultsets. Putting this issue on the backlog so that we can consider adding support in the future.

[ajayvikas]

@ajcvickers In EF6 we have ObjectContext.Translate api, which we could use in this kind of scenario. There it was possible to materialize SqlDataReader into the EF entity. However this is missing in EF Core 2.0. The issue has been raised in #4675. We use this feature extensively and without this we won't be able to migrate from EF6 to EF Core.

[divega]

@ppN2, I am curious about how you are using Translate(). Can you confirm if you need to have readers with multiple results?

[ajayvikas]

Generally when the entities are simple for example order, orderdetails etc kind, we don't need to use Translate. The include() method works just fine. There are many cases where we need to return a complex object graph from the stored procedure. Generally this stored procedure will have complex business rules and will return multiple resultsets (in our case about 15-20 resultsets). In these cases we run the ExecuteReader command and load SqlDataReader and using translate apis to dematerialize the entities one by one.

[ajayvikas]

@ajcvickers, @divega By looking at past responses and the source code, we came up with the following procedure which is equivalent to the EF6 ObjectContext.Translate (We could extend it to include the merge options, similar to EF6 Translate provides). We have tested this code and seems to be working. We do not have deep knowledge of he source code, so our confidence level is not very high. Is it possible for somebody with deeper knowledge to review this code? This will really help us to move forward with the migration to EF Core 2.0.

        public static List<T> Translate<T>(this DbSet<T> set, DbDataReader reader) where T : class
        {
            var entityList = new List<T>();
            if (reader == null || reader.HasRows == false) return entityList;
            var entityType = set.GetService<IModel>().FindEntityType(typeof(T));
            var valueBufferParameter = Expression.Parameter(typeof(ValueBuffer));
            var entityMaterializerSource = set.GetService<IEntityMaterializerSource>();
            var valueBufferFactory = set.GetService<IRelationalValueBufferFactoryFactory>().Create(new[] { typeof(T) }, null);
            var stateManager = set.GetService<IStateManager>() as StateManager;
            Func<ValueBuffer, T> materializer = Expression.Lambda<Func<ValueBuffer, T>>(
                    entityMaterializerSource.CreateMaterializeExpression(entityType, valueBufferParameter), valueBufferParameter)
                .Compile();
            stateManager.BeginTrackingQuery();
            while (reader.Read())
            {
                ValueBuffer valueBuffer = valueBufferFactory.Create(reader);
                var entity = materializer.Invoke(valueBuffer);
                var entry = stateManager.StartTrackingFromQuery(entityType, entity, valueBuffer, null);
                entityList.Add((T)entry.Entity);
            }
            return entityList;
        }

The way to use this code will be

            using (var ctx = new ApplicationDbContext())
            {
                using (var cnn = ctx.Database.GetDbConnection())
                {
                    var cmm = cnn.CreateCommand();
                    cmm.CommandType = System.Data.CommandType.Text;
                    cmm.CommandText = "SELECT AccountId, AccountBalance, AccountName FROM Accounts; SELECT CustomerId, AccountId, CustomerName FROM Customers";
                    cmm.Connection = cnn;
                    cnn.Open();
                    using (var reader = cmm.ExecuteReader())
                    {
                        var accounts = ctx.Accounts.Translate(reader);
                        reader.NextResult();
                        var customers = ctx.Customers.Translate(reader);
                    }
                }
            }

[ErikEJ]

@sjh37 FYI!

[sjh37]

Multiple result sets from a sproc are supported via the Entity Framework Reverse Poco Generator. This generates code for EF6, which you could copy and put into your EF.Core project.
Download at https://marketplace.visualstudio.com/items?itemName=SimonHughes.EntityFrameworkReversePOCOGenerator

[ErikEJ]

@sjh37 Notice that ObjectContext is not present in EF Core

[ajcvickers]

@ppN2 We looked through your code in triage and we think it should be okay for simple cases. Some things to keep in mind:

• The code makes use of "internal" APIs, which means that it could be broken any time a new EF version is released. Use internal surface at your own risk
• Some places it may not work include
  • Places where the column order may not be what is expected; for example, FromSql has special code to handle column ordering
  • Inheritance between entity types and TPH mapping
  • There is no identity resolution, so if an entity with a given ID is already tracked, then this looks like it will fail. (You might look into QueryBuffer for this.)

Overall, if you are in full control of the types, the queries, and the state of the context, then this should work, but as a general solution it will fall down in some places.

[srini1978]

@sjh37 . On your comment that we can use the multiple resultsets from stored proceudure in an EF core project, did you mean that as part of that hack :

1. We can add the EF6 nuget to the EF core project and generate the context
2. USe the generated context from reverse poco instead of the Context from Core project?

[sjh37]

@srini1978 I thought it would of been a simple task to generate the code using the generator, then copy n paste the stored proc and result set class to your ef core db context. However I didn't realise at the time that ObjectContext is not present in EF Core.

[anildbest83]

Is there any update on this? can I use EF Core and FromSql to get multiple result set using stored procedure in model?

[ajcvickers]

@anildbest83 This issue is in the Backlog milestone. We haven't done any work on it yet and that it is currently not planned for for the 2.2 or 3.0 release. We will re-assess the backlog following the 3.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[spudcud]

This is a big deal for us because we have very complex load SPs that do a lot of calculating/coalescing to figure out what records need to be included. This work can only be done once and we can return from 10-30 tables in one load SP. I attempted the code written by @ppN2 above but it did not work in the latest EF version (not overly surprised considering the Internals warning). I attempted to take a different approach to load and I was able to get it to work by simply taking data (ADO.NET) from the database and manually creating records and then attaching them to the context. This worked fine and would potentially work for us since we code gen a lot so we would know what tables are where with what columns so code gening this load logic wouldn't be terrible even though it is not ideal as EF should have this capability built in. I have no idea what the performance cost is for this as it wires up all relationship on attach. I do batch AttachRange hoping that would be better but I don't know. Once the context was loaded all entity access would then be done using the Local cache only.

Obviously this is not a generic solution that will work for any entity type automatically but at least on the surface seems like a possibility. Wanted to share to hopfully up priority on this issue as well as get any feedback on this approach.

[anildbest83]

Somehow I managed to tackle my situation using the code written by @ppN2, thanks

[spudcud]

@anildbest83 What did you do to get it to work? Can you post a snippet?

[anildbest83]

Something like this

Used SqlHerper.cs class (attached)

SqlHerpler.txt

and cosnume it like this

           `_context.LoadStoredProc("get_user_by_lastupdateddate]")
                .WithSqlParam("@lastupdatedfrom", lastUpdatedFrom)
                .WithSqlParam("@startwithuserid", startWithUserId)
                .ExecuteStoredProc((handler) =>
               {
                      usersSql.AddRange( handler.ReadToList<Users>());
                      handler.NextResult();

                      usersSql.AddRange(handler.ReadToList<Users2>());
                      handler.NextResult();

                      usersSql.AddRange( handler.ReadToList<Users3>());
                      handler.NextResult();
                });`

[mscappini]

I was wondering if anyone had managed to wrangle a decent workaround for this in EF Core 2.1. I've been attempting to dig into the internals of EF Core to figure out how to materialize and track an entity using a DbDataReader, but I get lost pretty quickly with what's going on.

As @spudcud pointed out, it looks like the workaround provided by @ppN2 no longer works for EF Core 2.1. I also attempted to hook up a workaround from #4675 by @jnm2, but see an identical problem there, too.

The crux of it is that I receive an exception System.InvalidOperationException: 'No mapping to a relational type can be found for the CLR type 'MyType'.' when attempting to call Create(new[] { typeof(TEntity) }, null) on the resolved IRelationalValueBufferFactoryFactory service resolved from my DbSet<TEntity>. I assume there used to be mappings for the DbContext types in that service (or its dependencies), but that may no longer be the case.

I guess two real questions holding me up are:

1. Is there a way to resolve a service or create a type IRelationalValueBufferFactory from CLR type to be used for the Create function on the IRelationalValueBufferFactoryFactory implementation?
2. Once that IRelationalValueBufferFactory is created/resolved, how can I avoid using the obsolete IRelationalValueBufferFactory.Create(valueTypes, indexMap) function in favor of the IRelationalValueBufferFactory.Create(types) function?

We have a rather large solution that I am working to convert, and this is one of the last hurdles preventing me from completely migrating to EF Core. Any guidance is greatly appreciated. Thanks!

[WellspringCS]

Crickets chirping...

Same here, @mscappini. It never occurred to me that a mature product like EF Core 2.x would not have any means by which to return a stored procedure returned recordset back to the caller. Moving on to ADO.NET, I guess, per @ajcvickers's guidance.

BTW, why is this issue titled "Support multiple resultsets"? Best I can tell, EF doesn't even really support single resultsets. (I am disregarding solutions such as this that look painful and awkward.)

I very much look forward to the version of EF that not only supports stored procedures, but can import them into projects automatically, as edmx did in the past. Until then, I guess I either live with ancient versions of EF or use some other tool, because I use stored procedures extensively. Am I that unusual?

[ajcvickers]

@WellspringCS This issue is titled "Support multiple resultsets" because that's what it's about. Returning single result sets is supported for query types and entity types..

[vaclav-antosik]

In EFCore 5 the
[Obsolete] public TypeMaterializationInfo( [NotNull] Type modelClrType, [CanBeNull] IProperty property, [CanBeNull] IRelationalTypeMappingSource typeMappingSource, int index = -1) : this(modelClrType, property, typeMappingSource, null, index) { }
has been switched to obsolete.
New constructor does not include the index parameter. So there is no way how to map order or reader columns to object columns. In case that order does not match, it's game over (for workarounds mentioned above).

I hope that this missing feature will be implemented before the index parameter finally disappears from the API.

[mwgolden]

Here is an update to @BladeWise to support EFCore 3.1 and TPH.

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

I'm having some trouble getting this solution to work. It looks like the materializerAction isn't getting assigned a delegate. But I don't think I understand this code enough to properly troubleshoot.

instead, I was able to get something working like this:

`using (connection)
{
var command = new SqlCommand(sql, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
foreach (DbParameter param in parameters)
{
command.Parameters.Add(param);
}
connection.Open();
var reader = await command.ExecuteReaderAsync();
int resultidx = 0;
do
{
Type type = resultSetMappingTypes[resultidx];

                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);
                    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);
                resultidx++;

            } while (reader.NextResult());
        }`

[ZarDBA]

It works with this tiny change on EF core 5 according to @mwgolden update
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

[excelthoughts]

This is not working for me. When I call my SP, it is conditional in what SELECT statements returns. Additionally, there are Instead of Insert Triggers returning from other tables being inserted to. I cannot capture the Errors (return when SP raises error) or Ids (SP success.) All I am getting back is the 2 Trigger returns then nothing. Any ideas? What details can I share?

Pseudo code (SP)

IF x Select Id
IF y Select Id, DateTime
IF z Select * From Errors

Pseudo code (Triggers)

Select Id, Bool, Bool From Table1
Select Id, byte[] From Table2

Code calling the MultiResultSetsFromSql

            var reader = await ((DbContext)_dbContext).MultiResultSetsFromSql<InsertTrigger1, InsertTrigger2, Error, int?, Receipt>("SP");

            List<insertTrigger1> insertTrigger1s= reader.FirstResultSet.ToList(); //if an insert has happened to table1
            List<insertTrigger2> insertTrigger2s = reader.SecondResultSet.ToList(); //if an insert has happened to table2
            IEnumerable<Error> errors = reader.ThirdResultSet.AsEnumerable(); //if error
            List<int?> ids= reader.FourthResultSet.ToList(); //if success
            List<Receipt> receipts = reader.FifthResultSet.ToList(); //if receipt

[RickSandstrom]

@mwgolden @BladeWise @ZarDBA @spasarto
We have been using the solution provided by @BladeWise in EF Core 3.1 successfully for a few years. Now we have upgraded to EF Core 6 and I can't get this to work.

TypeMaterializationInfo does not take a RelationalTypeMappingSource as parameter anymore, but a RelationalTypeMapping and I can't seem to get one of those.

The update from @ZarDBA takes me a bit further, but not to a working solution. Maybe I should try rewriting like @mwgolden did?

update:
Rewriting worked like a charm. The updated solution provided by @mwgolden seems to be working, and with much less code (and more understandable code). Thank you!

[ArnaudValensi]

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");

[yogeshfirke07]

how to replace IObjectContextAdapter and replace them with IInfrastructure i am migrating the code from entity framework 6 to entity framework core 6 but i am getting error for IObjectContextAdapter for the below code please suggest -

db.Database.Connection.Open();
var reader = cmd.ExecuteReader();
var blogs = ((IObjectContextAdapter)db)
.ObjectContext
.Translate(reader, "Blogs", MergeOption.AppendOnly);

                foreach (var item in blogs)
                {
                    Console.WriteLine(item.Name);
                }

[anildbest83]

In Entity Framework Core 6, the IObjectContextAdapter interface is no longer available, and instead, the IInfrastructure interface is used.

To replace IObjectContextAdapter with IInfrastructure, you can modify your code as follows:

db.Database.GetDbConnection().Open();
var reader = cmd.ExecuteReader();
var blogs = db.GetService<IStateManager>()
    .RegisterStreamedResults<Blog>(reader, "Blogs")
    .ToList();

foreach (var item in blogs)
{
    Console.WriteLine(item.Name);
}

In the above code, GetService() is used to get an instance of the IStateManager interface, which is used to manage the state of entities in Entity Framework Core. Then RegisterStreamedResults is called to register the streamed results with the state manager and the ToList method is called to materialize the results.

Note that RegisterStreamedResults takes the entity type Blog as its generic type argument and the name of the result set as its second argument. Also, you need to use the GetDbConnection method to get the underlying DbConnection object and call its Open method to open the connection before executing the command.

[yogeshfirke07]

In Entity Framework Core 6, the IObjectContextAdapter interface is no longer available, and instead, the IInfrastructure interface is used.

To replace IObjectContextAdapter with IInfrastructure, you can modify your code as follows:

db.Database.GetDbConnection().Open();
var reader = cmd.ExecuteReader();
var blogs = db.GetService<IStateManager>()
    .RegisterStreamedResults<Blog>(reader, "Blogs")
    .ToList();

foreach (var item in blogs)
{
    Console.WriteLine(item.Name);
}

In the above code, GetService() is used to get an instance of the IStateManager interface, which is used to manage the state of entities in Entity Framework Core. Then RegisterStreamedResults is called to register the streamed results with the state manager and the ToList method is called to materialize the results.

Note that RegisterStreamedResults takes the entity type Blog as its generic type argument and the name of the result set as its second argument. Also, you need to use the GetDbConnection method to get the underlying DbConnection object and call its Open method to open the connection before executing the command.

i am getting error at RegisterStreamedResults method
i am using Microsoft.EntityFrameworkCore -Version 6.0.15

[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.