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

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.