Testing a View using the InMemory Database and EntityFramework Core Code First

Question

I have a EF core code first web api application.

There is a Products entity and a UserProductsRating child entity (with a one to many relationship)

I also wanted to have an average review score (and be able to select/sort based on it) so created a view to do this (using the method described in this answer [https://stackoverflow.com/a/18707413][1])

So the migration for my View looks like:

   protected override void Up(MigrationBuilder migrationBuilder)
            {
                string script =
                @"
                CREATE VIEW AverageProductRating AS 
                SELECT u.ProductId, AVG(CAST(u.Rating AS FLOAT)) as AverageRating 
FROM dbo.UserRatings u GROUP BY u.ProductId";

                migrationBuilder.Sql(script);
            }

            protected override void Down(MigrationBuilder migrationBuilder)
            {
                string script = @"DROP VIEW dbo.AverageProductRating";

                migrationBuilder.Sql(script);
            }

Then there is an AverageRating entity on top.

This all works fine and allows me to create queries like:

 var top5Products = _db.Products.Include(x => x.AverageProductRating)
        .Where(x => x.AverageProductRating != null)
        .OrderByDescending(x => x.AverageProductRating.AverageRating)
        .Take(5); 

The problem occurs when I get to my Unit/Integration Tests. I am using the InMemoryDatabase with EnsureCreated to set up a testing instance/seed data.

var options = new DbContextOptionsBuilder<ProductsContext>()
                .UseInMemoryDatabase(Guid.NewGuid().ToString())
                .EnableSensitiveDataLogging()
                .Options;
            var context = new ProductsContext(options);

context.Database.EnsureCreated();

When I run tests against this the AverageProductRating Entity always has zero rows (I'm not sure if the View gets created at all or not)

I think this may be to do with restrictions in SQL in the inmemory db or the way migrations are run, but I'm not sure.

Any suggestions on how to work round this would be welcome

Thanks