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
- Running SQL query in database, using Entity Framework connection.
- Filling
DataSet from query result. - Getting individual
DataTable from the DataSet. - 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
{
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];
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
SELECT * FROM [tb_Test_Role] WHERE IsActive = 0 AND [Name] LIKE '%' + @Name +'%';
SELECT * FROM [tb_Test_Role] WHERE IsActive = 1 AND [Name] LIKE '%' + @Name +'%';
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
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
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)
{
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.8WithEfCore 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
- 29th June, 2024: Initial version
