How to test database views using Entity Framework Core's in memory DB provider?
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 DbSet
s and DbQuery
s and projecting to MyQueryType
.
Then the test would feed the involved entities with data and the queries using DbQuery
s 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 DbQuery
s 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.