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

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)"