Executing Raw SQL Queries using Entity Framework Core 6

• Download source code - 18.2 KB

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]; //if field is nullable
                            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
  • https://entityframeworkcore.com/knowledge-base/35631903/raw-sql-query-without-dbset---entity-framework-core
  • https://www.learnentityframeworkcore.com/raw-sql
• IDbContextTransaction to DbTransaction
  • https://dapper-tutorial.net/knowledge-base/46566756/how-do-i-get-an-idbtransaction-from-an-idbcontext-
• DbDataReader to List
  • https://stackoverflow.com/questions/1464883/how-can-i-easily-convert-datareader-to-listt
• DbDataReader Column Name and Types
  • https://stackoverflow.com/a/27200892/2948523

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

• 10^th August, 2022: Initial version