CODEWITHSUNDEEP
entity-framework
David
David

Reputation: 2243

Running sql in entity framework?

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?

Upvotes: 5

Views: 7234

Answers (4)

Teddy
Teddy

Reputation: 1417

ExecuteStoreQuery<> and ExecuteStoreCommand is what you want:

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

Upvotes: 2

Gavin
Gavin

Reputation: 17402

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

Upvotes: 10

Andy
Andy

Reputation: 1

@Alex James, out of curiosity, would this be efficient to run a full text sql bit of code, as in there should be no performance overhead right? To say, running the same full text sql code straight as a query in sql management studio.

Upvotes: 0

Alex James
Alex James

Reputation: 20924

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

Upvotes: 5

Related Questions