DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • From Naked Objects to Naked Functions
  • Building a RESTful Service Using ASP.NET Core and dotConnect for PostgreSQL
  • Working With Transactions in Entity Framework Core and Entity Developer
  • Working With Lazy Loading and Eager Loading in Entity Framework Core and Entity Developer

Trending

  • A Modern Stack for Building Scalable Systems
  • Performance Optimization Techniques for Snowflake on AWS
  • Contextual AI Integration for Agile Product Teams
  • Scaling DevOps With NGINX Caching: Reducing Latency and Backend Load
  1. DZone
  2. Coding
  3. Frameworks
  4. ASP.NET Core with Entity Framework Core: Returning Multiple Result Sets

ASP.NET Core with Entity Framework Core: Returning Multiple Result Sets

While it may not be supported, this tutorial shows how to use Entity Framework Core to return back multiple result sets.

By 
Jonathan Danylko user avatar
Jonathan Danylko
·
Dec. 12, 18 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
25.2K Views

Join the DZone community and get the full member experience.

Join For Free

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

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

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.

Entity Framework Database ASP.NET Framework ASP.NET Core

Published at DZone with permission of Jonathan Danylko, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • From Naked Objects to Naked Functions
  • Building a RESTful Service Using ASP.NET Core and dotConnect for PostgreSQL
  • Working With Transactions in Entity Framework Core and Entity Developer
  • Working With Lazy Loading and Eager Loading in Entity Framework Core and Entity Developer

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!