Question posted in
The official documentation can be found here.

Asp.net – Create a unit test to verify data from a SQL view in a .NET EF Core project

We created an API, which will fetch data from a SQL view created in the database.

Here is the repository code:

List<Entities.MyView> reportsData = await (
    from r in _context.MyView
    where r.UserKey == userKey
    select r)
    .ToListAsync(cancellationToken);

Now, we need to implement unit tests for our repository test.

Here is the database setup:

protected void SetUp()
{
    services.AddDbContext<UrpContext>(options =>
    {
        options.UseInMemoryDatabase("My_Database");
    });

    services.AddReportsPersistence();

    provider = services.BuildServiceProvider();

    // Clear the Database if exists any in context
    using (var scope = provider.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<UrpContext>();
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
    }
}

We’re seeding the tables used for the view with sample data. And in the context, we see that data is being added successfully to the tables. But when it comes to the repository and fetching data, it’s showing that my view doesn’t have any data.

What I found is when using an in-memory database for testing, the view is not materialized, so we need to simulate the view logic in the test setup. Is that true, that view will not be updated/refreshed with the data being added to tables.

Is there any recommended approach to implement unit tests for my scenario where I need to test the repository logic? (Only creating view using SQLite in test is the solution ??)

This is the example of how I was adding data to one of our table User:

var context = provider.GetRequiredService<UrpContext>();
User user = new User
{
    UserId = userId,
    Email = $"user{userKey}@org.in"
};

context.User.Add(user);
context.SaveChanges();
Tags:

2

Answers


  1. 0

    Short answer: Don’t try and unit test a repository pattern. That can be covered by an integration test that covers complete end-to-end scenarios with a real database. To set up such an integration test I recommend using the same DBMS as you will be using in production. Create a backup of an instance with the tables and seeded data that integration tests will run as, and restore a named instance reserved for each test suite to run against. The backup does not need to be a complete database. You can use several segmented databases with relevant tables and data to serve one or more test suite.
    Use the same DBMS as production with the same settings to ensure you don’t miss issues that might be caused by things like collation, regional settings, etc. These can also provide performance metrics and hint at issues as well with details like index usage, parameter sniffing. Testing against a different DBMS can have significantly different behavior.

    Unit tests should be fast so you can run them regularly as you develop incrementally. Integration tests would be run as part of an continuous integration process or prior to release candidates as they take more time to set up & run through actual data scenarios, taking several minutes to run through.

    One key reason for adopting a repository pattern is to serve as a boundary for unit testing. The goal being it is a dependency that can be mocked so you can test your actual business logic and as such it should be a lightweight abstraction over EF. You shouldn’t be unit testing views, or whether EF does what it is supposed to. The unit tests cover "does my code do what I expect when my repository returns … <- (insert mocked data scenario here)"

    Login or Signup to reply.
  2. 0

    Well, there are several ways to test your sql view, I’ll list them from least to most favourite & widely adopted way:

    1.Configure to swap your view’s implementation with EF Code when run unit test (use ToInmemoryQuery)

    • Pros: fast and easy
    • Cons: now you have to maintain both the view and the in-memory view query, plus the query representing the view might not be exactly matched the sql view.

    2.Use Sqlite In-memory View

    • Pros: fast and easy, probably provide result that is closer to the original view than the ToInmemoryQuery method.
    • Cons: Same as using ToInmemoryQuery, Sqlite In-memory View and T-SQL View might not be 100% identical. There are also syntax differences between T-SQL and Sqlite. For example, IIF, ISNULL, CAST(.. AS BIT) are not available in Sqlite, you have to manual convert them to Sqlite version.

    3.Use a real dedicated database for testing.

    • Pros: this is the closest level to sql view you can have for your test
    • Cons: All overheads of setting up and managing a new database. Your test cases might not have an isolated environment to run and might interfere with other tests while running parallelly – imagine 5 developers run the same tests suit at the same time – creating/updating/deleting/reading same data against 1 test database (of course this can be solved by proper setting of db Isolation Level/ make use of randomness for object creation/ clean up test data after done). And this is usually slower than running unit test with in-memory provider.

    4.Use Testcontainer

    • Pros: all of the pros you have for testing against a real database. With this method, you can be worried-free about tests interfere with each other & cleaning up data after test. Since everytime you run your tests, it would create a real mssql db inside a container and automatically clean itself up after done.
    • Cons: deeper customization of tests running might require Docker knowledge since it leverages Docker to create & run containers. And usually slower than any in-memory provider. As of my experience, it took around 3s to spawn a new container, run tests, and dispose the container – while tests that use in-memory provider typically run in 100-300ms.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search