EF Feature CTP5: Raw SQL Query/Command Support

December 8, 2010

11 comments

EF Feature CTP5: Raw SQL Query/Command Support

One of the new features that EF feature CTP5 supplies is the support for raw EF Feature CTP5: Raw SQL Query/Command SupportSQL Query/Command through the DbContext. In this post I’m going to show you examples for how you can use this feature. Pay attention that the details I provide might change in the future since its only a CTP and not a release.

DbContext Raw SQL Query/Command Support

EF feature CTP5 supports the execution of raw SQL queries and commands through the DbContext. This behavior resembles the ObjectContext’s same functionality that is exposed by the ExecuteStoreQuery<T> and ExecuteStoreCommand methods. You can use the DbContext‘s SqlQuery and SqlCommand methods which are exposed by the DbContext.Database property. The results of the method executions can be materialized into entities that can be tracked by the DbContext. Here is an example of how to use these methods:

static void Main(string[] args)
{
  using (SchoolEntities context = new SchoolEntities())
  {
    IEnumerable sqlQuery = context.Database.SqlQuery<Department>("select * from department");
    foreach (Department item in sqlQuery)
    {
      Console.WriteLine(item.Name);
    }
 
    sqlQuery = context.Database.SqlQuery<Course>("GetCoursesOrderByTitle");
    foreach (Course item in sqlQuery)
    {
      Console.WriteLine(item.Title);
    }
 
    var numberOfAffectedRows = context.Database.SqlCommand("DeleteDepartment @DepartmentID", new SqlParameter("DepartmentID", 9));
    Console.WriteLine("Number of affected rows: {0}", numberOfAffectedRows);
  }
}

In the program, that is using the same model from my previous example, I’m executing two queries (one is raw Sql and the other is using a stored procedure) and one command. The result of the execution:

Running Result

Summary

Lets sum up, the new CTP enables the running of raw Sql queries/commands. This can be very useful in scenarios that aren’t enabled by EF or advanced scenarios.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

11 comments

  1. James WhiteDecember 8, 2010 ב 19:24

    Do you think it would be a good idea to clear or fresh the local cache after executing a procedure that modifies a table?

    Reply
  2. Bret Ferrier @runxc1December 9, 2010 ב 20:28

    Maybe its the purist in me but I would much rather have seen

    IEnumerable sqlQuery = context.Database.ExecSqlQuery(“select * from department”);

    as opposed to

    IEnumerable sqlQuery = context.Database.SqlQuery(typeof(Department), “select * from department”);

    Reply
  3. Gil FinkDecember 10, 2010 ב 13:47

    @James White,

    It depends. If you dispose the context after the operation or you don’t need to perform other changes on the affected DbSet/EntitySet then it is unnecessary to refresh the local cache. If after the operation you need to do other changes to the same DbSet/EntitySet then you should refresh the local cache. DbContext has no way to refresh the context (as opposed to the ObjectContext) so you’ll have to retieve all the entities again if you want to refresh the local cache.

    Reply
  4. SiavashDecember 17, 2010 ב 9:37

    Thanks for the post Gil,

    Will this work with EF Code First in CTP5? Or will it only work with VS generated context from the DB??
    This looks very good but I don’t think I noticed it in any of Scott’s recent blog posts about EF Code First CTP5.

    Thanks again!

    Reply
  5. Gil FinkDecember 17, 2010 ב 13:48

    @Siavash,
    As I wrote, the ObjectContext has two methods – ExecuteStoreQuery and ExecuteStoreCommand while the DbContext exposes SqlQuery and SqlCommand methods.
    EF Feature CTP5 includes the SqlQuery and SqlCommand methods which can be used as I wrote in the post.

    Reply
  6. BehnamDecember 17, 2010 ב 21:13

    Great post. I’ve been playing with this for a bit and have got a couple of simple SELECT queries to work but this plain DELETE query doesn’t seem to work. Do you see anything wrong with this??

    resDB.Database.SqlQuery(typeof(int), “DELETE FROM MESSSAGE WHERE MESSAGEID=@MessageID”, new SqlParameter(“MessageID”, 3686));

    Thank you.

    Reply
  7. BobDecember 18, 2010 ב 1:38

    Why can’t I have or for the type for doing quick queries that bring back fields from a few tables but I don’t have predefined entities for them like this??

    myDB.Database.SqlQuery(“select top 10 c.Name, c.Address, i.Url from Contacts c join Info i on c.UserID= i.UserID”);

    There’re plenty of cases where this would be very helpful. We don’t need to get whole entities back…just a couple of fields.

    Reply
  8. Gil FinkDecember 18, 2010 ב 9:24

    @Behnam,

    Delete is a command and not a query so you should use the SqlCommand method instead of the SqlQuery method.

    Reply
  9. Gil FinkDecember 19, 2010 ב 10:19

    @Bob,
    This kind of behavior can’t be used with EF. EF is returning entities or complex types. If you want to define a query like in your example you need to create a complex type which will hold the returning fields and use it as the return type of the query.

    Reply
  10. JeremyJanuary 13, 2011 ב 0:04

    When executing a stored proc with CTP5 is there anyway to return output parameters from the stored proc?

    Reply
  11. Matt PennerFebruary 18, 2011 ב 12:07

    As Bob commented I would also vote for so we could do things like:
    myDB.Database.SqlQuery(“some dynamic query”)
    or myDB.Database.SqlQuery
    (“some dynamic query”)

    While I can use myDB.Database.SqlQuery it doesn’t populate the object with any properties. It’s as if SqlQuery looks for the property first and then fills it if there is one, otherwise it ignores it. I created a custom DynamicObject class and passed it to SqlQuery and the TrySetMember method was never called.

    This would be a great feature.

    Reply