Raw SQL Query without DbSet - Entity Framework Core

Question

With Entity Framework Core removing dbData Database SqlQuery lt SomeModel gt  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

User · Answer

In Core 2 1 you can do something like this   protected override void OnModelCreating ModelBuilder modelBuilder           modelBuilder Query lt Ranks gt         and then define you SQL Procedure  like   public async Task lt List lt Ranks gt  gt  GetRanks string value1  Nullable lt decimal gt  value2        SqlParameter value1Input   new SqlParameter   Param1   value1    object DBNull Value       SqlParameter value2Input   new SqlParameter   Param2   value2    object DBNull Value        List lt Ranks gt  getRanks   await this Query lt Ranks gt    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 lt Ranks gt  gettingRanks    DbContext GetRanks value1 value2  Result ToListAsync      This way you can call Raw SQL Procedures

User · Answer

Building on the other answers I ve written this helper that accomplishes the task  including example usage   public static class Helper       public static List lt T gt  RawSqlQuery lt T gt  string query  Func lt DbDataReader  T gt  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 lt T gt                          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   gt  new TopUser   Name    string x 0   Count    int x 1       result ForEach x   gt  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

User · Answer

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 lt ColumnAttribute gt  list   info GetCustomAttributes lt ColumnAttribute gt    ToList            return list Count  gt  0   list Single   Name   info Name                 lt summary gt          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           lt  summary gt      public static async IAsyncEnumerable lt T gt  ExecuteQuery lt T gt            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 lt PropertyInfo gt  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  lt  reader FieldCount  i                                  string name   reader GetName i                   PropertyInfo prop   lstColumns FirstOrDefault a   gt  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  quot SCHOOL ID quot    public int SchoolId   get  set          Column  quot CLOSE DATE quot   TypeName    quot datetime quot        public DateTime  CloseDate   get  set          Column  quot SCHOOL ACTIVE quot    public bool  SchoolActive   get  set       Actual usage  public async Task lt School gt  ActivateSchool int schoolId           note that we re intentionally not returning  quot SCHOOL ACTIVE quot  with select statement        this might be because of certain IF condition where we return some other data     return await  context ExecuteQuery lt School gt            quot UPDATE SCHOOL SET SCHOOL ACTIVE   1 WHERE SCHOOL ID    SchoolId  SELECT SCHOOL ID  CLOSE DATE FROM SCHOOL quot           new SqlParameter  quot  SchoolId quot   schoolId        SingleAsync

User · Answer

Add Nuget package - Microsoft EntityFrameworkCore Relational  using Microsoft EntityFrameworkCore      await YourContext Database ExecuteSqlCommandAsync       p0   p1   param1  param2       This will return the row numbers as an int  See - https   docs microsoft com en-us dotnet api microsoft entityframeworkcore relationaldatabasefacadeextensions executesqlcommand view efcore-3 0

User · Answer

I know it s an old question  but maybe it helps someone to call stored procedures without adding DTOs as DbSets   https   stackoverflow com a 62058345 3300944

User · Answer

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     gt   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   Executing Raw SQL Queries using Entity Framework Core  Raw SQL Queries

User · Answer

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 lt School gt  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

User · Answer

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 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 lt T gt  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 lt SomeModel gt  SomeModels   get  set     Secondly use FromSql method like you do with DbSet lt T gt   var result   context SomeModels FromSql  quot SQL SCRIPT quot   ToList    var result   await context SomeModels FromSql  quot SQL SCRIPT quot   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   If you re using EF Core 3 0 and higher versions 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 lt SomeModel gt  SomeModels   get  set     protected override void OnModelCreating ModelBuilder modelBuilder        modelBuilder Entity lt SomeModel gt    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  quot SQL SCRIPT quot   ToList

User · Answer

try this   create extension method   public static List lt T gt  ExecuteQuery lt T gt  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 lt T gt                         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  lt  reader FieldCount  i                                                          var name   reader GetName i                               PropertyInfo prop   lstColumns FirstOrDefault a   gt  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 lt PeopleView gt  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

