Convert DbContext way to SqlCommand way

ef core executesqlcommand
ef core executesqlcommand return value
context.database.executesqlcommand stored procedure
executesqlrawasync
entity framework executesqlcommand
executesqlcommandasync
the required column was not present in the results of a 'fromsql' operation
ef core stored procedure

Today I have an App which work using EF Core, with the DbContext class and looks like this:

    public class RcoreContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=rCoreData.TestDb;Trusted_Connection=True;");
        }

        public DbSet<Hosts> Hosts { get; set; }
        public DbSet<Route> Route { get; set; }
    }

    public class Hosts
    {
        public int      HostsId  { get; set; }
        public string   Host     { get; set; }

        [NotMapped]
        public Dictionary<object, object> Datas { get; set; }

        public string   DatasJson { get { return JsonConvert.SerializeObject(Datas); } set { Datas = JsonConvert.DeserializeObject<Dictionary<object, object>>(value); } }
    }

    public class Route
    {
        public int                          RouteId     { get; set; }
        public int                          HostId      { get; set; }
        public string                       RealRoute   { get; set; }
        public string                       alias       { get; set; }
    }

I very like this way because the requests returns are based on existing classes (Hosts and Route in this code).

The SqlCommand way seems more comfortable to use (it seems to be more PHP-like) but seems to return the entries as object. (I read this article)

I would like to know if is it possible to make a mix between the 2 ways and to send a request as a StoredProcedure like SqlCommand do BUT still be based on existing classes for the request return.

Thanks

if you want to use ADO.NET in EF, you just need to get the current connection string and create the sqlcommand as below:

using (var context = new RcoreContext())
{
   // creates a Command 
   var command = context.Database.Connection.CreateCommand();
   command.CommandType = commandType;
   command.CommandText = sql;

   using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
        for (int i = 0; i < reader.FieldCount; i++)
        {
            Console.WriteLine(reader.GetValue(i));
        }
        Console.WriteLine();
        }
    }
}

and if you just want to execute sql or stored procedure, you can also use below in EF

 _dbContext.Database.ExecuteSqlCommand(sql, parameters);
_dbContext.Database.SqlQuery<TEntity>(sql, parameters);

Convert DbContext way to SqlCommand way - asp.net, The SqlCommand way seems more comfortable to use (it seems to be more PHP​-like) but seems to return the entries as object. (I read this article) I would like to  At the time of writing (EF Core 2.1), there is no way to execute arbitrary secuence returning SQL command.. Only entity types and Query Types are supported via FromSql.. So the closest solution is to define query type (a class holding the query result) and use FromSql, but it's not generic - the query types must be registered in the DbContext via fluent API and the method should receive a

    using (var connection = context.Database.GetDbConnection())
    using (var command = context.Database.GetDbConnection().CreateCommand())
    { 
       command.CommandText = string.Format(sqlQuery, id);
       connection.Open();
       using (var reader = command.ExecuteReader()){}
    }

Executing Raw SQL Queries using Entity Framework Core, Entity Framework 6.x allows you to execute the raw SQL query for the underlying ExecuteSqlCommand(). DbSet.SqlQuery(). Use the DbSet.SqlQuery() method to write raw SQL queries which return entity instances. from Students SQL in the database to get all students and will be converted into a list of Student entities​. Simple iteration through materialized result of the query should be what you need - that is ORM way. If you don't like it use SqlCommand directly. DbContext API is simplified and because of that it doesn't contain many features available in ObjectContext API. Accessing data reader is one of them. You can try to convert DbContext to

using (var connection = new SqlConnection(_dbContext.Database.Connection.ConnectionString))
using (var cmd = new SqlCommand())
{
      cmd.Connection = connection;
      cmd.CommandText = "your query here";
      cmd.CommandType = CommandType.Text;

      cmd.Parameters.Add("@param1", SqlDbType.Int);

      cmd.Connection.Open();
      cmd.ExecuteNonQuery();
}

Execute Raw SQL Query in Entity Framework 6, Learn how to execute queries in Entity Framework and get the data from the EF 6 supports different types of queries which in turn convert into SQL queries for You can use the LINQ method syntax or query syntax when querying with EDM. The way I designed this is so that I can better unit test my Controller code by injecting a real or mock DbContext. What I'm currently planning to do is using Unity and the IDbEntities interface to inject the DbContext into the controller.

Querying in Entity Framework, Any parameter values you supply will automatically be converted to a DbParameter. context.Database.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating  I want Code to be compact As this DataTable dt=DML.Gettable(Query);//This is static Function That returns DataTable But If i try this in entity framework using linq All one method i know is first M

Database.ExecuteSqlCommand Method (System.Data.Entity , ExecuteSqlCommand() by default will wrap the command in a transaction if There are overloads of this method that allow you to override this behavior if you wish. Prior to EF6 Entity Framework insisted on opening the database this may result in long-running transactions – which can in turn cause  This article shows basic patterns for configuring a DbContext via a DbContextOptions to connect to a database using a specific EF Core provider and optional behaviors. EF Core design-time tools such as migrations need to be able to discover and create a working instance of a DbContext type in order to gather details about the application's

Working with Transactions, SqlCommand is the alternative way to query, insert, update or delete to execute CRUD operations on a database using the Entity Framework and the new AlbumId generated we added the “select CAST(scope_identity()  This is a very basic operation in EF. I highly suggest you pick up a book on EF and work through it. We can't tell you exactly what the code is going to be because we know NOTHING about your EF database setup, like your DbContext name.

Comments
  • Hello, _dbContext.Database.SqlQuery<TEntity>(sql, parameters); seems to be what I look for but I use ASP.NET Core 1.0 (so EF Core) and .SqlQuery() does not exist. Instead of it, I can use .FromSql(), is it possible to use stored procedure with it ? Thanks
  • I think you can take a look this
  • Thank you, this is exactly what i want