How is raw SQL run against an Entity Framework Core context?

Question

I have used Entity Framework for a long time, but have an edge case where I need to use SQL. I was wondering if I could use my existing Entity Framework Core context for this or not. Here is what I have currently, but the queryResults variable contains a "-1" value, instead of a list of Students, after running it:

string tableName = "Students";
var queryResults = db.Database.ExecuteSqlRaw(@"SELECT * FROM {0}", tableName);

Any ideas?

---

• Entity Framework Core 3.1
• .NET Core 3.1
• Linq-to-SQL

Answer 1

"I was wondering if I could use my existing Entity Framework Core context for this or not":

Yes you can use your existing databaseContext but you have to execute that query on your dbContext Entity see the example below:

var sqlCommand = $"SELECT * FROM Students";
var executeSQL = await _context.Students.FromSqlRaw(sqlCommand).ToListAsync();
return Ok(executeSQL);

Output:

Note: As you can see I am executing sqlCommand on Students dbContext this is valid. But using DbContext you cannot pass the table name dynamically. You must need to define it explicitly.

Hope above steps guided you accordingly, You can have a look on official document for more details here

Update Using Ado.Net Connection:

     using (var connection = _context.Database.GetDbConnection())
            {
                connection.Open();
                var tableName = "Students";
                List<Student> _listStudent = new List<Student>();
                var command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = string.Format("SELECT * FROM [{0}];", tableName);
                SqlDataReader reader = (SqlDataReader)command.ExecuteReader();
                while (reader.Read())
                {
                    var student = new Student(); // You have to bind dynamic property here based on your table entities
                    student.FirstName = reader["FirstName"].ToString(); // Remember Type Casting is required here it has to be according to database column data type
                    student.LastName = reader["LastName"].ToString();
                    _listStudent.Add(student);

                }
                reader.Close();
                command.Dispose();
                connection.Close();

            }

Answer 2

It is possible; I just had to do this for a pet project.

You need to reference the Microsoft.EntityFrameworkCore.Relational NuGet.

ConsoleApp Example:

Program.cs

using System.Collections.Generic;

namespace EfDirectSql
{
    class Program
    {
        /*
         * written:  VS2019 .Net Core 3.1 Console App
         * 
         * used nugets:
         * 
         *      Microsoft.EntityFrameworkCore.SqlServer  3.1.0
         *      Microsoft.EntityFrameworkCore.Relational 3.1.0
         *
        */
        static void Main(string[] args)
        {
            // attention: supply your database server name
            ApplicationContext context = new ApplicationContext("?YOURSERVER?", "Master");

            //  note:   leveraging included extension methods for the dbContext class.
            object scalarResult = context.ExecuteScalar("SELECT COUNT(1) FROM Master.dbo.SysObjects");
            object nonQueryResult = context.ExecuteNonQuery("SELECT * FROM Master.dbo.SysObjects");  // likely your -1 

            IEnumerable<SysObject> readerResult = context.ExecuteReader<SysObject>("SELECT * FROM Master.dbo.SysObjects");
        }
    }
}

ApplicationContext.cs

using Microsoft.EntityFrameworkCore;

namespace EfDirectSql
{
    public class ApplicationContext
    : DbContext
    {
        public ApplicationContext(string serverName, string catalogName)
        {
            this.ServerName = serverName;
            this.CatalogName = catalogName;
        }

        public readonly string ServerName;

        public readonly string CatalogName;

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer($"Data Source={this.ServerName};Initial Catalog={this.CatalogName};Integrated Security=true;");

            base.OnConfiguring(optionsBuilder);
        }
    }
}

DbContextExtensions.cs

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

namespace EfDirectSql
{
    public static class DbContextExtensions
    {
        public static object ExecuteScalar
        (
            this DbContext context,
            string sql
        )
        {
            IDbConnection connection = context.Database.GetDbConnection();
            IDbCommand command = connection.CreateCommand();
            object result = null;

            try
            {
                connection.Open();          

                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                result = command.ExecuteScalar();
            }
            finally
            {
                connection.Close();
            }

            return result;
        }

        public static int ExecuteNonQuery
        (
            this DbContext context,
            string sql
        )
        {
            IDbConnection connection = context.Database.GetDbConnection();
            IDbCommand command = connection.CreateCommand();

            int result;

            try
            {
                connection.Open();          

                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                result = command.ExecuteNonQuery();

                //  likely the -1
            }
            finally
            {
                connection.Close();
            }

            return result;
        }

        public static IEnumerable<TType> ExecuteReader<TType>
        (
            this DbContext context,
            string sql
        )
        where TType : class, new()
        {
            IDbConnection connection = context.Database.GetDbConnection();
            IDbCommand command = connection.CreateCommand();

            IEnumerable<TType> result = new List<TType>();

            try
            {
                connection.Open();          

                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                IDataReader reader = command.ExecuteReader(CommandBehavior.Default);

                result = Convert<TType>(reader);
            }
            finally
            {
                connection.Close();
            }

            return result;
        }

        private static IEnumerable<TType> Convert<TType>(IDataReader reader)
        where TType : class, new()
        {
            List<PropertyInfo> properties = typeof(TType)
                .GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Where(p => p.CanWrite)
                .ToList();

            IList<TType> instances = new List<TType>();

            while (reader.Read())
            {
                TType instance = new TType();

                properties
                    .ForEach
                    (p =>
                        // for the purposes of the example, this works - could be outliers.
                        p.SetValue(instance, reader[p.Name] == DBNull.Value ? null : reader[p.Name])
                    );

                instances.Add(instance);
            }

            return instances;
        }
    }
}

SysObject.cs

namespace EfDirectSql
{
    //  shortened represenation of the MS-SQL sysobject table
    public class SysObject
    {
        public string name { get; set; }

        public int id { get; set; }

        public string xtype { get; set; }

        public int uid { get; set; }

        public int info { get; set; }

        public int status { get; set; }

        //  the rest are not needed for a demo.
    }
}