In this article, we explore both existing and custom options to run row SQL in EF Core, but focus more on extension method implementations using ADO.NET.
Background
Entity Framework Core allows us to drop down to raw SQL queries when working with a relational database. Plus it provides mechanisms for executing raw SQL queries directly against the database using ADO.NET features. Here, we are going to explore both existing and custom options to run row SQL in Entity Framework Core but will focus more on extension method implementations using ADO.NET.
Existing Options
In Entity Framework Core, there are multiple options to run raw SQL quires. To use them, we need to install Microsoft.EntityFrameworkCore.Relational along with Microsoft.EntityFrameworkCore:
Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Relational
ExecuteSqlRaw
Executes a non query SQL. Here are some insert, update, and delete examples. Parametrized query is optional, we can skip it if needed.
Insert
object[] paramItems = new object[]
{
new SqlParameter("@paramName", "Ben"),
new SqlParameter("@paramCreatedBy", "Ben"),
new SqlParameter("@paramCreatedOn", DateTime.UtcNow),
new SqlParameter("@paramIsDeleted", true),
};
int items = Db.Database.ExecuteSqlRaw("INSERT INTO Users([Name],
[IsDeleted], CreatedOn, CreatedBy) VALUES (@paramName, @paramIsDeleted,
@paramCreatedOn, @paramCreatedBy)", paramItems);
Update
object[] paramItems = new object[]
{
new SqlParameter("@paramEmail", "ben@gmail.com"),
new SqlParameter("@paramName", "Ben")
};
int items = Db.Database.ExecuteSqlRaw
("UPDATE Users SET Email = @paramEmail WHERE [Name] = @paramName", paramItems);
Delete
object[] paramItems = new object[]
{
new SqlParameter("@paramName", "Ben")
};
int items = Db.Database.ExecuteSqlRaw("DELETE FROM Users
WHERE [Name] = @paramName", paramItems);
Before 3.1, there was ExecuteSqlCommand.
FromSqlRaw<T>
Selects data and maps to existing DbSet<TSource>.
List<User> usersInDb = Db.Users.FromSqlRaw
(
"SELECT * FROM Users WHERE Name=@paramName",
new SqlParameter("@paramName", user.Name)
)
.ToList();
This is applicable only with DbSet declarations. Here Users is a DbSet<T>, declared inside the DbContext.
public class CpuAppDbContext : DbContext
{
public DbSet<User> Users { get; set; }
}
FromSqlInterpolated<T>
List<User> usersInDb = Db.Users.FromSqlInterpolated<User>
(
$"SELECT * FROM Users WHERE Name={user.Name}"
)
.ToList();
Custom DbContext Extension Methods
Here are some extension methods for DbContext and DatabaseFacade objects to run raw SQLs. In the helper class EfSqlHelper.cs of Database.Core project, we will find the listed extension methods.
ExecuteScalar
- Returns the first column of the first row in the result set returned by the query
- Optional query parametrization
- Optional command type and command timeout
ExecuteNonQuery
- Executes Raw SQL queries that do not return any data
- Returns the number of affected rows
- Optional query parametrization
- Optional command type and command timeout
- Supports Db transaction
FromSqlQuery<T>
- Executes Raw SQL queries that do return data
Mapp returned data rows to a given type T
- Mapp's data manually
- Mapp's data automatically
- Optional query parametrization.
- Optional command type and command timeout
FromSqlRaw<T>
- A generic wrapper for builtin
FromSqlRaw
ExecuteScalar
Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
namespace EfCoreHelper.Database.Core
{
public static class EfSqlHelper
{
public static DbTransaction GetDbTransaction(this IDbContextTransaction source)
{
return (source as IInfrastructure<DbTransaction>).Instance;
}
public static object ExecuteScalar(this DbContext context, string sql,
List<DbParameter> parameters = null,
CommandType commandType = CommandType.Text,
int? commandTimeOutInSeconds = null)
{
Object value = ExecuteScalar(context.Database, sql, parameters,
commandType, commandTimeOutInSeconds);
return value;
}
public static object ExecuteScalar(this DatabaseFacade database,
string sql, List<DbParameter> parameters = null,
CommandType commandType = CommandType.Text,
int? commandTimeOutInSeconds = null)
{
Object value;
using (var cmd = database.GetDbConnection().CreateCommand())
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.CommandText = sql;
cmd.CommandType = commandType;
if (commandTimeOutInSeconds != null)
{
cmd.CommandTimeout = (int)commandTimeOutInSeconds;
}
if (parameters != null)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
value = cmd.ExecuteScalar();
}
return value;
}
}
}
In the extraction methods, we are using ADO.NET features. From the Ef DbContext's database object, we are accessing the underlying DB connection object and creating the Db command from it. Then assigning all the required parameters to the command object like the SQL, Command Type, SQL parameters, using existing DB transition, and optional command timeout to the newly created command. Finally, calling ExecuteScalar() to execute the raw SQL query.
int count = (int)Db.ExecuteScalar
(
"SELECT COUNT(1) FROM Users WHERE Name=@paramName",
new List<DbParameter>() { new SqlParameter("@paramName", user.Name) }
);
ExecuteNonQuery
Executes a Transact-SQL statement against the connection and returns the number of rows affected.
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
namespace EfCoreHelper.Database.Core
{
public static class EfSqlHelper
{
public static DbTransaction GetDbTransaction(this IDbContextTransaction source)
{
return (source as IInfrastructure<DbTransaction>).Instance;
}
public static int ExecuteNonQuery(this DbContext context, string command,
List<DbParameter> parameters = null,
CommandType commandType = CommandType.Text,
int? commandTimeOutInSeconds = null)
{
int value = ExecuteNonQuery(context.Database, command,
parameters, commandType, commandTimeOutInSeconds);
return value;
}
public static int ExecuteNonQuery(this DatabaseFacade database,
string command, List<DbParameter> parameters = null,
CommandType commandType = CommandType.Text,
int? commandTimeOutInSeconds = null)
{
using (var cmd = database.GetDbConnection().CreateCommand())
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
var currentTransaction = database.CurrentTransaction;
if (currentTransaction != null)
{
cmd.Transaction = currentTransaction.GetDbTransaction();
}
cmd.CommandText = command;
cmd.CommandType = commandType;
if (commandTimeOutInSeconds != null)
{
cmd.CommandTimeout = (int)commandTimeOutInSeconds;
}
if (parameters != null)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
return cmd.ExecuteNonQuery();
}
}
}
}
The extraction methods are quite similar to the previous one. From the DbContext's database object, create the Db command. Then, assign all the required parameters to the command object like the SQL, Command Type, SQL parameters, use existing DB transition, and optional command timeout to the command. Finally, calling ExecuteNonQuery() to execute the raw SQL query.
Db.ExecuteNonQuery("DELETE FROM Users WHERE Id < @paramId", new List<DbParameter>()
{ new SqlParameter("@paramId", user.Id) });
Use With Transaction
Exception error = null;
using (var tran = Db.Database.BeginTransaction())
{
try
{
Db.ExecuteNonQuery("UPDATE Users SET Email =
@paramEmail WHERE Id = @paramId", new List<DbParameter>()
{ new SqlParameter("@paramEmail", newEmailOfOldUser),
new SqlParameter("@paramId", oldUser.Id) });
Db.ExecuteNonQuery("UPDATE Users SET Email =
@paramEmail WHERE Id = @paramId", new List<DbParameter>()
{ new SqlParameter("@paramEmail", newEmailOfUser),
new SqlParameter("@paramId", user.Id) });
tran.Commit();
}
catch (Exception ex)
{
error = ex;
tran.Rollback();
}
}
Use With Transaction Scope
Exception error = null;
using (var scope = new TransactionScope())
{
try
{
Db.ExecuteNonQuery("UPDATE Users SET Email =
@paramEmail WHERE Id = @paramId", new List<DbParameter>()
{ new SqlParameter("@paramEmail", newEmailOfOldUser),
new SqlParameter("@paramId", oldUser.Id) });
Db.ExecuteNonQuery("UPDATE Users SET Email = @paramEmail WHERE Id = @paramId",
new List<DbParameter>() { new SqlParameter("@paramEmail", newEmailOfUser),
new SqlParameter("@paramId", user.Id) });
scope.Complete();
}
catch (Exception ex)
{
error = ex;
}
}
FromSqlQuery<T>
Creates a raw SQL query that will return elements of the given generic type. In older Entity Framework versions, there was Database.SqlQuery<T> which used to do a similar thing, but dropped in newer versions/core. Now this generic type mapping can be done in two ways:
- Mapping data manually
- Mapping data automatically
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
namespace EfCoreHelper.Database.Core
{
public static class EfSqlHelper
{
private class PropertyMapp
{
public string Name { get; set; }
public Type Type { get; set; }
public bool IsSame(PropertyMapp mapp)
{
if (mapp == null)
{
return false;
}
bool same = mapp.Name == Name && mapp.Type == Type;
return same;
}
}
public static DbTransaction GetDbTransaction(this IDbContextTransaction source)
{
return (source as IInfrastructure<DbTransaction>).Instance;
}
public static IEnumerable<T> FromSqlQuery<T>
(this DbContext context, string query, List<DbParameter> parameters = null,
CommandType commandType = CommandType.Text,
int? commandTimeOutInSeconds = null) where T : new()
{
return FromSqlQuery<T>(context.Database, query, parameters,
commandType, commandTimeOutInSeconds);
}
public static IEnumerable<T> FromSqlQuery<T>
(this DatabaseFacade database, string query,
List<DbParameter> parameters = null,
CommandType commandType = CommandType.Text,
int? commandTimeOutInSeconds = null) where T : new()
{
const BindingFlags flags = BindingFlags.Public |
BindingFlags.Instance | BindingFlags.NonPublic;
List<PropertyMapp> entityFields = (from PropertyInfo aProp
in typeof(T).GetProperties(flags)
select new PropertyMapp
{
Name = aProp.Name,
Type = Nullable.GetUnderlyingType
(aProp.PropertyType) ?? aProp.PropertyType
}).ToList();
List<PropertyMapp> dbDataReaderFields = new List<PropertyMapp>();
List<PropertyMapp> commonFields = null;
using (var command = database.GetDbConnection().CreateCommand())
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
var currentTransaction = database.CurrentTransaction;
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = query;
command.CommandType = commandType;
if (commandTimeOutInSeconds != null)
{
command.CommandTimeout = (int)commandTimeOutInSeconds;
}
if (parameters != null)
{
command.Parameters.AddRange(parameters.ToArray());
}
using (var result = command.ExecuteReader())
{
while (result.Read())
{
if (commonFields == null)
{
for (int i = 0; i < result.FieldCount; i++)
{
dbDataReaderFields.Add(new PropertyMapp
{ Name = result.GetName(i),
Type = result.GetFieldType(i) });
}
commonFields = entityFields.Where
(x => dbDataReaderFields.Any(d =>
d.IsSame(x))).Select(x => x).ToList();
}
var entity = new T();
foreach (var aField in commonFields)
{
PropertyInfo propertyInfos =
entity.GetType().GetProperty(aField.Name);
var value = (result[aField.Name] == DBNull.Value) ?
null : result[aField.Name];
propertyInfos.SetValue(entity, value, null);
}
yield return entity;
}
}
}
}
public static IEnumerable<T> FromSqlQuery<T>
(this DbContext context, string query, Func<DbDataReader, T> map,
List<DbParameter> parameters = null, CommandType commandType = CommandType.Text,
int? commandTimeOutInSeconds = null)
{
return FromSqlQuery(context.Database, query, map, parameters,
commandType, commandTimeOutInSeconds);
}
public static IEnumerable<T> FromSqlQuery<T>
(this DatabaseFacade database, string query, Func<DbDataReader, T> map,
List<DbParameter> parameters = null,
CommandType commandType = CommandType.Text,
int? commandTimeOutInSeconds = null)
{
using (var command = database.GetDbConnection().CreateCommand())
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
var currentTransaction = database.CurrentTransaction;
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = query;
command.CommandType = commandType;
if (commandTimeOutInSeconds != null)
{
command.CommandTimeout = (int)commandTimeOutInSeconds;
}
if (parameters != null)
{
command.Parameters.AddRange(parameters.ToArray());
}
using (var result = command.ExecuteReader())
{
while (result.Read())
{
yield return map(result);
}
}
}
}
}
}
Model
public class UserModel
{
public string Name { get; set; }
public string Email { get; set; }
public bool? IsDeleted { get; set; }
}
Table
DROP TABLE IF EXISTS [dbo].[Users]
GO
CREATE TABLE [dbo].[Users](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[Email] [nvarchar](max) NULL,
[IsDeleted] [bit] NOT NULL,
[CreatedOn] [datetime2](7) NOT NULL,
[CreatedBy] [nvarchar](max) NOT NULL,
[ModifiedOn] [datetime2](7) NULL,
[ModifiedBy] [nvarchar](max) NULL
)
Here, Name, Email, and IsDeleted are present both in the C# model and Db table. Data types are also similar.
Manually Mapping
We can do manual mapping using both column index or column names.
Using Index
List<UserModel> usersInDb = Db.FromSqlQuery
(
"SELECT Name, Email FROM Users WHERE Name=@paramName",
x => new UserModel
{
Name = (string)x[0],
Email = (string)x[1]
},
new List<DbParameter>() { new SqlParameter("@paramName", user.Name) }
)
.ToList();
Using Column Name
List<UserModel> usersInDb = Db.FromSqlQuery
(
"SELECT Name, Email FROM Users WHERE Name=@paramName",
x => new UserModel
{
Name = x["Name"] is DBNull ? "" : (string)x["Name"],
Email = x["Email"] is DBNull ? "" : (string)x["Email"]
},
new List<DbParameter>() { new SqlParameter("@paramName", user.Name) }
)
.ToList();
Auto Mapping
The mapping process depends on the class property name, data type vs column name, and data type. This auto mapping is done using reflection. So it is better not to select a very large data set using this method.
List<UserModel> usersInDb = Db.FromSqlQuery<UserModel>
(
"SELECT Name, Email, IsDeleted FROM Users WHERE Name=@paramName",
new List<DbParameter>() { new SqlParameter("@paramName", user.Name) }
)
.ToList();
FromSqlRaw<T>
This is a generic wrapper of existing FromSqlRaw<T>:
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
namespace EfCoreHelper.Database.Core
{
public static class EfSqlHelper
{
public static IQueryable<TSource> FromSqlRaw<TSource>
(this DbContext db, string sql, params object[] parameters)
where TSource : class
{
var item = db.Set<TSource>().FromSqlRaw(sql, parameters);
return item;
}
}
}
TSource should be included in DbContext as DbSet<TSource>.
Using the Extension Method
List<User> usersInDb = Db.FromSqlRaw<User>
(
"SELECT * FROM Users WHERE Name=@paramName",
new SqlParameter("@paramName", user.Name)
)
.ToList();
Limitations
- The stored procedure is not tested, but should work like
EXEC sp_name or using command type CommandType.StoredProcedure FromSqlQuery<T> automapping is done using reflection. Can face performance issues with large datasets.- Avoid joining
FromSqlQuery<T> others IEnumerable<T> in Linq ExecuteNonQuery or FromSqlQuery<T> SQL statements execute immediately, regardless of whether or not we call Db.SaveChanges()ExecuteSqlRaw or FromSqlRaw<T> also, execute immediately- Tested with SQL Server and Oracle
References
- Run Raw SQL
- IDbContextTransaction to DbTransaction
- DbDataReader to List
- DbDataReader Column Name and Types
About Code Sample
- Visual Studio 2022 Solution
- ASP.NET 6
- EF Core 6
- This example is also tested in core 5
Database.Test is an intrigued unit test project. Change the connection string in appsettings.json. Create Users table in db, check db.sql of project Database.Application. Check/Run unit tests of EfSqlHelperTests.cs
{
"ConnectionStrings": {
"DatabaseConnection": "Data Source=.\\SQLEXPRESS;Initial Catalog=Cup;Integrated Security=True"
}
}
DROP TABLE IF EXISTS [dbo].[Users]
GO
CREATE TABLE [dbo].[Users](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[Email] [nvarchar](max) NULL,
[IsDeleted] [bit] NOT NULL,
[CreatedOn] [datetime2](7) NOT NULL,
[CreatedBy] [nvarchar](max) NOT NULL,
[ModifiedOn] [datetime2](7) NULL,
[ModifiedBy] [nvarchar](max) NULL
)
History
- 10th August, 2022: Initial version