User · Answer

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                    lt summary gt              Execute raw SQL query with query parameters              lt  summary gt               lt typeparam name  quot T quot  gt the return type lt  typeparam gt               lt param name  quot db quot  gt the database context database  usually  context Database lt  param gt               lt param name  quot query quot  gt the query string lt  param gt               lt param name  quot map quot  gt the map to map the result to the object of type T lt  param gt               lt param name  quot queryParameters quot  gt the collection of query parameters  if any lt  param gt               lt returns gt  lt  returns gt          public static List lt T gt  ExecuteSqlRawExt lt T  P gt  this DatabaseFacade db  string query  Func lt DbDataReader  T gt  map  IEnumerable lt P gt  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 lt T gt                          while  result Read                                                  entities Add map result                                               return entities                                                                       Note in the above that  quot T quot  is the type for the return and  quot P quot  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 lt CarController gt   logger            this would be your Entity Framework Core context         private readonly CarContext  context           public CarController ILogger lt CarController gt  logger  CarContext context                         logger   logger               context   context                           more stuff here                  lt summary gt             Get car example             lt  summary gt          HttpGet         public IEnumerable lt Car gt  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    quot id1 quot                  Value    quot 25 quot                            MySqlParameter p2   new MySqlParameter                             ParameterName    quot id2 quot                  Value    quot 26 quot                            MySqlParameter p3   new MySqlParameter                             ParameterName    quot id3 quot                  Value    quot 27 quot                              add the 3 query parameters to an IEnumerable compatible list object            List lt MySqlParameter gt  queryParameters   new List lt MySqlParameter gt      p1  p2  p3                  note the extension is now easily accessed off the  context Database object              also note for ExecuteSqlRawExt lt Car  MySqlParameter gt               Car is my return type  quot T quot               MySqlParameter is the specific DbParameter type MySqlParameter type  quot P quot             List lt Car gt  result    context Database ExecuteSqlRawExt lt Car  MySqlParameter gt            quot SELECT Car Make  Car Model  CONCAT WS     Car Make       Car Model  As DisplayTitle FROM Car WHERE Car Id IN  id1   id2   id3  quot           x   gt  new Car   Make    string x 0   Model    string x 1   DisplayTitle    string x 2              queryParameters               return result                    The query would return rows like    quot Ford quot    quot Explorer quot    quot Ford Explorer quot    quot Tesla quot    quot Model X quot    quot Tesla Model X quot  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

User · Answer

Actually you can create a generic repository and do something like this public class GenericRepository lt TEntity gt    IGenericRepository lt TEntity gt  where TEntity   BaseEntity       private readonly DataContext context      private readonly DbSet lt TEntity gt  dbSet       public GenericRepository DataContext context                this context   context          this dbSet   context Set lt TEntity gt                    public IEnumerable lt TEntity gt  ExecuteCommandQuery string command            gt  dbSet FromSqlRaw command

User · Answer

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 lt User gt    SELECT u Id  fname   lname  username FROM dbo Users   ToList lt User gt

User · Answer

You can use this  from https   github com aspnet EntityFrameworkCore issues 1862 issuecomment-451671168     public static class SqlQueryExtensions       public static IList lt T gt  SqlQuery lt T gt  this DbContext db  string sql  params object   parameters  where T   class               using  var db2   new ContextForQueryType lt T gt  db Database GetDbConnection                           return db2 Query lt T gt    FromSql sql  parameters  ToList                         private class ContextForQueryType lt T gt    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   gt  options EnableRetryOnFailure                  base OnConfiguring optionsBuilder                      protected override void OnModelCreating ModelBuilder modelBuilder                        modelBuilder Entity lt T gt    HasNoKey                base OnModelCreating modelBuilder                      And the usage      using  var db   new Db                  var results   db SqlQuery lt ArbitraryType gt   quot select 1 id   joe  name quot              or with an anonymous type like this         var results2   db SqlQuery      gt  new   id  1  name  quot  quot    quot select 1 id   joe  name quot

User · Answer

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 lt SomeModel gt    HasNoKey     More information can be found here

User · Answer

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

User · Answer

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 lt int gt  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 lt T gt  ExecuteScalarAsync lt T gt  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

User · Answer

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 lt IConcurrencyDetector gt                  using  concurrencyDetector EnterCriticalSection                                  var rawSqlCommand   databaseFacade                      GetService lt IRawSqlCommandBuilder gt                         Build sql  parameters                    return rawSqlCommand                      RelationalCommand                      ExecuteReader                          databaseFacade GetService lt IRelationalConnection gt                             parameterValues  rawSqlCommand ParameterValues                                    public static async Task lt RelationalDataReader gt  ExecuteSqlQueryAsync this DatabaseFacade databaseFacade                                                                string sql                                                                CancellationToken cancellationToken   default CancellationToken                                                                params object   parameters                         var concurrencyDetector   databaseFacade GetService lt IConcurrencyDetector gt                  using  concurrencyDetector EnterCriticalSection                                  var rawSqlCommand   databaseFacade                      GetService lt IRawSqlCommandBuilder gt                         Build sql  parameters                    return await rawSqlCommand                      RelationalCommand                      ExecuteReaderAsync                          databaseFacade GetService lt IRelationalConnection gt                             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

User · Answer

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 lt TyreSearchResult gt  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 lt TyreSearchResult gt  sqltext  p null true null CommandType StoredProcedure  ToList

[c#] Raw SQL Query without DbSet - Entity Framework Core

Examples related to c#

Examples related to entity-framework-core