Execute raw SQL query in EntityFramework and return non-entity Complex Type

Well, In Entity Framework 6 we have something like Database.SqlQuery which allow us to write raw SQL query and get transformed result in to complex type. Here is a small code snippet that works properly.
There are various to execute raw SQL like:

• DbSet.SqlQuery()// return entity type
• DbContext.Database.SqlQuery()// return non-entity type
• DbContext.Database.ExecuteSqlCommand()// return no of raw affected by execution.

Return non-entity type

using (var context = new DB2016Context())
{
    var blogDetails = context.Database.SqlQuery<BlogDetails>(
"SELECT Name, Description FROM dbo.Blog B inner join dbo.Description D on B.ID=D.BlogId").ToList();
}
 
 
class BlogDetails
{
    public string Name { get; set; }
    public string Description { get; set; }
}

Execution of store procedure with parameter

var result = operationsRepository.Database.ExecuteSqlCommand("exc dbo.ProcessData",
    new SqlParameter("@customer", "CU001"),
    new SqlParameter("@email", "blog.raj111@gmail.com"),
    new SqlParameter("@invoiceDetailID", "INV001")
);

Where are in EntityFrameworkCore 2.0 we have limitation with  work with non-entity type. Please refer attached screen shot:

 But we have DbSet.FromSql where we can always get result and can transform in to entity type.

using (var dbContext = new OTestContext())
{
    var data = dbContext.Subjects.FromSql("select * from subject").ToList();
}

If still want to work with non-entity type in EntityFrameworkCore we can create Extension for the same:

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;
 
namespace Microsoft.EntityFrameworkCore
{
 
    public static class RDFacadeExtensions
    {
        public static List<T> ExecSQLQuery<T>(this DatabaseFacade databaseFacade, string sqlQuery, params object[] parameters)
        {
 
            var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
 
            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = databaseFacade
                    .GetService<IRawSqlCommandBuilder>()
                    .Build(sqlQuery, parameters);
 
                var result = rawSqlCommand
                    .RelationalCommand
                    .ExecuteReader(
                        databaseFacade.GetService<IRelationalConnection>(),
                        parameterValues: rawSqlCommand.ParameterValues).DbDataReader;
 
                List<T> list = new List<T>();
                T obj = default(T);
                while (result.Read())
                {
                    obj = Activator.CreateInstance<T>();
                    foreach (PropertyInfo prop in obj.GetType().GetProperties())
                    {
                        if (!object.Equals(result[prop.Name], DBNull.Value))
                        {
                            prop.SetValue(obj, result[prop.Name], null);
                        }
                    }
                    list.Add(obj);
                }
                return list;
 
            }
 
        }
 
        public static DbDataReader ExecSQLQuery(this DatabaseFacade databaseFacade, string sqlQuery, params object[] parameters)
        {
 
            var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
 
            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = databaseFacade
                    .GetService<IRawSqlCommandBuilder>()
                    .Build(sqlQuery, parameters);
 
                var result = rawSqlCommand
                    .RelationalCommand
                    .ExecuteReader(
                        databaseFacade.GetService<IRelationalConnection>(),
                        parameterValues: rawSqlCommand.ParameterValues).DbDataReader;
 
                return result;
            }
 
        }
 
 
    }
}

And as implementation we can use given snippet and it worked for me. :-)

using (var dbContext = oTestContext)
{
    string query = "";//raw select statement
    var subjects = dbContext.Database.ExecSQLQuery<SubjectView>(query);
}

public class SubjectView
{
    public Int64 ID { get; set; }
    public DateTime EntryDate { get; set; }
    public string Status { get; set; }
    public string Name { get; set; }
}