How to properly moq ExecuteQuery method within DataContext?

Question

I'm having a difficult time trying to understand how to appropriately return mocked data from a simulated database call in a unit test.

Here's an example method I want to unit test (GetBuildings):

public class BuildingService : IBuildingService {

    public IQueryable<Building> GetBuildings(int propertyId)
    {
        IQueryable<Building> buildings;

        // Execution path for potential exception thrown
        // if (...) throw new SpecialException();

        // Another execution path...
        // if (...) ...

        using (var context = DataContext.Instance())
        {
            var Params = new List<SqlParameter>
            {
                new SqlParameter("@PropertyId", propertyId)
            };

            // I need to return mocked data here...
            buildings = context
              .ExecuteQuery<Building>(System.Data.CommandType.StoredProcedure, "dbo.Building_List", Params.ToArray<object>())
              .AsQueryable();


        }

        return buildings;
    }

}

So GetBuildings calls a stored procedure.

So I need to mock the DataContext, that of which I can override and set a testable instance. So what happens here is, in the above example DataContext.Instance() does return the mocked object.

[TestFixture]
public class BuildingServiceTests
{

    private Mock<IDataContext> _mockDataContext;

    [SetUp]
    public void Setup() {
        _mockDataContext = new Mock<IDataContext>();
    }

    [TearDown]
    public void TearDown() {
        ...
    }

    [Test]
    public void SomeTestName() {

        _mockDataContext.Setup(r => 
            r.ExecuteQuery<Building>(CommandType.StoredProcedure, "someSproc"))
            .Returns(new List<Building>() { new Building() { BuildingId = 1, Title = "1" }}.AsQueryable());

      DataContext.SetTestableInstance(_mockDataContext.Object); 
        var builings = BuildingService.GetBuildings(1, 1);

      // Assert...

    }

Please ignore some of the parameters, like propertyId. I've stripped those out and simplified this all. I simply can't get the ExecuteQuery method to return any data.

All other simple peta-poco type methods I can mock without issue (i.e. Get, Insert, Delete).

Update

DataContext.Instance returns the active instance of the DataContext class, if exists, and if not exists, returns a new one. So the method of test under question returns the mocked instance.

Answer 1

Do not mock DataContext. Because mocking DataContext will produce tests tightly coupled to the implementation details of DataContext. And you will be forced to change tests for every change in the code even behavior will remain same.

Instead introduce a "DataService" interface and mock it in the tests for BuildingService.

public interface IDataService
{
    IEnumerable<Building> GetBuildings(int propertyId)
}

Then, you can tests implementation of IDataService agains real database as part of integration tests or tests it agains database in memory.

If you can test with "InMemory" database (EF Core or Sqlite) - then even better -> write tests for BuildingService against actual implementation of DataContext.

In tests you should mock only external resources (web service, file system or database) or only resources which makes tests slow.

Not mocking other dependencies will save you time and give freedom while you refactoring your codebase.

After update:

Based on the updated question, where BuildingService have some execution path - you can still testing BuildingService and abstract data related logic to the IDataService.

For example below is BuildingService class

public class BuildingService
{
    private readonly IDataService _dataService;

    public BuildingService(IDataService dataService)
    {
         _dataService = dataService;
    }

    public IEnumerable<Building> GetBuildings(int propertyId)
    {
        if (propertyId < 0)
        {
            throw new ArgumentException("Negative id not allowed");
        }

        if (propertyId == 0)
        {
            return Enumerable.Empty<Building>();
        }

        return _myDataService.GetBuildingsOfProperty(int propertyId);
    }
}

In tests you will create a mock for IDataService and pass it to the constructor of BuildingService

var fakeDataService = new Mock<IDataContext>();
var serviceUnderTest = new BuildingService(fakeDataService);

Then you will have tests for:

"Should throw exception when property Id is negative"  
"Should return empty collection when property Id equals zero"
"Should return collection of expected buildings when valid property Id is given"

For last test case you will mock IDataService to return expected building only when correct propertyId is given to _dataService.GetBuildingsOfProperty method

Answer 2

In order for the mock to return data is needs to be set up to behave as expected given a provided input.

currently in the method under test it is being called like this

buildings = context
  .ExecuteQuery<Building>(System.Data.CommandType.StoredProcedure, "dbo.Building_List", Params.ToArray<object>())
  .AsQueryable();

Yet in the test the mock context is being setup like

_mockDataContext.Setup(r => 
    r.ExecuteQuery<Building>(CommandType.StoredProcedure, "someSproc"))
    .Returns(new List<Building>() { new Building() { BuildingId = 1, Title = "1" }}.AsQueryable());

Note what the mock is told to expect as parameters.

The mock will only behave as expected when provided with those parameters. Otherwise it will return null.

Consider the following example of how the test can be exercised based on the code provided in the original question.

[Test]
public void SomeTestName() {
    //Arrange
    var expected = new List<Building>() { new Building() { BuildingId = 1, Title = "1" }}.AsQueryable();
    _mockDataContext
        .Setup(_ => _.ExecuteQuery<Building>(CommandType.StoredProcedure, It.IsAny<string>(), It.IsAny<object[]>()))
        .Returns(expected);

    DataContext.SetTestableInstance(_mockDataContext.Object);
    var subject = new BuildingService();

    //Act
    var actual = subject.GetBuildings(1);

    // Assert...
    CollectionAssert.AreEquivalent(expected, actual);
}

That said, the current design of the system under test is tightly coupled to a static dependency which is a code smell and makes the current design follow some bad practices.

The static DataContext which is currently being used as a factory should be refactored as such,

public interface IDataContextFactory {
    IDataContext CreateInstance();
}

and explicitly injected into dependent classes instead of calling the static factory method

public class BuildingService : IBuildingService {

    private readonly IDataContextFactory factory;

    public BuildingService(IDataContextFactory factory) {
        this.factory = factory
    }

    public IQueryable<Building> GetBuildings(int propertyId) {
        IQueryable<Building> buildings;

        using (var context = factory.CreateInstance()) {
            var Params = new List<SqlParameter> {
                new SqlParameter("@PropertyId", propertyId)
            };

            buildings = context
              .ExecuteQuery<Building>(System.Data.CommandType.StoredProcedure, "dbo.Building_List", Params.ToArray<object>())
              .AsQueryable();
        }

        return buildings;
    }
}

This will allow for a proper mock to be created in injected into the subject under test without using a static workaround hack.

[Test]
public void SomeTestName() {
    //Arrange
    var expected = new List<Building>() { new Building() { BuildingId = 1, Title = "1" }}.AsQueryable();
    _mockDataContext
        .Setup(_ => _.ExecuteQuery<Building>(CommandType.StoredProcedure, It.IsAny<string>(), It.IsAny<object[]>()))
        .Returns(expected);

    var factoryMock = new Mock<IDataContextFactory>();
    factoryMock
        .Setup(_ => _.CreateInstance())
        .Returns(_mockDataContext.Object);

    var subject = new BuildingService(factoryMock.Object);

    //Act
    var actual = subject.GetBuildings(1);

    // Assert...
    CollectionAssert.AreEquivalent(expected, actual);
}