Using DbContext in EF 4.1 Part 10: Raw SQL Queries

  • Article

 

The information in this post is out of date.

Visit msdn.com/data/ef for the latest information on current and past releases of EF.

For Raw SQL Queries see https://msdn.com/data/jj592907

 

Introduction

Version 4.1 of the Entity Framework contains both the Code First approach and the new DbContext API. This API provides a more productive surface for working with the Entity Framework and can be used with the Code First, Database First, and Model First approaches. This is the tenth post of a twelve part series containing collections of patterns and code fragments showing how features of the new API can be used.

The posts in this series do not contain complete walkthroughs. If you haven’t used EF 4.1 before then you should read Part 1 of this series and also Code First Walkthrough or Model and Database First with DbContext before tackling this post.

Writing SQL queries for entities

The SqlQuery method on DbSet allows a raw SQL query to be written that will return entity instances. The returned objects will be tracked by the context just as they would be if there were returned by a LINQ query. For example:

 using (var context = new UnicornsContext())
{
    var unicorns = context.Unicorns.SqlQuery(
                   "select * from Unicorns").ToList();
}

Note that, just as for LINQ queries, the query is not executed until the results are enumerated—in the example above this is done with the call to ToList.

Care should be taken whenever raw SQL queries are written for two reasons. First, the query should be written to ensure that it only returns entities that are really of the requested type. For example, when using features such as inheritance it is easy to write a query that will create entities that are of the wrong CLR type.

Second, some types of raw SQL query expose potential security risks, especially around SQL injection attacks. Make sure that you use parameters in your query in the correct way to guard against such attacks.

Writing SQL queries for non-entity types

A SQL query returning instances of any type, including primitive types, can be created using the SqlQuery method on the Database class. For example:

 using (var context = new UnicornsContext())
{
    var unicornNames = context.Database.SqlQuery<string>(
                       "select Name from Unicorns").ToList();
}

The results returned from SqlQuery on Database will never be tracked by the context even if the objects are instances of an entity type.

Sending raw commands to the database

Non-query commands can be sent to the database using the ExecuteSqlCommand method on Database. For example:

 using (var context = new UnicornsContext())
{
    context.Database.ExecuteSqlCommand(
        "update Unicorns set Name = 'Franky' where Name = 'Beepy'"); 
}

ExecuteSqlCommand is sometimes used in a database initializer to perform additional configuration of the database (such as setting indexes) after it has been created by Code First.

Note that any changes made to data in the database using ExecuteSqlCommand are opaque to the context until entities are loaded or reloaded from the database.

Summary

In this part of the series we looked at ways in which entities and other types can be queried from the database using raw SQL, and how raw non-query commands can be executed on the database.

As always we would love to hear any feedback you have by commenting on this blog post.

For support please use the Entity Framework Forum.

Arthur Vickers

Developer

ADO.NET Entity Framework

Comments

  • Anonymous
    February 04, 2011
    What about Entity-SQL, is it possible to use?

  • Anonymous
    February 06, 2011
    The comment has been removed

  • Anonymous
    February 07, 2011
    @rjperes You can drop down to ObjectContext (use ((IObjectContextAdapter)context).ObjectContext on your DbContext instance) and use CreateQuery to create an eSQL query. There is nothing built into the DbContext API directly for eSQL--it sems that eSQL it is not frequently used and confuses the LINQ experience. That being said it would be interesting to know how you use it since it is possible we could add ESqlQuery methods or similar in the future. @Andries I'm not an expert on stored procs and EF, but I don't believe this is currently possible with EF.

  • Anonymous
    February 07, 2011
    Hi, Arthur! Thanks for your reply! I had already found that out. However, if I don't explicitly set the DefaultContainerName property, it throws an exception! Is this expected behavior? Thanks! ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext; octx.DefaultContainerName = "BlogEntities"; ObjectQuery<Post> q = octx.CreateQuery<Post>("SELECT VALUE p FROM Post AS p"); List<Post> posts = q.ToList();

  • Anonymous
    February 08, 2011
    The comment has been removed

  • Anonymous
    February 17, 2011
    The comment has been removed

  • Anonymous
    April 04, 2011
    Is it possible to load related entities using RAW SQL queries? Will relationship be fixed automatically?

  • Anonymous
    May 02, 2011
    Do i need to use the syntax of the chosen database when i use raw sqlcommands

  • Anonymous
    January 24, 2012
    @Nazaf: I don’t think this is currently possible because the materializer doesn’t have the metadata necessary to know how to deal with the related entities. @RCaspers: Yes, the query is not translated in any way. Look into Entity SQL if you need a text-based query language that will operate against your model and be translated into the store query language by EF. Thanks, Arthur

  • Anonymous
    March 26, 2012
    OK, I am stumped. The Documentation for SqlQuery() shows that there is an argument list for the query string of type params, but it doesn't say what those objects are. I was hoping for SqlParameter, but that doesn't seem to work. Any hints on how to do this? thanks, Pete Mack

  • Anonymous
    March 27, 2012
    @Pete Mack - You can pass in SqlParameter if you want, or you can just pass in the raw values (i.e. int, string, etc.) and they will automatically get wrapped up in SqlParameters.

  • Anonymous
    July 05, 2012
    Hi is it possible to return a dataset for queries for non entity types - in your example you used string but lets say I do a query like select * from tblPerson where PersonID = @p0, which element type should I choose to load the data into a viewbag? thx for interesting post

  • Anonymous
    July 06, 2012
    @Fred The types doesn't have to be an entity type but it must have properties with names that match the names of the columns returned by the query. Putting the results directly into a bag is not supported by this API. You can drop down to lower-level APIs, get a data reader, and use that to put the results into a bag.

  • Anonymous
    September 12, 2012
    The comment has been removed

  • Anonymous
    September 14, 2012
    @SNSRKrishna - It means that when you iterate over the results it will always query the database to get the results, even if they have already been retrieved by a previous iteration. For example this code would result in two queries to the database (one for each of the foreach loops). var allBlogs = context.Blogs.SqlQuery("SELECT * FROM Blogs"); foreach(var blog in allBlogs) {    ... } foreach(var blog in allBlogs) {    ... }

  • Anonymous
    November 20, 2012
    Ok, and what about "GROUP BY", something like: var result = context.Database.SqlQuery<WHAT>("select * from Table1 group by Table1.Col1"); Is it possible to do that?

  • Anonymous
    November 21, 2012
    @xeonix_nya - You would need to define a class whose properties match the expected result set. The class doesn't have to be part of your model, EF will just match the column names in the result set with the property names on your class.