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?
Add a comment
|
4 Answers
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();
}
}
1 Comment
Carlo V. Dango
I would advice the use of ExecuteStoreCommand() in the ObjectContext instead of the above ExecuteSql()
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
Comments
ExecuteStoreQuery<> and ExecuteStoreCommand is what you want:
using (NorthWindEntities ctx = new NorthWindEntities())
{
ctx.ExecuteStoreQuery<>()
ctx.ExecuteStoreCommand();
}
Comments
@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.
Comments
