How to mock Entity Framework's FromSqlRaw method?

Question

I am writing a Unit Test and need to mock Entity Framework's .FromSqlRaw method. When the method is executed in the class under test, it throws following exception:

System.InvalidOperationException: There is no method 'FromSqlOnQueryable' on type 'Microsoft.EntityFrameworkCore.RelationalQueryableExtensions' that matches the specified arguments.

Following is class under test:

public class PowerConsumptionRepository : IPowerConsumptionRepository
    {
        private readonly IDatabaseContext _databaseContext;
        private readonly IDateTimeHelper _dateTimeHelper;

        public PowerConsumptionRepository(IDatabaseContext databaseContext, IDateTimeHelper dateTimeHelper)
        {
            _databaseContext = databaseContext;
            _dateTimeHelper = dateTimeHelper;
        }
        public List<IntervalCategoryConsumptionModel> GetCurrentPowerConsumption(string siteId)
        {
            var currentDate = _dateTimeHelper
                .ConvertUtcToLocalDateTime(DateTime.UtcNow, ApplicationConstants.LocalTimeZone)
                .ToString("yyyy-MM-dd");
            var currentDateParameter = new SqlParameter("currentDate", currentDate);
            var measurements = _databaseContext.IntervalPowerConsumptions
                .FromSqlRaw(SqlQuery.CurrentIntervalPowerConsumption, currentDateParameter)
                .AsNoTracking()
                .ToList();
            return measurements;
        }
    }

Unit Test:

    public class PowerConsumptionRepositoryTests
    {
        [Fact]
        public void TestTest()
        {
            var data = new List<IntervalCategoryConsumptionModel>
            {
                new IntervalCategoryConsumptionModel
                {
                    Id = 1,
                    Hvac = 10                    
                },
                new IntervalCategoryConsumptionModel
                {
                    Id = 1,
                    Hvac = 10
                }
            }.AsQueryable();
            var dateTimeHelper = Substitute.For<IDateTimeHelper>();
            dateTimeHelper.ConvertUtcToLocalDateTime(Arg.Any<DateTime>(), Arg.Any<string>()).Returns(DateTime.Now);
            var mockSet = Substitute.For<DbSet<IntervalCategoryConsumptionModel>, IQueryable<IntervalCategoryConsumptionModel>>();
            ((IQueryable<IntervalCategoryConsumptionModel>)mockSet).Provider.Returns(data.Provider);
            ((IQueryable<IntervalCategoryConsumptionModel>)mockSet).Expression.Returns(data.Expression);
            ((IQueryable<IntervalCategoryConsumptionModel>)mockSet).ElementType.Returns(data.ElementType);
            ((IQueryable<IntervalCategoryConsumptionModel>)mockSet).GetEnumerator().Returns(data.GetEnumerator());
            var context = Substitute.For<IDatabaseContext>();
            context.IntervalPowerConsumptions = (mockSet);
            var repo = new PowerConsumptionRepository(context, dateTimeHelper);
            var result = repo.GetCurrentPowerConsumption(Arg.Any<string>());
            result.Should().NotBeNull();
        }
    }

Affectionate

To quote the EF Core documentation: we never try to mock DbContext or IQueryable. Doing so is difficult, cumbersome, and fragile. Don't do it. (Their emphasis.) – Gilemette

Right. I understand. But does that mean I cannot unit test it? I cannot use In-memory database as .FromSqlRaw executes SQL query. – Affectionate

Isn't that the problem then? That you are testing a class that is highly coupled with your database and now you want to abstract that database but finding it very hard or impossible? To properly test PowerConsumptionRepository you should probably use a "real" database. I recently came across the post Avoid In-Memory Databases for Tests by Jimmy Bogard which might give you some more perspective on this issue. – Gilemette

Or check for the provider type in your production code. – Anelace

Answer 1

In my scenario I use FromSqlRaw method for invoke stored procedure in my database. For EntityFramework Core (version 3.1 works well for sure) I do it in this way:

Add virtual method to your DbContext class:

public virtual IQueryable<TEntity> RunSql<TEntity>(string sql, params object[] parameters) where TEntity : class
{
    return this.Set<TEntity>().FromSqlRaw(sql, parameters);
}

It's just a simple virtaul wraper from static FromSqlRaw, so you can easily mock it:

