With Entity Framework Core removing dbData.Database.SqlQuery<SomeModel> I can't find a solution to build a raw SQL Query for my full-text search query that will return the tables data and also the rank.
The only method I've seen to build a raw SQL query in Entity Framework Core is via dbData.Product.FromSql("SQL SCRIPT"); which isn't useful as I have no DbSet that will map the rank I return in the query.
Any Ideas???
It depends if you're using EF Core 2.1 or EF Core 3 and higher versions.
If you're using EF Core 2.1 Release Candidate 1 available since 7 may 2018, you can take advantage of the proposed new feature which is Query type.
What is query type?
In addition to entity types, an EF Core model can contain query types, which can be used to carry out database queries against data that isn't mapped to entity types.
When to use query type?
Serving as the return type for ad hoc FromSql() queries.
Mapping to database views.
Mapping to tables that do not have a primary key defined.
Mapping to queries defined in the model.
So you no longer need to do all the hacks or workarounds proposed as answers to your question. Just follow these steps:
First you defined a new property of type DbQuery<T> where T is the type of the class that will carry the column values of your SQL query. So in your DbContext you'll have this:
public DbQuery<SomeModel> SomeModels { get; set; }
Secondly use FromSql method like you do with DbSet<T>:
var result = context.SomeModels.FromSql("SQL_SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();
Also note that DdContexts are partial classes, so you can create one or more separate files to organize your 'raw SQL DbQuery' definitions as best suits you.
Query type is now known as Keyless entity type. As said above query types were introduced in EF Core 2.1. If you're using EF Core 3.0 or higher version you should now consider using keyless entity types because query types are now marked as obsolete.
This feature was added in EF Core 2.1 under the name of query types. In EF Core 3.0 the concept was renamed to keyless entity types. The [Keyless] Data Annotation became available in EFCore 5.0.
We still have the same scenarios as for query types for when to use keyless entity type.
So to use it you need to first mark your class SomeModel with [Keyless] data annotation or through fluent configuration with .HasNoKey() method call like below:
public DbSet<SomeModel> SomeModels { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<SomeModel>().HasNoKey();
}
After that configuration, you can use one of the methods explained here to execute your SQL query. For example you can use this one:
var result = context.SomeModels.FromSqlRaw("SQL SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();
Building on the other answers I've written this helper that accomplishes the task, including example usage:
public static class Helper
{
public static List<T> RawSqlQuery<T>(string query, Func<DbDataReader, T> map)
{
using (var context = new DbContext())
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
context.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
var entities = new List<T>();
while (result.Read())
{
entities.Add(map(result));
}
return entities;
}
}
}
}
Usage:
public class TopUser
{
public string Name { get; set; }
public int Count { get; set; }
}
var result = Helper.RawSqlQuery(
"SELECT TOP 10 Name, COUNT(*) FROM Users U"
+ " INNER JOIN Signups S ON U.UserId = S.UserId"
+ " GROUP BY U.Name ORDER BY COUNT(*) DESC",
x => new TopUser { Name = (string)x[0], Count = (int)x[1] });
result.ForEach(x => Console.WriteLine($"{x.Name,-25}{x.Count}"));
I plan to get rid of it as soon as built-in support is added. According to a statement by Arthur Vickers from the EF Core team it is a high priority for post 2.0. The issue is being tracked here.
In EF Core you no longer can execute "free" raw sql. You are required to define a POCO class and a DbSet for that class.
In your case you will need to define Rank:
var ranks = DbContext.Ranks
.FromSql("SQL_SCRIPT OR STORED_PROCEDURE @p0,@p1,...etc", parameters)
.AsNoTracking().ToList();
As it will be surely readonly it will be useful to include the .AsNoTracking() call.
EDIT - Breaking change in EF Core 3.0:
DbQuery() is now obsolete, instead DbSet() should be used (again). If you have a keyless entity, i.e. it don't require primary key, you can use HasNoKey() method:
ModelBuilder.Entity<SomeModel>().HasNoKey()
More information can be found here
For now, until there is something new from EFCore I would used a command and map it manually
using (var command = this.DbContext.Database.GetDbConnection().CreateCommand())
{
command.CommandText = "SELECT ... WHERE ...> @p1)";
command.CommandType = CommandType.Text;
var parameter = new SqlParameter("@p1",...);
command.Parameters.Add(parameter);
this.DbContext.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
while (result.Read())
{
.... // Map to your entity
}
}
}
Try to SqlParameter to avoid Sql Injection.
dbData.Product.FromSql("SQL SCRIPT");
FromSql doesn't work with full query. Example if you want to include a WHERE clause it will be ignored.
Some Links:
You can execute raw sql in EF Core - Add this class to your project. This will allow you to execute raw SQL and get the raw results without having to define a POCO and a DBSet. See https://github.com/aspnet/EntityFramework/issues/1862#issuecomment-220787464 for original example.
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System.Threading;
using System.Threading.Tasks;
namespace Microsoft.EntityFrameworkCore
{
public static class RDFacadeExtensions
{
public static RelationalDataReader ExecuteSqlQuery(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
{
var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = databaseFacade
.GetService<IRawSqlCommandBuilder>()
.Build(sql, parameters);
return rawSqlCommand
.RelationalCommand
.ExecuteReader(
databaseFacade.GetService<IRelationalConnection>(),
parameterValues: rawSqlCommand.ParameterValues);
}
}
public static async Task<RelationalDataReader> ExecuteSqlQueryAsync(this DatabaseFacade databaseFacade,
string sql,
CancellationToken cancellationToken = default(CancellationToken),
params object[] parameters)
{
var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = databaseFacade
.GetService<IRawSqlCommandBuilder>()
.Build(sql, parameters);
return await rawSqlCommand
.RelationalCommand
.ExecuteReaderAsync(
databaseFacade.GetService<IRelationalConnection>(),
parameterValues: rawSqlCommand.ParameterValues,
cancellationToken: cancellationToken);
}
}
}
}
Here's an example of how to use it:
// Execute a query.
using(var dr = await db.Database.ExecuteSqlQueryAsync("SELECT ID, Credits, LoginDate FROM SamplePlayer WHERE " +
"Name IN ('Electro', 'Nitro')"))
{
// Output rows.
var reader = dr.DbDataReader;
while (reader.Read())
{
Console.Write("{0}\t{1}\t{2} \n", reader[0], reader[1], reader[2]);
}
}
You can use this (from https://github.com/aspnet/EntityFrameworkCore/issues/1862#issuecomment-451671168 ) :
public static class SqlQueryExtensions
{
public static IList<T> SqlQuery<T>(this DbContext db, string sql, params object[] parameters) where T : class
{
using (var db2 = new ContextForQueryType<T>(db.Database.GetDbConnection()))
{
return db2.Query<T>().FromSql(sql, parameters).ToList();
}
}
private class ContextForQueryType<T> : DbContext where T : class
{
private readonly DbConnection connection;
public ContextForQueryType(DbConnection connection)
{
this.connection = connection;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
// switch on the connection type name to enable support multiple providers
// var name = con.GetType().Name;
optionsBuilder.UseSqlServer(connection, options => options.EnableRetryOnFailure());
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<T>().HasNoKey();
base.OnModelCreating(modelBuilder);
}
}
}
And the usage:
using (var db = new Db())
{
var results = db.SqlQuery<ArbitraryType>("select 1 id, 'joe' name");
//or with an anonymous type like this
var results2 = db.SqlQuery(() => new { id =1, name=""},"select 1 id, 'joe' name");
}
In Core 2.1 you can do something like this:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<Ranks>();
}
and then define you SQL Procedure, like:
public async Task<List<Ranks>> GetRanks(string value1, Nullable<decimal> value2)
{
SqlParameter value1Input = new SqlParameter("@Param1", value1?? (object)DBNull.Value);
SqlParameter value2Input = new SqlParameter("@Param2", value2?? (object)DBNull.Value);
List<Ranks> getRanks = await this.Query<Ranks>().FromSql("STORED_PROCEDURE @Param1, @Param2", value1Input, value2Input).ToListAsync();
return getRanks;
}
This way Ranks model will not be created in your DB.
Now in your controller/action you can call:
List<Ranks> gettingRanks = _DbContext.GetRanks(value1,value2).Result.ToListAsync();
This way you can call Raw SQL Procedures.
try this: (create extension method)
public static List<T> ExecuteQuery<T>(this dbContext db, string query) where T : class, new()
{
using (var command = db.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
db.Database.OpenConnection();
using (var reader = command.ExecuteReader())
{
var lst = new List<T>();
var lstColumns = new T().GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
while (reader.Read())
{
var newObject = new T();
for (var i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
PropertyInfo prop = lstColumns.FirstOrDefault(a => a.Name.ToLower().Equals(name.ToLower()));
if (prop == null)
{
continue;
}
var val = reader.IsDBNull(i) ? null : reader[i];
prop.SetValue(newObject, val, null);
}
lst.Add(newObject);
}
return lst;
}
}
}
Usage:
var db = new dbContext();
string query = @"select ID , Name from People where ... ";
var lst = db.ExecuteQuery<PeopleView>(query);
my model: (not in DbSet):
public class PeopleView
{
public int ID { get; set; }
public string Name { get; set; }
}
tested in
.netCore 2.2 and 3.0.
Note: this solution has the slow performance
Not directly targeting the OP's scenario, but since I have been struggling with this, I'd like to drop these ex. methods that make it easier to execute raw SQL with the DbContext:
public static class DbContextCommandExtensions
{
public static async Task<int> ExecuteNonQueryAsync(this DbContext context, string rawSql,
params object[] parameters)
{
var conn = context.Database.GetDbConnection();
using (var command = conn.CreateCommand())
{
command.CommandText = rawSql;
if (parameters != null)
foreach (var p in parameters)
command.Parameters.Add(p);
await conn.OpenAsync();
return await command.ExecuteNonQueryAsync();
}
}
public static async Task<T> ExecuteScalarAsync<T>(this DbContext context, string rawSql,
params object[] parameters)
{
var conn = context.Database.GetDbConnection();
using (var command = conn.CreateCommand())
{
command.CommandText = rawSql;
if (parameters != null)
foreach (var p in parameters)
command.Parameters.Add(p);
await conn.OpenAsync();
return (T)await command.ExecuteScalarAsync();
}
}
}
Actually you can create a generic repository and do something like this
public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : BaseEntity
{
private readonly DataContext context;
private readonly DbSet<TEntity> dbSet;
public GenericRepository(DataContext context)
{
this.context = context;
this.dbSet = context.Set<TEntity>();
}
public IEnumerable<TEntity> ExecuteCommandQuery(string command)
=> dbSet.FromSqlRaw(command);
}
I used Dapper to bypass this constraint of Entity framework Core.
IDbConnection.Query
is working with either sql query or stored procedure with multiple parameters. By the way it's a bit faster (see benchmark tests )
Dapper is easy to learn. It took 15 minutes to write and run stored procedure with parameters. Anyway you may use both EF and Dapper. Below is an example:
public class PodborsByParametersService
{
string _connectionString = null;
public PodborsByParametersService(string connStr)
{
this._connectionString = connStr;
}
public IList<TyreSearchResult> GetTyres(TyresPodborView pb,bool isPartner,string partnerId ,int pointId)
{
string sqltext "spGetTyresPartnerToClient";
var p = new DynamicParameters();
p.Add("@PartnerID", partnerId);
p.Add("@PartnerPointID", pointId);
using (IDbConnection db = new SqlConnection(_connectionString))
{
return db.Query<TyreSearchResult>(sqltext, p,null,true,null,CommandType.StoredProcedure).ToList();
}
}
}
My case used stored procedure instead of raw SQL
Created a class
Public class School
{
[Key]
public Guid SchoolId { get; set; }
public string Name { get; set; }
public string Branch { get; set; }
public int NumberOfStudents { get; set; }
}
Added below on my DbContext class
public DbSet<School> SP_Schools { get; set; }
To execute the stored procedure:
var MySchools = _db.SP_Schools.FromSqlRaw("GetSchools @schoolId, @page, @size ",
new SqlParameter("schoolId", schoolId),
new SqlParameter("page", page),
new SqlParameter("size", size)))
.IgnoreQueryFilters();
This solution leans heavily on the solution from @pius. I wanted to add the option to support query parameters to help mitigate SQL injection and I also wanted to make it an extension off of the DbContext DatabaseFacade for Entity Framework Core to make it a little more integrated.
First create a new class with the extension:
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
namespace EF.Extend
{
public static class ExecuteSqlExt
{
/// <summary>
/// Execute raw SQL query with query parameters
/// </summary>
/// <typeparam name="T">the return type</typeparam>
/// <param name="db">the database context database, usually _context.Database</param>
/// <param name="query">the query string</param>
/// <param name="map">the map to map the result to the object of type T</param>
/// <param name="queryParameters">the collection of query parameters, if any</param>
/// <returns></returns>
public static List<T> ExecuteSqlRawExt<T, P>(this DatabaseFacade db, string query, Func<DbDataReader, T> map, IEnumerable<P> queryParameters = null)
{
using (var command = db.GetDbConnection().CreateCommand())
{
if((queryParameters?.Any() ?? false))
command.Parameters.AddRange(queryParameters.ToArray());
command.CommandText = query;
command.CommandType = CommandType.Text;
db.OpenConnection();
using (var result = command.ExecuteReader())
{
var entities = new List<T>();
while (result.Read())
{
entities.Add(map(result));
}
return entities;
}
}
}
}
}
Note in the above that "T" is the type for the return and "P" is the type of your query parameters which will vary based on if you are using MySql, Sql, so on.
Next we will show an example. I'm using the MySql EF Core capability, so we'll see how we can use the generic extension above with this more specific MySql implementation:
//add your using statement for the extension at the top of your Controller
//with all your other using statements
using EF.Extend;
//then your your Controller looks something like this
namespace Car.Api.Controllers
{
//Define a quick Car class for the custom return type
//you would want to put this in it's own class file probably
public class Car
{
public string Make { get; set; }
public string Model { get; set; }
public string DisplayTitle { get; set; }
}
[ApiController]
public class CarController : ControllerBase
{
private readonly ILogger<CarController> _logger;
//this would be your Entity Framework Core context
private readonly CarContext _context;
public CarController(ILogger<CarController> logger, CarContext context)
{
_logger = logger;
_context = context;
}
//... more stuff here ...
/// <summary>
/// Get car example
/// </summary>
[HttpGet]
public IEnumerable<Car> Get()
{
//instantiate three query parameters to pass with the query
//note the MySqlParameter type is because I'm using MySql
MySqlParameter p1 = new MySqlParameter
{
ParameterName = "id1",
Value = "25"
};
MySqlParameter p2 = new MySqlParameter
{
ParameterName = "id2",
Value = "26"
};
MySqlParameter p3 = new MySqlParameter
{
ParameterName = "id3",
Value = "27"
};
//add the 3 query parameters to an IEnumerable compatible list object
List<MySqlParameter> queryParameters = new List<MySqlParameter>() { p1, p2, p3 };
//note the extension is now easily accessed off the _context.Database object
//also note for ExecuteSqlRawExt<Car, MySqlParameter>
//Car is my return type "T"
//MySqlParameter is the specific DbParameter type MySqlParameter type "P"
List<Car> result = _context.Database.ExecuteSqlRawExt<Car, MySqlParameter>(
"SELECT Car.Make, Car.Model, CONCAT_WS('', Car.Make, ' ', Car.Model) As DisplayTitle FROM Car WHERE Car.Id IN(@id1, @id2, @id3)",
x => new Car { Make = (string)x[0], Model = (string)x[1], DisplayTitle = (string)x[2] },
queryParameters);
return result;
}
}
}
The query would return rows like:
"Ford", "Explorer", "Ford Explorer"
"Tesla", "Model X", "Tesla Model X"
The display title is not defined as a database column, so it wouldn't be part of the EF Car model by default. I like this approach as one of many possible solutions. The other answers on this page reference other ways to address this issue with the [NotMapped] decorator, which depending on your use case could be the more appropriate approach.
Note the code in this example is obviously more verbose than it needs to be, but I thought it made the example clearer.
You can also use QueryFirst. Like Dapper, this is totally outside EF. Unlike Dapper (or EF), you don't need to maintain the POCO, you edit your sql SQL in a real environment, and it's continually revalidated against the DB. Disclaimer: I'm the author of QueryFirst.
I updated extension method from @AminRostami to return IAsyncEnumerable (so LINQ filtering can be applied) and it's mapping Model Column name of records returned from DB to models (Tested with EF Core 5):
Extension itself:
public static class QueryHelper
{
private static string GetColumnName(this MemberInfo info)
{
List<ColumnAttribute> list = info.GetCustomAttributes<ColumnAttribute>().ToList();
return list.Count > 0 ? list.Single().Name : info.Name;
}
/// <summary>
/// Executes raw query with parameters and maps returned values to column property names of Model provided.
/// Not all properties are required to be present in model (if not present - null)
/// </summary>
public static async IAsyncEnumerable<T> ExecuteQuery<T>(
[NotNull] this DbContext db,
[NotNull] string query,
[NotNull] params SqlParameter[] parameters)
where T : class, new()
{
await using DbCommand command = db.Database.GetDbConnection().CreateCommand();
command.CommandText = query;
command.CommandType = CommandType.Text;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
await db.Database.OpenConnectionAsync();
await using DbDataReader reader = await command.ExecuteReaderAsync();
List<PropertyInfo> lstColumns = new T().GetType()
.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
while (await reader.ReadAsync())
{
T newObject = new();
for (int i = 0; i < reader.FieldCount; i++)
{
string name = reader.GetName(i);
PropertyInfo prop = lstColumns.FirstOrDefault(a => a.GetColumnName().Equals(name));
if (prop == null)
{
continue;
}
object val = await reader.IsDBNullAsync(i) ? null : reader[i];
prop.SetValue(newObject, val, null);
}
yield return newObject;
}
}
}
Model used (note that Column names are different than actual property names):
public class School
{
[Key] [Column("SCHOOL_ID")] public int SchoolId { get; set; }
[Column("CLOSE_DATE", TypeName = "datetime")]
public DateTime? CloseDate { get; set; }
[Column("SCHOOL_ACTIVE")] public bool? SchoolActive { get; set; }
}
Actual usage:
public async Task<School> ActivateSchool(int schoolId)
{
// note that we're intentionally not returning "SCHOOL_ACTIVE" with select statement
// this might be because of certain IF condition where we return some other data
return await _context.ExecuteQuery<School>(
"UPDATE SCHOOL SET SCHOOL_ACTIVE = 1 WHERE SCHOOL_ID = @SchoolId; SELECT SCHOOL_ID, CLOSE_DATE FROM SCHOOL",
new SqlParameter("@SchoolId", schoolId)
).SingleAsync();
}
With Entity Framework 6 you can execute something like below
Create Modal Class as
Public class User
{
public int Id { get; set; }
public string fname { get; set; }
public string lname { get; set; }
public string username { get; set; }
}
Execute Raw DQL SQl command as below:
var userList = datacontext.Database.SqlQuery<User>(@"SELECT u.Id ,fname , lname ,username FROM dbo.Users").ToList<User>();
