How could I Mock the FromSql() method?

Question

I was wondering is there any way other than building a wrapper for mocking the FromSql? I know this method is static, but since they added things like AddEntityFrameworkInMemoryDatabase to entity framework core, I thought there might be a solution for this too, I use EF Core 1.0.1 in my project.

My end goal is to test this method:

public List<Models.ClosestLocation> Handle(ClosestLocationsQuery message)
{
    return _context.ClosestLocations.FromSql(
        "EXEC GetClosestLocations {0}, {1}, {2}, {3}",
        message.LocationQuery.Latitude,
        message.LocationQuery.Longitude,
        message.LocationQuery.MaxRecordsToReturn ?? 10,
        message.LocationQuery.Distance ?? 10
    ).ToList();
}

I want to ensure that my query is handled with the same object that I passed into it, based on this answer in entity framework 6 I could do something like this:

[Fact]
public void HandleInvokesGetClosestLocationsWithCorrectData()
{
    var message = new ClosestLocationsQuery
    {
        LocationQuery =
            new LocationQuery {Distance = 1, Latitude = 1.165, Longitude = 1.546, MaxRecordsToReturn = 1}
    };

    var dbSetMock = new Mock<DbSet<Models.ClosestLocation>>();

    dbSetMock.Setup(m => m.FromSql(It.IsAny<string>(), message))
        .Returns(It.IsAny<IQueryable<Models.ClosestLocation>>());

    var contextMock = new Mock<AllReadyContext>();

    contextMock.Setup(c => c.Set<Models.ClosestLocation>()).Returns(dbSetMock.Object);

    var sut = new ClosestLocationsQueryHandler(contextMock.Object);
    var results = sut.Handle(message);

    contextMock.Verify(x => x.ClosestLocations.FromSql(It.IsAny<string>(), It.Is<ClosestLocationsQuery>(y =>
        y.LocationQuery.Distance == message.LocationQuery.Distance &&
        y.LocationQuery.Latitude == message.LocationQuery.Latitude &&
        y.LocationQuery.Longitude == message.LocationQuery.Longitude &&
        y.LocationQuery.MaxRecordsToReturn == message.LocationQuery.MaxRecordsToReturn)));
}

But unlike SqlQuery<T> in EF 6, the FromSql<T> in EF Core is static extension method, I'm asking this question because I think I might approach this problem from the wrong angle or there might be a better alternative than a wrapper, I'd appreciate any thought on this.

Answer 1

I also fell into the same situation and answer given by Philippe helped but it the main method was throwing System.ArgumentNullException.

From this link, I was finally able to write some unit tests...

Here is my class under test:

public class HolidayDataAccess : IHolidayDataAccess
{
    private readonly IHolidayDataContext holDataContext;

    public HolidayDataAccess(IHolidayDataContext holDataContext)
    {
        this.holDataContext = holDataContext;
    }

    public async Task<IEnumerable<HolidayDate>> GetHolidayDates(DateTime startDate, DateTime endDate)
    {
        using (this.holDataContext)
        {
            IList<HolidayDate> dates = await holDataContext.Dates.FromSql($"[dba].[usp_GetHolidayDates] @StartDate = {startDate}, @EndDate = {endDate}").AsNoTracking().ToListAsync();
            return dates;
        }
    }
}

and here is the test method:

[TestMethod]
public async Task GetHolidayDates_Should_Only_Return_The_Dates_Within_Given_Range()
{
    // Arrange.

        SpAsyncEnumerableQueryable<HolidayDate> dates = new SpAsyncEnumerableQueryable<HolidayDate>();
        dates.Add(new HolidayDate() { Date = new DateTime(2018, 05, 01) });
        dates.Add(new HolidayDate() { Date = new DateTime(2018, 07, 01) });
        dates.Add(new HolidayDate() { Date = new DateTime(2018, 04, 01) });
        dates.Add(new HolidayDate() { Date = new DateTime(2019, 03, 01) });
        dates.Add(new HolidayDate() { Date = new DateTime(2019, 02, 15) });


    var options = new DbContextOptionsBuilder<HolidayDataContext>()
        .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
        .Options;

    HolidayDataContext context = new HolidayDataContext(options);

    context.Dates = context.Dates.MockFromSql(dates);

    HolidayDataAccess dataAccess = new HolidayDataAccess(context);

    //Act.
    IEnumerable<HolidayDate> resutlDates = await dataAccess.GetHolidayDates(new DateTime(2018, 01, 01), new DateTime(2018, 05, 31));

    // Assert.

    Assert.AreEqual(resutlDates.Any(d => d.Date != new DateTime(2019, 03, 01)), true);
    Assert.AreEqual(resutlDates.Any(d => d.Date != new DateTime(2019, 02, 15)), true);

    // we do not need to call this becuase we are using a using block for the context...
    //context.Database.EnsureDeleted();
}

