Using DbContext in EF 4.1 Part 10: Raw SQL Queries

 


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