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

From .NET Core 2.1:

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

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

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

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; }