To use UseInMemoryDatabase you need to add Microsoft.EntityFrameworkCore.InMemory package from NuGet The helper classes are here:

public class SpAsyncEnumerableQueryable<T> : IAsyncEnumerable<T>, IQueryable<T>
{
    private readonly IList<T> listOfSpReocrds;

    public Type ElementType => throw new NotImplementedException();

    public IQueryProvider Provider => new Mock<IQueryProvider>().Object;

    Expression IQueryable.Expression => throw new NotImplementedException();

    public SpAsyncEnumerableQueryable()
    {
        this.listOfSpReocrds = new List<T>();
    }        

    public void Add(T spItem) // this is new method added to allow xxx.Add(new T) style of adding sp records...
    {
        this.listOfSpReocrds.Add(spItem);
    }

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

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

    IAsyncEnumerator<T> IAsyncEnumerable<T>.GetEnumerator()
    {
        return listOfSpReocrds.ToAsyncEnumerable().GetEnumerator();
    }
}

...and the Db extensions class that contains the mock of FromSql method..

public static class DbSetExtensions
{
    public static DbSet<T> MockFromSql<T>(this DbSet<T> dbSet, SpAsyncEnumerableQueryable<T> spItems) where T : class
    {
        var queryProviderMock = new Mock<IQueryProvider>();
        queryProviderMock.Setup(p => p.CreateQuery<T>(It.IsAny<MethodCallExpression>()))
            .Returns<MethodCallExpression>(x => spItems);

        var dbSetMock = new Mock<DbSet<T>>();
        dbSetMock.As<IQueryable<T>>()
            .SetupGet(q => q.Provider)
            .Returns(() => queryProviderMock.Object);

        dbSetMock.As<IQueryable<T>>()
            .Setup(q => q.Expression)
            .Returns(Expression.Constant(dbSetMock.Object));
        return dbSetMock.Object;
    }
}

Hope this helps!

Edits: refactored SpAsyncEnumerableQueryable class to have Add method. Got rid of parameterised construction that took array of T. Implemented IQueryProvider Provider => new Mock<IQueryProvider>().Object; to support .AsNoTracking(). Calling the ToList asynchronously.

Answer 2

If you look at the code in FromSql<T>, you can see that it makes a call to source.Provider.CreateQuery<TEntity>. This is what you have to mock.

In your case, I think you can work it out with something like that:

var mockProvider = new Mock<IQueryProvider>();
mockProvider.Setup(s => s.CreateQuery(It.IsAny<MethodCallExpression>()))
    .Returns(null as IQueryable);
var mockDbSet = new Mock<DbSet<AllReady.Models.ClosestLocation>>();
mockDbSet.As<IQueryable<AllReady.Models.ClosestLocation>>()
    .Setup(s => s.Provider)
    .Returns(mockProvider.Object);
var t = mockDbSet.Object;
context.ClosestLocations = mockDbSet.Object;

var sut = new ClosestLocationsQueryHandler(context);
var results = sut.Handle(message);

Not sure how you can Verify on a MethodCallExpression afterwards, but I suppose that would be possible. Alternatively, there might be a way to check the generated SQL.

Answer 3

Old thread is old; however if anyone else is looking; It's possible to perform a Moq verify on a from sql invocation, you've got to do it against the query provider.

var providerMock = Mock.Get(((IQueryable<TestEntity>) mockedDbContext.Set<TestEntity>()).Provider);
providerMock.Verify(m => m.CreateQuery<TestEntity>(It.IsAny<MethodCallExpression>()), Times.Once);

Same methodology applies to NSub as well.

The method call expression verification can be fine tuned to match on sql and parameters, it contains the sql (RawSqlString) and the parameters (IEnumerable) as constant expressions.

var mceRawSqlString = (RawSqlString) ((ConstantExpression) mce.Arguments[1]).Value;
var mceParameters = (object[]) ((ConstantExpression) mce.Arguments[2]).Value

where mce is the MethodCallExpression fed to CreateQuery.

I've done a bit of MCE deconstruction in my project EntityFrameworkCore.Testing to support FromSql mocking; I farm out the actual matching to supporting methods as it gets a bit hairy.

queryProviderMock.Setup(m => m.CreateQuery<T>(It.Is<MethodCallExpression>(mce => SpecifiedParametersMatchMethodCallExpression(mce, sqlToMatchAgainst, parametersToMatchAgainst))))