[Solved] Microsoft Azure: Alternative for database.SqlQuery() from Entity Framework 6.x for Entity Framework Core

In case of Azure Web Jobs or Azure Functions  when You try to find  alternative for database.SqlQuery<T>() from Entity Framework  for Entity Framework Core (equivalent doesn’t exists ) You can use presented below solution  in C#. Solution tested and works very good in practice on production environments.

Let’s say that we want use below code from Entity Framework 6.x:

using (var database = new DbContext())
{
var result = database.SqlQuery<long>(string.Format("EXEC [dbo].[INSERT_Transaction] @DeviceID = {0}, @UserID = {1}, @Note = '{2}'",
deviceId,
userId,
note))
.First();
}



and  use under Entity Framework Core. Below is one from many possible implementation based on farther presented static class:

List<long> resultList = new List<long>();

using (var database = new DbContext())
{
resultList = database.LoadStoredProc("[dbo].[INSERT_Transaction]]")
.WithSqlParam("DeviceID", deviceId)
.WithSqlParam("UserID", userId)
.WithSqlParam("Note", note)
.ExecuteStoredProc<long>().Result;

var result = resultList.First();
}

As returned result You can use different types <T> also Your own classes.

List<T> resultList = new List<T>();
 
using (var database = new DbContext())
{
resultList = database.LoadStoredProc("[dbo].[INSERT_Transaction]]")
.WithSqlParam("DeviceID", deviceId)
.WithSqlParam("UserID", userId)
.WithSqlParam("Note", note)
.ExecuteStoredProc<T>().Result;
 
var result = resultList.First();
}

Solution works in async or sync mode without any problems. If You want use async mode then remove .Result from the source code;

Mentioned by me static class with eqvivalent functions:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace AzureFunctions.Helpers
{

    public static class SqlCommand
    {
        public static DbCommand LoadStoredProc(this DbContext context, string storedProcName)
        {
            var cmd = context.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = storedProcName;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            return cmd;
        }


        public static DbCommand WithSqlParam(
      this DbCommand cmd, string paramName, object paramValue)
        {
            if (string.IsNullOrEmpty(cmd.CommandText))
                throw new InvalidOperationException(
                  "Call LoadStoredProc before using this method");
            var param = cmd.CreateParameter();
            param.ParameterName = paramName;
            param.Value = paramValue;
            cmd.Parameters.Add(param);
            return cmd;
        }

        private static List<T> MapToList<T>(this DbDataReader dr)
        {
            var objList = new List<T>();
            var props = typeof(T).GetRuntimeProperties();

            var colMapping = dr.GetColumnSchema()
              .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
              .ToDictionary(key => key.ColumnName.ToLower());

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    T obj = Activator.CreateInstance<T>();
                    foreach (var prop in props)
                    {
                        var val =
                          dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
                        prop.SetValue(obj, val == DBNull.Value ? null : val);
                    }
                    objList.Add(obj);
                }
            }
            return objList;
        }
        public static async Task<List<T>> ExecuteStoredProc<T>(this DbCommand command)
        {
            using (command)
            {
                if (command.Connection.State == System.Data.ConnectionState.Closed)
                    command.Connection.Open();
                try
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        return reader.MapToList<T>();
                    }
                }
                catch (Exception e)
                {
                    throw (e);
                }
                finally
                {
                    command.Connection.Close();
                }
            }
        }
    }
      
    }

August 9th, 2019