var dbMock = new Mock<YourContext>();
var tableContent = new List<YourTable>()
{
    new YourTable() { Id = 1, Name = "Foo" },
    new YourTable() { Id = 2, Name = "Bar" },
}.AsAsyncQueryable();
dbMock.Setup(_ => _.RunSql<YourTable>(It.IsAny<string>(), It.IsAny<object[]>())).Returns(tableContent );

Call our new RunSql method instead of FromSqlRaw:

// Before
//var resut = dbContext.FromSqlRaw<YourTable>("SELECT * FROM public.stored_procedure({0}, {1})", 4, 5).ToListAsync();
// New
var result = dbContext.RunSql<YourTable>("SELECT * FROM public.stored_procedure({0}, {1})", 4, 5).ToListAsync();

Last, but not least, you need to add AsAsyncQueryable() extension method to your test project. It's provided by user @vladimir in a brilliant answer here:

public static class QueryableExtensions
{
    public static IQueryable<T> AsAsyncQueryable<T>(this IEnumerable<T> input)
    {
        return new NotInDbSet<T>( input );
    }

}

public class NotInDbSet< T > : IQueryable<T>, IAsyncEnumerable< T >, IEnumerable< T >, IEnumerable
{
    private readonly List< T > _innerCollection;
    public NotInDbSet( IEnumerable< T > innerCollection )
    {
        _innerCollection = innerCollection.ToList();
    }

    public IAsyncEnumerator< T > GetAsyncEnumerator( CancellationToken cancellationToken = new CancellationToken() )
    {
        return new AsyncEnumerator( GetEnumerator() );
    }

    public IEnumerator< T > GetEnumerator()
    {
        return _innerCollection.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return GetEnumerator();
    }

    public class AsyncEnumerator : IAsyncEnumerator< T >
    {
        private readonly IEnumerator< T > _enumerator;
        public AsyncEnumerator( IEnumerator< T > enumerator )
        {
            _enumerator = enumerator;
        }

        public ValueTask DisposeAsync()
        {
            return new ValueTask();
        }

        public ValueTask< bool > MoveNextAsync()
        {
            return new ValueTask< bool >( _enumerator.MoveNext() );
        }

        public T Current => _enumerator.Current;
    }

    public Type ElementType => typeof( T );
    public Expression Expression => Expression.Empty();
    public IQueryProvider Provider => new EnumerableQuery<T>( Expression );
}

Henton

Answer 2

The in-memory provider can't do it as it's a relational operation. Ignoring the philosophical side of it there are probably a couple of ways you could solve it.

1. Mocking the query provider

Under the covers it's runs through the IQueryProvider.CreateQuery<T>(Expression expression) method so you can use a mocking framework to intercept the invocation and return what you want. That's how EntityFrameworkCore.Testing (disclaimer I am the author) does it. This is how I unit test FromSql* invocations in my code.

2. A better in-memory provider

I haven't used it much but my understanding is a provider like SQLite may support it.

To address the OP comments, WRT whether you should be using an in-memory provider/mocking the DbContext, we are in the realm of personal opinion. Mine is that I have no reservations using the in-memory provider, it's easy to use, reasonably fast and works well for many. I do agree that you shouldn't mock the DbContext, simply because it'd be really hard to do. EntityFrameworkCore.Testing doesn't mock the DbContext per se, it wraps over an in-memory provider and uses popular mocking frameworks to provide support for things like FromSql* and ExecuteSql*.

I read the linked article by Jimmy Bogard (who I have the utmost respect for), however on this topic I don't agree on all points. On the rare occasion that I have raw SQL in my data access layer, it's generally to invoke a stored procedure or function which already has been tested/has tests outside of my SUT. I generally treat them as a dependency; I should be able to write my unit tests for my SUT with that dependency returning the values required to adequately test my SUT.

Matti

I've tried your solution but all what can I get is exception: 'Constructor on type 'MyProject.Models.TestDbContext' not found.', what is wrong because my TestDbContext has a public parameterless constructor (public TestDbContext() { }). – Henton

If you're experiencing a problem with my library, pop an issue up on the repo with a working sample and I can help you further. – Matti

Answer 3

With .FromSqlRaw you are sending raw sql query to the database engine.
If you really want to test that your application (.FromsqlRaw) works as expected, test it against an actual database.

Yes it is slower, yes it requires running database with some test data - and yes it will provide you strong confidence that your application is working.

All other tests (mocked or in-memory or sqlite) will provide you false feeling of confidence.

Translocation