How to test database views using Entity Framework Core's in memory DB provider?

Question

When I tried to add objects to views, it throws exception saying unable to track an instance of type because it is a query type. Is there a way to get around this?

Answer 1

I ended up refactoring Ivan's extension class code based on his suggestions/recommendations, as follows. I added overloads to the ToFakeQuery method to take in a dictionary.

public static class InMemoryQueryProviderExtensions
{
    static Dictionary<Type, IQueryable> queries = new Dictionary<Type, IQueryable>();

    public static void SetQuery<T>(IQueryable<T> query)
    {
        lock (queries)
            queries[typeof(T)] = query;
    }

    public static IQueryable<T> GetQuery<T>()
    {
        lock (queries)
            return queries.TryGetValue(typeof(T), out var query) ? (IQueryable<T>)query : Enumerable.Empty<T>().AsQueryable();
    }
    private static IQueryable<T> GetQuery<T>(Dictionary<Type, IQueryable> queryDictionary)
    {
        return queryDictionary.TryGetValue(typeof(T), out var query) ? (IQueryable<T>)query : Enumerable.Empty<T>().AsQueryable();
    }

    public static QueryTypeBuilder<T> ToFakeQuery<T>(this QueryTypeBuilder<T> builder)
        where T : class
    {
        return builder.ToQuery(() => GetQuery<T>());
    }
    public static QueryTypeBuilder<T> ToFakeQuery<T>(this QueryTypeBuilder<T> builder, Dictionary<Type, IQueryable> queryDictionary)
        where T : class
    {
        return builder.ToQuery(() => GetQuery<T>(queryDictionary));
    }
}

And then, creating a new derived class for my DBContext as follows. Basically, making the derived instance of the in-memory DBContext maintain the dictionary.

public class TlInMemoryDbContext : TlDbContext
{
    public TlInMemoryDbContext(DbContextOptions<TlDbContext> options)
        : base(options)
    { }

    Dictionary<Type, IQueryable> queries = new Dictionary<Type, IQueryable>();
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Query<EffectiveTimeEntry>().ToFakeQuery(queries);
    }

    public void SetQuery<T>(IQueryable<T> query)
    {
        lock (queries)
            queries[typeof(T)] = query;
    }
}

Answer 2

I used the above as suggested by Ivan Stoev.

This is how it looked

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  if(Database.IsSqlServer())
  {
     modelBuilder.Query<ProviderRating>(entity =>
                {
                    entity.ToView("vGetProviderRatingData", "dbo");
                    entity.Property(e => e.col1)
                    .HasMaxLength(10)
                    .IsUnicode(false);
                    entity.Property(e => e.col2)
                    .HasMaxLength(60)
                    .IsUnicode(false);
                    entity.Property(e => e.col3)
                    .HasMaxLength(10)
                    .IsUnicode(false);

                });
  }
  else
  {
    modelBuilder.Query<ProviderRating>().ToQuery(() =>
                 ProviderRatingFake.Select(m => new ProviderRating()
                 {
                     col1 = m.col1,
                     col2 = m.col2,
                     col3 = m.col3,
                 }
                ));
  }
}

The ProviderRatingFake class exactly similar to ProviderRating class

I also added this code in DbContext file (ProviderQualityContext)

 public virtual DbSet<ProviderRatingFake> ProviderRatingFake { get; set; }

 public virtual DbQuery<ProviderRating> ProviderRating { get; set; }

Then I tested like this

[TestMethod]
public void TestingWithInMemoryDb()
{
   var options = new DbContextOptionsBuilder<ProviderQualityContext>()
                .UseInMemoryDatabase(databaseName: "Read_From_Database")
                .Options;
  var fakeProviderRating = new ProviderRatingFake
            {
                col1 = 1,
                col2 = "Something",
                col3 = "Something",
            };
  using (var context = new ProviderQualityContext(options))
  {
    context.ProviderRatingFake.Add(fakeProviderRating);
    context.SaveChanges();
  }

  //use the newly created context and inject it into controller or repository
  using (var context = new ProviderQualityContext(options))
  {
    //use the test context here and make assertions that you are returning the
   //fake data
   //Note that the actual code uses the Query like this
   //This query will be populated with fake data using the else block
   //in the method OnModelCreating
   var returnedData = this.dbContext.Query<ProviderRating>().Where(m => m.col1 == 
   "Something")
  }

}

Answer 3

Query Types are read-only by definition (for all database providers, not only for in memory):

• Are never tracked for changes on the DbContext and therefore are never inserted, updated or deleted on the database.

However, additionally to their usual usage scenarios of

• Mapping to database views.
• Mapping to tables that do not have a primary key defined.

they allow

• Mapping to queries defined in the model.

or in other words

• May be mapped to a defining query - A defining query is a secondary query declared in the model that acts a data source for a query type.

which is achieved with ToQuery fluent API:

Configures a query used to provide data for a query type.

So for testing query types with in memory database, you should utilize the defining query mapping capability.

For instance, inside OnModelCreating override you could add something like this:

if (Database.IsInMemory())
{
   // In memory test query type mappings
    modelBuilder.Query<MyQueryType>().ToQuery(() => LINQ_query);
    // ... similar for other query types
}
else
{
    // Database query type mappings
    modelBuilder.Query<MyQueryType>().ToView("MyQueryTypeView");
    // ... 
}

where LINQ_query is a normal LINQ query accessing context DbSets and DbQuerys and projecting to MyQueryType.

Then the test would feed the involved entities with data and the queries using DbQuerys will retrieve the data from the defining query.

---

The above should be the recommended way to test views with in memory database.

Just for completeness, it's possible to directly feed the DbQuerys with data (basically mocking them) by creating some sort of query repository, but with the following restriction - it must be shared (static), because currently EF Core does not handle correctly db context members (like global query filter does) other than DbSet<T> and DbQuery<T>.

Something like this:

public static class FakeQueryProvider
{
    static Dictionary<Type, IQueryable> queries = new Dictionary<Type, IQueryable>();

    public static void SetQuery<T>(IQueryable<T> query)
    {
        lock (queries)
            queries[typeof(T)] = query;
    }

    public static IQueryable<T> GetQuery<T>()
    {
        lock (queries)
            return queries.TryGetValue(typeof(T), out var query) ? (IQueryable<T>)query : Enumerable.Empty<T>().AsQueryable();
    }

    public static QueryTypeBuilder<T> ToFakeQuery<T>(this QueryTypeBuilder<T> builder)
        where T : class
    { 
        return builder.ToQuery(() => GetQuery<T>());
    }
}

then instead of

.ToQuery(() => LINQ_query);

you would use

.ToFakeQuery(); 

and would feed it inside the test like this

 List<MyQueryType> data = ...;
 FakeQueryProvider.SetQuery(data.AsQueryable());

Still I recommend the first approach due to shared storage limiting the ability to run MyQueryType related tests in parallel.