How to execute SqlQuery with Entity Framework Core 2.1?

Question

In Entity Framework 6, I can execute a raw SQL query on the database using the following command:

IEnumerable<string> Contact.Database.SqlQuery<string>("SELECT a.title FROM a JOIN b ON b.Id = a.aId WHERE b.Status = 10"); 

On a new project, I am trying to use Entity Framework Core 2.1. I have a need to execute raw SQL query. While googling, I can see that the extension SqlQuery was changed to FromSql. However, FromSql only exists on the DbSet<> not on the DbContext.Database.

How can I run FromSql outside the DbSet<>? The method FromSql does not exists on the database object DbContext.Database.FromSql<>.

Answer 1

Updates

This is wokring for EF Core 2.1 but if you're using EF Core 3.0 or higher versions please refer to this complete answer.

---

I can see that the extension SqlQuery was changed to FromSql

But the new FromSql method is more restrcitive than SqlQuery. The documentation of that method explains that it exists some limitations like:

SQL queries can only be used to return entity types that are part of your model. There is an enhancement on our backlog to enable returning ad-hoc types from raw SQL queries.
The SQL query must return data for all properties of the entity or query type.

[...]
Update: more recent GitHub dotnet/efcore discussion Support raw SQL queries without defining an entity type for the result #10753

So in your case the SQL query you're using is the following:

SELECT a.title FROM a JOIN b ON b.Id = a.aId WHERE b.Status = 10 

As the documentation said you can only use FromSql with entity or query type. Your SQL query doesn't return all data of your entity defined in your model but it only returns one column of your entity. By the way a new feature is introduced in EF Core 2.1 which is in Release Candidate since 7 may 2018. Microsoft says:

EF Core 2.1 RC1 is a “go live” release, which means once you test that your application works correctly with RC1, you can use it in production and obtain support from Microsoft, but you should still update to the final stable release once it’s available.

##Using FromSql on query type##

What is a query type:

An EF Core model can now include query types. Unlike entity types, query types do not have keys defined on them and cannot be inserted, deleted or updated (i.e. they are read-only), but they can be returned directly by queries. Some of the usage scenarios for query types are: mapping to views without primary keys, mapping to tables without primary keys, mapping to queries defined in the model, serving as the return type for FromSql() queries

If you want to use query type feature with your SQL text you first define a class, let's name it MySuperClass:

public class MySuperClass {     public string Title { get; set; } } 

Then in your DbContext class defined a property of type DbQuery<MySuperClass> like below:

public DbQuery<MySuperClass> MySuperQuery { get; set; } 

Finally you can use FromSql on it like below:

var result = context.MySuperQuery.FromSql("SELECT a.title FROM a JOIN b ON b.Id = a.aId WHERE b.Status = 10").ToList().First(); var title = result.Title; 

##Don't want to use DbQuery<T>## If you don't want to use DbQuery<T> and don't want to define a class that contains only one property then you can use ExecuteSqlCommandAsync like @vivek nuna did in his answer(his answer is partially correct). But you must know that returned value by that method is the number of rows affected by your query. Also you must put your title as an output parameter so make your query a stored procedure. Use ExecuteSqlCommandAsync or ExecuteSqlCommand and after that read the output parameter you passed when calling the method.

A simpler way without creating a stored procedure therefore not using ExecuteSqlCommandAsync or ExecuteSqlCommand is to the following code:

using (var context = new MyDbContext()) {     var conn = context.Database.GetDbConnection();     await conn.OpenAsync();     var command = conn.CreateCommand();     const string query = "SELECT a.title FROM a JOIN b ON b.Id = a.aId WHERE b.Status = 10";     command.CommandText = query;     var reader = await command.ExecuteReaderAsync();     while (await reader.ReadAsync())     {         var title = reader.GetString(0);         // Do whatever you want with title      } }   

You can make this logic a helper method that will recevie your SQL Query and returns the desired data. But I recommend you use Dapper.Net whcih contains a lot of helpers methods that will help to deal easily with RAW SQL like we do above and also sharing the smae connection with DbContext.