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();
    }
}

Answer 3

The Database property and SqlQuery method are not marked as virtual so they can't be mocked (using Moq; you could use a different library that can account for this but that may be more inertia than you'd like).

You'd need to use some sort of abstraction to get around this, such as by wrapping the entire query of the database in a helper class:

public interface IQueryHelper
{
    IList<MyObject> DoYourQuery(string value);
}

public class QueryHelper : IQueryHelper
{
    readonly MyDbContext myDbContext;

    public QueryHelper(MyDbContext myDbContext)
    {
        this.myDbContext = myDbContext;
    }

    public IList<MyObject> DoYourQuery(string value)
    {
        return myDbContext.Database.SqlQuery<MyObject>("exec [dbo].[my_sproc] {0}", value).ToList();
    }
}

Now the method you are testing becomes:

public void YourMethod()
{
    var myObjects = queryHelper.DoYourQuery("some_value");
}

Then you'd inject the IQueryHelper in the constructor of the class you're testing and mock that.

You're going to be missing test coverage on DoYourQuery, but the now the query is so simple there are obviously no deficiencies.

Answer 4

Should anyone come across this. I solved this with a few approaches. Just another way to address this.

1. My context is abstracted through an interface. I only need a few of the methods:

   public interface IDatabaseContext
   {
       DbSet<T> Set<T>() where T : class;
       DbEntityEntry<T> Entry<T>(T entity) where T : class;
       int SaveChanges();
       Task<int> SaveChangesAsync();
       void AddOrUpdateEntity<TEntity>(params TEntity[] entities) where TEntity : class;
   

   }

2. All of my database access is through async methods. Which brings up a whole new set of problems when trying to mock it. Fortunately - it has been answered here. The exception you get is related to the missing mock for IDbAsyncEnumerable. Using the solution provided - I just extended it a bit more so that I had a helper to return a Mock> object that mocked all of the properties expected.

   public static Mock<DbSqlQuery<TEntity>> CreateDbSqlQuery<TEntity>(IList<TEntity> data)
       where TEntity : class, new()
   {
       var source = data.AsQueryable();
       var mock = new Mock<DbSqlQuery<TEntity>>() {CallBase = true};
       mock.As<IQueryable<TEntity>>().Setup(m => m.Expression).Returns(source.Expression);
       mock.As<IQueryable<TEntity>>().Setup(m => m.ElementType).Returns(source.ElementType);
       mock.As<IQueryable<TEntity>>().Setup(m => m.GetEnumerator()).Returns(source.GetEnumerator());
       mock.As<IQueryable<TEntity>>().Setup(m => m.Provider).Returns(new TestDbAsyncQueryProvider<TEntity>(source.Provider));
       mock.As<IDbAsyncEnumerable<TEntity>>().Setup(m => m.GetAsyncEnumerator()).Returns(new TestDbAsyncEnumerator<TEntity>(data.GetEnumerator()));
       mock.As<IDbSet<TEntity>>().Setup(m => m.Create()).Returns(new TEntity());
       mock.As<IDbSet<TEntity>>().Setup(m => m.Add(It.IsAny<TEntity>())).Returns<TEntity>(i => { data.Add(i); return i; });
       mock.As<IDbSet<TEntity>>().Setup(m => m.Remove(It.IsAny<TEntity>())).Returns<TEntity>(i => { data.Remove(i); return i; });
       return mock;
   }
   

3. Finally - using the solution provided by @Yulium Chandra - my testing of raw SQL with mocked context looks like:

       public Mock<DbSet<TestModel>> MockDbSet { get; }
   
       ....
   
       MockDbSet.Setup(x => x.SqlQuery(It.IsAny<string>))
             .Returns<string,object[]>
             ((sql, param) => 
             {
                   var sqlQueryMock = MockHelper.CreateDbSqlQuery(Models);
   
                   sqlQueryMock.Setup(x => x.AsNoTracking())
                     .Returns(sqlQueryMock.Object);
   
                   return sqlQueryMock.Object;
               });
   

Answer 5

I did a variation on @Patrick Quirk's answer which is working well for me, where I pass the context into the method itself. My interface:

public interface ISqlQueryHelper<T, in TU> where T : class where TU : DbContext
    {
        Task<IList<T>> Execute(FormattableString value, TU context);
    }

the implementation:

public class SqlQueryHelper<T,TU> : ISqlQueryHelper<T,TU> where T : class 
                                                            where TU : DbContext
    {
        public async Task<IList<T>> Execute(FormattableString value, TU context)
        {
            return await context.Database.SqlQuery<T>(value).ToListAsync();
        }
    }

... wiring up the dependency injection in Startup.cs:

services.AddScoped<ISqlQueryHelper<UserDto,UserContext>, SqlQueryHelper<UserDto,UserContext>>();

then finally a call to it:

FormattableString query = $"EXECUTE [dbo].[GetTechLeadForSpecifiedUser] @userId = {userId}";

            var techLeads = (await _sqlQueryHelper.Execute(query, _context)).ToList();