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

c# entity-framework-core

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?

1
10
4/9/2019 6:29:04 PM

Accepted Answer

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.

6
4/9/2019 6:27:22 PM

Popular Answer

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;
    }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow