Using Raw SQL with Entity Framework

When using Entity Framework with Code First, LINQ is generally used to have the DBContext relate LINQ queries to objects. But sometimes you need to execute raw SQL queries directly—here's how to do it effectively.

The Standard Approach: LINQ

For example, if I want a specific user from my application, I write something like:

using (SecurityEngineDb LAPDb = new SecurityEngineDb())
{
    var user = LAPDb.users.Single(p => p.username == userName);
    return user;
}

Entity Framework then transforms this into the SQL instruction:

SELECT [Fields] FROM Users WHERE UserName = @UserName

When You Need Raw SQL

There are some SQL instructions that, due to their complexity, optimization requirements, or other reasons, cannot be written using LINQ. Fortunately, the context includes the ability to write SQL instructions directly using DBContext.Database.SqlQuery.

Using SqlQuery with Model Entities

The SqlQuery method is generic. If Entity Framework recognizes that the result object is a User or any other model entity, simply specify it:

var users = context.Database
    .SqlQuery<Usuario>(sqlInstruction, parameters);

Voilà! You have users that are the result of your complex SQL instruction.

Handling Custom Result Sets

But what if the SQL instruction returns a set of columns that don't correspond to any of your model classes? (This is very common in reports, for example.)

The solution: Declare an independent class outside the model and use it as the parameter in SqlQuery.

public class FullfilledTransition
{
    public int Requested { get; set; }
    public int Fullfilled { get; set; }
    public Guid Transition_id { get; set; }
    public Guid idSourceActivity { get; set; }
    public string nameSourceActivity { get; set; }
    public string nameTargetActivity { get; set; }
}

// Usage
var results = context.Database
    .SqlQuery<FullfilledTransition>(complexSqlQuery, parameters)
    .ToList();

Key Takeaways