Entity framework Core Raw SQLQueries with custom model

Question

Using Entity Framework 6, I was able to use execute a Raw SQL Query and use a custom model which was not defined in the DBContext in order to store the output of the query. A simple example is the following:

List<MyModel> data = context.Database.SqlQuery<MyModel>("SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;").ToList();

I execute one SQL command and I expect a list of custom models.

I try to do something similar with Entity Framework Core and the closest example that I found will force me to define a property from DBContext. This will not allow me to use a custom model to fill the data that SQL server will return.

var books = context.Books.FromSql("SELECT * FROM Books").ToList();

This query informs Entity Framework Core that the query will return a list of books. Is there a way to implement something like this in Entity Framework Core?

Answer 1

follow these steps:

Create your model

Probably it could be better if you can reduce it to a model as generic as possible but it's not a must:

public class MyCustomModel
{
   public string Text { get; set; }
   public int Count { get; set; }
}

Add it to your own DbContext

Create DbSet for your custom model

public virtual DbSet<MyCustomModel> MyCustomModelName { get; set; }

Keep in mind to specify your custom model has no key

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

    modelBuilder.Entity<MyCustomModel>().HasNoKey();
}

Use it from your dbContext instance

async public Task<List<MyCustomModel>> GetMyCustomData()
{
    var rv = new List<MyCustomModel>();
    using (var dataContext = new DbContext())
    {
        var sql = @"
            select textField as 'Text', count(1) as 'Count'
            from MyTable";
        rv = await dataContext.Set<MyCustomModel>().FromSqlRaw(sql).ToListAsync();
    }
    return rv;
}

Answer 2

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

Answer 3

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 ......");

Answer 4

From .NET Core 2.1:

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

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