Return Multiple Result Sets or DataSet Using Entity Framework

• DataSetWithEf.zip - 11.8 KB

Background

There was a time when our application was doing many database read calls to load different lists of data to render a particular view. Structurally data's were different but logically they were doing common set of things at Db end, which was increasing the execution time in total. So the plan was to avoid many database round trips and get everything in a single call.

The title may sound like we are doing all the things using Entity Framework, but under the hood the entire process will be done using ADO.NET and some custom mappers. This solution is fit for the application which using single ORM like Entity Framework. From solution point of view here we will be 

1. Running SQL query in database, using Entity Framework connection.
2. Filling DataSet from query result.
3. Getting individual DataTable from the DataSet.
4. Mapping each DataTable to expected entity list List<T>.

Entity Framework Methods

Here we are going to create extension methods for DbContext considering Entity Framework and Entity Framework Core. The extension methods expects two parameters

• SQL query string
• Optional DbParameter objects for parameterized query

Depending on the SQL query, the database will return single/multiple result table. While using this extension method we should make sure the query is return expected table set/sets with rows or no rows. Else we have to use mappers accordingly. 

Entity Framework

using System.Data;
using System.Data.Common;
using System.Data.Entity;

public static class DbContextExtensions
{
    public static DataSet DataSet(this DbContext context, string sqlQuery, params DbParameter[] parameters)
    {
        return context.Database.DataSet(sqlQuery, parameters);
    }

    public static DataSet DataSet(this Database database, string sqlQuery, params DbParameter[] parameters)
    {
        DataSet data = new DataSet();
        DbConnection connection = database.Connection;
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(connection);
        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlQuery;
            if (parameters != null)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }
            using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(data);
            }
        }
        return data;
    }
}

Entity Framework Core

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;

public static class DbContextExtensions
{
    public static DataSet DataSet(this DbContext context, string sqlQuery, params DbParameter[] parameters)
    {
        return context.Database.DataSet(sqlQuery, parameters);
    }

    public static DataSet DataSet(this DatabaseFacade database, string sqlQuery, params DbParameter[] parameters)
    {
        DataSet data = new DataSet();
        DbConnection connection = database.GetDbConnection();
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(connection);
        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlQuery;
            if (parameters != null)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }
            using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(data);
            }
        }
        return data;
    }
}

DataTable to Entity List<T> Mapper

DataSet will contain single/multiple result set or table. This helper method will help us to map DataTable to a List<T> of entity. This mapping process depends on

• Name of the entity property and column of the table
• Data type of the property and column

If they are same, value of the column will be mapped to the property. Mostly I testing will public properties.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;


public static class DataTableExtensions
{
    /*Converts DataTable To List*/
    public static List<TSource> ToList<TSource>(this DataTable dataTable) where TSource : new()
    {
        var dataList = new List<TSource>();

        const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic;
        var objFieldNames = (from PropertyInfo aProp in typeof(TSource).GetProperties(flags)
                             select new { aProp.Name, Type = Nullable.GetUnderlyingType(aProp.PropertyType) ?? aProp.PropertyType }).ToList();
        var dataTblFieldNames = (from DataColumn aHeader in dataTable.Columns
                                 select new { Name = aHeader.ColumnName, Type = aHeader.DataType }).ToList();
        var commonFields = objFieldNames.Intersect(dataTblFieldNames).ToList();

        foreach (DataRow dataRow in dataTable.AsEnumerable().ToList())
        {
            var aTSource = new TSource();
            foreach (var aField in commonFields)
            {
                PropertyInfo propertyInfos = aTSource.GetType().GetProperty(aField.Name);
                var value = dataRow[aField.Name] == DBNull.Value ? null : dataRow[aField.Name]; //if database field is nullable
                propertyInfos.SetValue(aTSource, value, null);
            }
            dataList.Add(aTSource);
        }
        return dataList;
    }
}

This mapping process doesn't depend on the property/column count. Means we are not forcing C# model class to implement all the columns of the table/result-set. We can have any number of proprieties, but only similar one's will get mapped base on name and data type. There is no actual dependency of this mapper and we can use any other library in terms of efficiency.

Data Models

C# Entity 

public class RoleModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

Database Table

