Entity Framework Core

From time to time your ORM is going to let you down in some way. No matter how good an ORM is there will always be some situations that the queries produced will not meet a performance requirement. Entity Framework Core will not be any different. This post is going to cover a couple of ways to execute raw SQL if the need arises.

Table Definition

The examples in this post are dealing with a Contacts table that contains columns for Id, Name, Address, City, State and Zip code. The following Entity Framework Core DbContext is defined for access to the Contacts table and is accessed via _context in all of the examples.

public sealed class ContactsDbContext : DbContext
{
    private static bool _created;
    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext()
    {
        if (_created) return;
        Database.Migrate();
        _created = true;
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Contact>().HasKey(c => c.Id);
    }
}

DbSet FromSql

From any DbSet there is a FromSql function that will take raw SQL or a stored procedure that will be used instead of the SQL Entity Framework would have generated. The following example is using FromSql to load contacts based on the above DbContext and DbSet.

var results = _context.Contacts.FromSql("SELECT Id, Name Address, City, State, Zip " +
                                        "FROM Contacts " +
                                        "WHERE Name IN (@p0, @p1)",
                                        name1, name2);

FromSql takes a query followed by a list of parameters that should be used in the query’s execution. For example in the above @p0 maps to name1 and @p1 maps to name2. I recommend always using parameters instead of manually adding the filters to the SQL string in order to help prevent SQL injection attacks. Note that the @p followed by increasing numbers seems to be the only parameters that FromSql works with.

The great thing about this method is just like querying with via Entity Framework with LINQ execution is delayed and it returns an IQueryable so more filters can be added on as needed.

ADO.NET

Another options it to grab a connection from the DbContext using Database.GetDbConnection() and use ADO.NET to perform what ever operations are needed. Here is an example of connecting to the database and getting the name of the first contact. This is not a good example of a query that would need to be run this way, but provides a simple example of how a query would be executed.

using (var connection = _context.Database.GetDbConnection())
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(*) FROM Contacts";
        var result = command.ExecuteScalar().ToString();
    }
}

Since this is down to ADO.NET you can do pretty much anything that is needed including readers, adapters, data tables, etc. Hopefully the need for this option will be minimal, but it is important to know that it exists.