LeCintas LeCintas - 1 year ago 96
ASP.NET (C#) Question

Convert DbContext way to SqlCommand way

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)

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

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 (
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
. (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.


Answer Source

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++)

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download