Running sql in entity structure?

Question

Is it possible to run an SQL query directly from generated entity calls? Or do I need to create a procedure, then call it through the entity infrastructure?

Answer 1

The other day I was looking for this game, here is an example that I found, hope this 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 quite simple, because there ObjectContextare new methods that allow you to directly execute storage commands (i.e. SQL):

See this: ExecuteStoreCommand

If you are still using EF 3.5 SP1, you can still query directly the database if you really want to do this:

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

(dbconn) , ADO.NET ..

,

Answer 3

ExecuteStoreQuery<>and ExecuteStoreCommand- this is what you want:

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

Answer 4

@ Alex James, out of curiosity, will it be effective to run the full text of sql-bit code, since it should not have performance overheads? Say by running the same full sql text code just like a query in sql management studio.