Running sql in entity framework?

Question

Is there any way to run a sql statement straight from the entity framework generated calls? Or will I have to create a procedure then call that via the entity framework?

Answer 1

Was Googling around for this myself the other day, this is the example I found hope it helps

  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();  
            using (DbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }
        finally
        {
            if (initialState != ConnectionState.Open)
                conn.Close(); 
        }
    }

Answer 2

In EF 4.0 this is pretty easy because there are new methods on the ObjectContext that allow you to execute store commands (i.e. SQL) directly:

See this: ExecuteStoreCommand

If you are still using EF 3.5 SP1 you can still execute a query directly against the database if you really want to like this:

var econn = ctx.Connection as EntityConnection;
var dbconn = econn.StoreConnection;

at this point you have access to a connection (dbconn) to the underlying database, so you can use normal ADO.NET code to execute queries etc.

Hope this helps

Alex

Answer 3

ExecuteStoreQuery<> and ExecuteStoreCommand is what you want:

using (NorthWindEntities ctx = new NorthWindEntities())
{
    ctx.ExecuteStoreQuery<>()
    ctx.ExecuteStoreCommand();
}