How could I Mock the FromSql() method?

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.

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.