Entity framework Core Raw SQLQueries with custom model

From .NET Core 2.1:

1. Add modelBuilder.Query<YourModel>() to OnModelCreating(ModelBuilder modelBuilder)

2. Use context.Query<YourModel>().FromSql(rawSql) to get data

The question was about .NET Core 2. Now I have a solution and I am going to write it here so that someone else could use it in case he/she needs it.

First of all we add the following method in dbContext class

public List<T> ExecSQL<T>(string query)
{
    using (var command = Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = query;
        command.CommandType = CommandType.Text;
        Database.OpenConnection();

        List<T> list = new List<T>();
        using (var result = command.ExecuteReader())
        {
            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);
            }
        }
        Database.CloseConnection();
        return list;
    }
}

Now we can have the following code.

List<Customer> Customers = _context.ExecSQL<Customer>("SELECT ......");

Here's how I was able to get this working (for completeness):

MyModel.cs:

public class MyModel
{
    // The columns your SQL will return
    public double? A { get; set; }
    public double? B { get; set; }
}

Add class that just inherits from your original EF context class (i called mine DbContextBase):

public class DbContext : DbContextBase
{
    public virtual DbSet<MyModel> MyModels { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Necessary, since our model isnt a EF model
        modelBuilder.Entity<MyModel>(entity =>
        {
            entity.HasNoKey();
        });
    }
}

Use that class (instead of your original EF context class):

// Use your new db subclass
using (var db = new DbContext())
{
    var models = await db.MyModels.FromSqlRaw(...).ToListAsync();    // E.g.: "SELECT * FROM apple A JOIN banana B ON A.col = B.col"
}

Notes:

• If you need to, just use FromSqlInterpolated instead of FromSqlRaw
• The "db context" subclass allows you to update EF models without affecting your "polyfill" code
• Works with SQL Server stored procs that return only 1 result set