How to Moq Entity Framework SqlQuery calls

Question

I've been able to mock DbSet's from entity framework with Moq using this link.

However, I would now like to know how I could mock the call to SqlQuery. Not sure if this is possible or how as it relies on the mocked db context knowing what "query" is being called.

Below is what I am trying to mock.

var myObjects = DbContext.Database
    .SqlQuery<MyObject>("exec [dbo].[my_sproc] {0}", "some_value")
    .ToList();

I currently haven't tried anything as did not know how to start mocking this example.

The mocking of the DbSet is below and to re-iterate, I can correctly mock returning a DbSet of MyObject's but now am trying to mock a SqlQuery that returns a list of MyObject's.

var dbContext = new Mock<MyDbContext>();
dbContext.Setup(m => m.MyObjects).Returns(mockObjects.Object);

dbContext.Setup(m => m.Database.SqlQuery... something along these lines

Answer 1

Database.SqlQuery<T> is not marked as virtual, but Set<T>.SqlQuery is marked as virtual.

Based on Database.SqlQuery<T> documentation

The results of this query are never tracked by the context even if the type of object returned is an entity type. Use the 'SqlQuery(String, Object[])' method to return entities that are tracked by the context.

and Set<T>.SqlQuery documentation

By default, the entities returned are tracked by the context; this can be changed by calling AsNoTracking on the DbRawSqlQuery returned.

then the Database.SqlQuery<T>(String, Object[]) should be equivalent with Set<T>.SqlQuery(String, Object[]).AsNoTracking() (only if T is EF entity, not a DTO / VM).

So if you can replace the implementation into:

var myObjects = DbContext
    .Set<MyObject>()
    .SqlQuery("exec [dbo].[my_sproc] {0}", "some_value")
    .AsNoTracking()
    .ToList();

you can mock it as follow

var list = new[] 
{ 
    new MyObject { Property = "some_value" },
    new MyObject { Property = "some_value" },
    new MyObject { Property = "another_value" }
};

var setMock = new Mock<DbSet<MyObject>>();
setMock.Setup(m => m.SqlQuery(It.IsAny<string>(), It.IsAny<object[]>()))
    .Returns<string, object[]>((sql, param) => 
    {
        // Filters by property.
        var filteredList = param.Length == 1 
            ? list.Where(x => x.Property == param[0] as string) 
            : list;
        var sqlQueryMock = new Mock<DbSqlQuery<MyObject>>();
        sqlQueryMock.Setup(m => m.AsNoTracking())
            .Returns(sqlQueryMock.Object);
        sqlQueryMock.Setup(m => m.GetEnumerator())
            .Returns(filteredList.GetEnumerator());
        return sqlQueryMock.Object;
    });

var contextMock = new Mock<MyDbContext>();
contextMock.Setup(m => m.Set<MyObject>()).Returns(setMock.Object);

Answer 2

You can add a virtual method to your database context that you can override in unit tests:

public partial class MyDatabaseContext : DbContext
{
    /// <summary>
    /// Allows you to override queries that use the Database property
    /// </summary>
    public virtual List<T> SqlQueryVirtual<T>(string query)
    {
        return this.Database.SqlQuery<T>(query).ToList();
    }
}