SqlQuery with EntityFramework Core 3

By Philipp Wagner | June 20, 2020

One thing I am missing in EntityFramework Core is a way to run raw SQL queries and map the results to a class, just like the EntityFramework 6 DbContext.Database.SqlQuery<T> method.

So why on earth would you want to execute raw SQL?

For small projects it's much simpler to write a small query, than fiddling with abstractions like...

LINQ if you are in C#
JPQL if you are in Java

I firmly believe a good query can save you hundred lines of code.

Does it create a maintenance nightmare? Probably.

Anyway!

@davidbaxterbrowne shared quite nice solution to add a SqlQuery<T> extension method to an EntityFramework Core DbContext.

I think it's worth sharing, because it is quite hard to find being buried in a GitHub issue.

All credit goes to @davidbaxterbrowne:

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;

namespace EasyTimeTracking.Web.Database.Extensions
{
    // Please see the GitHub Issue for the Original Code:
    //
    //      https://github.com/dotnet/efcore/issues/1862
    //
    public static class SqlQueryExtensions
    {
        public static Task<List<T>> SqlQueryAsync<T>(this DbContext db, FormattableString sql, CancellationToken cancellationToken) where T : class
        {            
            using (var contextForQuery = new ContextForQueryType<T>(db.Database.GetDbConnection()))
            {
                return contextForQuery.Set<T>()
                    .FromSqlInterpolated(sql)
                    .AsNoTracking()
                    .ToListAsync(cancellationToken);
            }
        }

        private class ContextForQueryType<T> : DbContext where T : class
        {
            private readonly DbConnection connection;

            public ContextForQueryType(DbConnection connection)
            {
                this.connection = connection;
            }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseNpgsql(connection);

                base.OnConfiguring(optionsBuilder);
            }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<T>().HasNoKey();

                base.OnModelCreating(modelBuilder);
            }
        }
    }
}

And now you can use the Extension method on a DbContext like this:

public async Task<List<MyObject>> GetEntitiesAsync(DateTime startDate, CancellationToken cancellationToken)
{
    using (var context = new ApplicationDbContext())
    {
        return await context
            .SqlQueryAsync<MyObject>($@"select *
                                      from my_database_table t
                                      where t.date >= {startDate}", cancellationToken);    
    }
}

Please note, that the method takes a FormattableString, so it does parameter binding under the hood to avoid SQL injections.

It's not a perfect solution, because you still need to hardcode the provider in the OnConfiguring method of the DbContext, but I didn't find a simple way in the EntityFramework Core API surface to get the original options.

Let me know, if you have ideas on how to improve the code.