How to Run Native SQL with Entity Framework

Is it possible to run native sql with entity framework?

For .NET Framework version 4 and above: use ObjectContext.ExecuteStoreCommand() if your query returns no results, and use ObjectContext.ExecuteStoreQuery if your query returns results.

For previous .NET Framework versions, here's a sample illustrating what to do. Replace ExecuteNonQuery() as needed if your query returns results.

static void ExecuteSql(ObjectContext c, string sql)
{
    var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;
    DbConnection conn = entityConnection.StoreConnection;
    ConnectionState initialState = conn.State;
    try
    {
        if (initialState != ConnectionState.Open)
            conn.Open();  // open connection if not already open
        using (DbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }
    }
    finally
    {
        if (initialState != ConnectionState.Open)
            conn.Close(); // only close connection if not initially open
    }
}

How to execute raw SQL query using Entity Framework without having to use a model?

Specify string as the type argument.

var results = db1New.Database.SqlQuery<string>("SELECT LOWER(column_name) AS column_name FROM information_schema.columns WHERE table_name = @p0", tableName).ToArray();
                                       ^^^^^^

How to execute raw sql query with in entity framework?

You can execute the following types of queries:

1. SQL query for entity types which returns particular types of entities.

   using (var ctx = new SchoolDBEntities())
    {
   
        var studentList = ctx.Students.SqlQuery("Select * from Student").ToList<Student>();
   
   }
   

2. SQL query for non-entity types which returns a primitive data type.

   using (var ctx = new  SchoolDBEntities())
   {
   
   var studentName = ctx.Students.SqlQuery("Select studentid, studentname 
       from Student where studentname='New Student1'").ToList();
   }
   
   
    //Error
    using (var ctx = new SchoolDBEntities())
    {                
        //this will throw an exception
        var studentName = ctx.Students.SqlQuery("Select studentid as id, studentname as name 
           from Student where studentname='New Student1'").ToList();
    }
   
    //SQL query for non-entity types:
     using (var ctx = new SchoolDBEntities())
     {
          //Get student name of string type
         string studentName = ctx.Database.SqlQuery<string>("Select studentname 
       from Student where studentid=1").FirstOrDefault<string>();
     }
   

3. Raw SQL commands to the database.

         using (var ctx = new SchoolDBEntities())
         {
   
              //Update command
              int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student 
           set studentname ='changed student by command' where studentid=1");
             //Insert command
            int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) 
           values('New Student')");
            //Delete command
            int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student 
           where studentid=1");
   
          }
   

You can also refer this

---

Related Topics