DROP TABLE IF EXISTS [dbo].[tb_Test_Role];
GO
CREATE TABLE [dbo].[tb_Test_Role] (
    [Id]   INT IdENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) NOT NULL,
    [IsActive] BIT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

If we select everything from this table the structure will be similar to C# model RoleModel

Database Stored Procedure

DROP PROCEDURE IF EXISTS [dbo].[sp_Test_Role];
GO
CREATE TABLE [dbo].[tb_Test_Role] (
    [Id]   INT IdENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) NOT NULL,
    [IsActive] BIT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE OR ALTER PROCEDURE [dbo].[sp_Test_Role](
    @Name VARCHAR(MAX) = ''
)
AS
BEGIN
/*return data in two different set*/
SELECT * FROM [tb_Test_Role] WHERE IsActive = 0 AND [Name] LIKE '%' + @Name +'%';    --inactive roles
SELECT * FROM [tb_Test_Role] WHERE IsActive = 1 AND [Name] LIKE '%' + @Name +'%';    --active roles
END

The procedure is selecting twice, resulting two result sets, Each set is similar to C# model RoleModel

Data

GO
TRUNCATE TABLE [dbo].[tb_Test_Role]
INSERT INTO [dbo].[tb_Test_Role] ([Name], IsActive)
VALUES ('admin 0', 0), ('sa 0', 0), ('user 0', 0);
INSERT INTO [dbo].[tb_Test_Role] ([Name], IsActive)
VALUES ('admin 1', 1), ('sa 1', 1), ('user 1', 1);
GO


SELECT * FROM [dbo].[tb_Test_Role];
GO
EXEC sp_Test_Role '';

Using Extension Methods

Single Result Set/Table

/*Query returning single result set or table*/
var db = new MopDb();
DataTable allRole = db.DataSet("SELECT * FROM [dbo].[tb_Test_Role]").Tables[0];
List<RoleModel> allRoleList = allRole.ToList<RoleModel>();

Multiple Result Set/Table

/*Stored procedure returning multiple result sets or tables*/
var db = new MopDb();
DataSet searchRole = db.DataSet(
    "EXEC [dbo].[sp_Test_Role] @paramName",
    new SqlParameter("paramName", SqlDbType.NVarChar) { Value = "" }
);
DataTable inactiveRole = searchRole.Tables[0];
DataTable activeRole = searchRole.Tables[1];
List<RoleModel> inactiveRoleList = inactiveRole.ToList<RoleModel>();
List<RoleModel> activeRoleList = activeRole.ToList<RoleModel>();

DbParameter Names for Different DB

• SqlServer: SqlParameter
• Oracle: OracleParameter
• MySql: MySqlParameter
• PostgreSql: NpgsqlParameter

Manage NULL Value

If we want to pass NULL value as parameterized query, we can mange things at two places

SqlParameter Level

int? isActive = 1;
DataSet filterRole = db.DataSet(
    "EXEC [dbo].[sp_Get_Roles] @paramIsActive",
    new SqlParameter("paramIsActive", SqlDbType.Bit) { Value = isActive ?? (object)DBNull.Value, IsNullable = true }
);

Extension Method

Added foreach loop just at the start of the method

public static DataSet DataSet(this Database database, string sqlQuery, params DbParameter[] parameters)
{
    /*added to manage null values*/
    foreach (var parameter in parameters.Where(x => x.Value == null)) 
    {
        parameter.Value = DBNull.Value;
    }
}

Others

Solution And Projects

It is a Visual Studio 2022 solution:

• WithEf is .NET Framework 4.8
• WithEfCore is .NET 6.0

Expecting this code will work from.NET Framework 4.5 and .NET Core 2.2

Connection Strings

App.config at WithEf:

XML

<connectionStrings>
  <add name="MopDbConnection" connectionString="Data Source=10.10.15.13\DB002;
   Initial Catalog=TESTDB; PASSWORD=dhaka; USER ID=FSTEST;"
   providerName="System.Data.SqlClient" />
</connectionStrings>

appsettings.json at WithEfCore:

JavaScript

"ConnectionStrings": {
  "MopDbConnection": "server=10.10.15.13\\DB002;database=TESTDB;
                      user id=FSTEST;password=dhaka"
}

Limitations

The code may throw unexpected errors for untested inputs. If any, just let me know.

History

• 29^th June, 2024: Initial version