Execute Raw SQL in 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.

6 thoughts on “Execute Raw SQL in Entity Framework Core”

    1. Jon,

      As far as I know the issue you were having would still be a problem. EF is not going to know that it should update its in memory data based on some raw SQL that was executed. When using the FromSql function should be fine, but not the ADO level functions.

      I am also not seeing a reload function like the one you used in your SO answer.

  1. Pingback: .NET Blog

  2. .Net Core 3.1 keeps giving me this error –
    The required column ‘id’ was not present in the results of a ‘FromSql’ operation.

    I tried using your ADO.Net technique and it worked the first time like a charm.
    So I may jut stick with this.
    Thank You!

Leave a Reply to [email protected] Cancel Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.