ASP.NET Core with Entity Framework Core: Returning Multiple ResultSets

While it may not be supported, today's post shows how to use Entity Framework Core to return back multiple result sets.

One of the things I like most about Entity Framework is the various ways you can retrieve data to hydrate your entities.

You can retrieve data using LINQ (command or fluent with methods), ExecuteQuery (and all of it's various flavors), or even ADO.NET with straight SQL.

Entity Framework Core gave us all of these capabilities. The one thing missing was the ability to return multiple result sets.

In a past article, I demonstrated how to return multiple result sets from a stored procedure.

With Entity Framework in ASP.NET Core, things are a little trickier. 

Why Bother?

Why not just use Entity Framework Core to return back all of the data we need and be done with it?

Let's look at an example.

If my application needs the following data for a dashboard screen, it may require:

• Menu Entities - A list of MenuItems (from a Menu table).
• Permission Entities - A list of Permissions of which menu items can be displayed or hidden (from a Permission table).
• Notifications - Notifications to let the user know what's happening around the system (no relations...just a UserID attached to the Notifications table)

If we use regular LINQ, we are making three database calls to retrieve our data. One for each table of data (queried, of course). Ok, Ok, maybe two calls because of the Permission relationship pointing to a Menu.

If we use multiple result sets, we are making one call to a stored procedure and mapping the data into our entities.

Imagine if you had more than three tables requiring data. Since this is a dashboard, you may have more placeholders in numerous tables. Let's say you need nine tables.

That's nine hits to the database. Yikes!

This is why I firmly believe making one call to a stored procedure is faster than numerous calls.

No Translate?

One of the key components in Entity Framework (pre-Core) was the ability to translate a DbDataReader into a Entity. This was done by calling the Translate<T> method on an ObjectContext.

In Entity Framework Core, the Translate<T> method isn't available. Therefore, we need to write our own.

We'll call our new class...the "Materializer" (I guess it's better than calling it The Terminator).

Our Materializer will have the following requirements:

1. If an entity's property references another entity, bypass it.
2. If an entity's property references a collection, bypass it.
3. If an entity's property has a [NotMapped] attribute attached to it, bypass it.

Basically, we only want to map an IDataRecord to an Entity's native types.

This will make the mapping easier instead of following ICollections down a rabbit hole and into "Recursion-Land" (Yes, it's a place).

Building the Materializer

Here's the flow of the code.

1. The materializer will get a list of properties from T using reflection.
2. Make sure the requirements are met (see above).
3. Map the IDataRecord values to the Entity.

The code is a simple static class.

using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;

public class Materializer
{
    public static T Materialize<T>(IDataRecord record) where T : new()
    {
        var t = new T();
        foreach (var prop in typeof(T).GetProperties())
        {
            // 1). If entity reference, bypass it.
            if (prop.PropertyType.Namespace == typeof(T).Namespace)
            {
                continue;
            }

            // 2). If collection, bypass it.
            if (prop.PropertyType != typeof(string) && typeof(IEnumerable).IsAssignableFrom(prop.PropertyType))
            {
                continue;
            }

            // 3). If property is NotMapped, bypass it.
            if (Attribute.IsDefined(prop, typeof(NotMappedAttribute)))
            {
                continue;
            }

            // If the property doesn't map to an existing field, just continue.
            if (!record.Exists(prop.Name)) continue;

            var dbValue = record[prop.Name];
            if (dbValue is DBNull) continue;

            if (prop.PropertyType.IsConstructedGenericType &&
                prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                var baseType = prop.PropertyType.GetGenericArguments()[0];
                var baseValue = Convert.ChangeType(dbValue, baseType);
                var value = Activator.CreateInstance(prop.PropertyType, baseValue);
                prop.SetValue(t, value);
            }
            else
            {
                var value = Convert.ChangeType(dbValue, prop.PropertyType);
                prop.SetValue(t, value);
            }
        }

        return t;
    }
}

Next, we focus on the DataRecord itself.

Since the DbDataReader is an IDataRecord itself, we can use my favorite .NET concept of Extension Methods.

public static class DataRecordExtensions
{
    private static readonly ConcurrentDictionary<Type, object> _materializers = new ConcurrentDictionary<Type, object>();

    public static IList<T> Translate<T>(this DbDataReader reader) where T : new()
    {
        var materializer = (Func<IDataRecord, T>)_materializers.GetOrAdd(typeof(T), (Func<IDataRecord, T>)Materializer.Materialize<T>);
        return Translate(reader, materializer, out var hasNextResults);
    }

    public static IList<T> Translate<T>(this DbDataReader reader, Func<IDataRecord, T> objectMaterializer)
    {
        return Translate(reader, objectMaterializer, out var hasNextResults);
    }

    public static IList<T> Translate<T>(this DbDataReader reader, Func<IDataRecord, T> objectMaterializer, 
        out bool hasNextResult)
    {
        var results = new List<T>();
        while (reader.Read())
        {
            var record = (IDataRecord)reader;
            var obj = objectMaterializer(record);
            results.Add(obj);
        }

        hasNextResult = reader.NextResult();

        return results;
    }

    public static bool Exists(this IDataRecord record, string propertyName)
    {
        return Enumerable.Range(0, record.FieldCount).Any(x => record.GetName(x) == propertyName);
    }
}

If we had a number of materializers, I wanted to keep them in a collection to reuse them. Think of it like a thread-safe cache of materializers.

Hence, the need for the ConcurrentDictionary.

Since I was missing the Translate<T> method, I thought a DbDataReader Extension Method would suffice.

Performing a Translate<T>

As an example, if we follow through with the example from above and we have a DashboardData class,

public class DashboardData
{
    public IEnumerable<MenuItem> MenuItems { get;set; }
    public IEnumerable<Permission> Permissions { get; set; }
}

Our repository call would follow the same example in the old post with a mapper class.

public async Task<DashboardData> GetDashboardDataAsync()
{
    return await ExecuteReaderAsync(DashboardDataMapper, "[dbo].[GetDashboardData]");
}

public DashboardData DashboardDataMapper(DbDataReader reader)
{
    var result = new DashboardData
    {
        // Result Set 1 - MenuItems
        MenuItems = reader.Translate<MenuItem>(),
        
        // Result Set 2 - Root MeuItems
        Permissions = reader.Translate<Permission>()
    };

    return result;
}

A couple notes about this approach:

• Of course, these entities are per-table based on what query is returned. They will only populate native types and not the entity references. You will have to manually set those. For example, if a MenuItem references a single Permission entity which is a property, the Permission property would be null and you would have to manually set it.
• The resulting data returned is merely a simple POCO or DTO (Data Transfer Object). The calling method could transform or map this result data (DashboardData, in this case) into something more usable.

Conclusion

In this updated approach, I've shown how to return multiple result sets using Entity Framework Core and some modifications to the previous code.

On the surface, this code seems to work quite well.

This technique should solve any issues centered around returning multiple result sets with Entity Framework Core.

I'll continue to refactor as I dig into it further. But do you have any suggestions regarding this code?

Do you use multiple result sets? Is there a more optimized way of executing multiple queries? Post your comments below and let's discuss.