SqlQuery with EntityFramework Core 3

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.

How to contribute

One of the easiest ways to contribute is to participate in discussions. You can also contribute by submitting pull requests.

General feedback and discussions?

Do you have questions or feedback on this article? Please create an issue on the Repositories issue tracker.

Something is wrong or missing?

There may be something wrong or missing in this article. If you want to help fixing it, then please make a Pull Request to this file.