Filtering for certain sub-types in EF-Core and TPT-inheritance in WebApi

user_5532977 0
Viewed 76

I am using the EF-Core and have a data model like that (just an example with the animal to keep things simple). The inhertitance is mapped in the database via TPT (table per type).

public abstract class Animal { }

public class Monkey : Animal { }

public class Bear : Animal { }

public class Dog: Animal { }

On my frontend I am displaying all animals. So I create a query of the base-type (abstract) and display all animals on the frontend. But now I want to add a filter via query-string to only display a certain type-set of animals (e.g. Dog and Monkey).

What's the best practice to do so?

With TPT unfortunately I don't have no discriminator to have a handle to filter? How is this done?

Currenty I am receiving the selected types in my WebApi-Conroller via a string array, e.g:

public IActionResult(string[] types) {
    // ...
}

An idea would by to map this string array to Type and then go with .OfType. But then I have the problem that I am not able to chain OfType, because the result-set is always ZERO when chaining.

And it would be perfect that the filtering is done on the database directly, so I want to avoid to that in memory for performance reasons.

Any ideas?

entity-framework-core asp.net-web-api entity-framework c#
1 Answers

Actually you don't need access to discriminator. All inheritance models (TPH, TPT and TPC when added) use type based query conditions for filtering the base set. OfType is just one of them, in general the C# operator is T is used for filtering and as T or cast (T) for access.

There is no out of the box method for applying Type based filter, but it can be created with Expression class - the corresponding method for C# is T is Expression.TypeIs, which in combination with Expression.OrElse allows you to build dynamically the desired predicate.

For instance:

public static Expression<Func<T, bool>> MakeFilter<T>(this IEnumerable<Type> types)
{
    var parameter = Expression.Parameter(typeof(T), "e");
    var body = types.Select(type => Expression.TypeIs(parameter, type))
        .Aggregate<Expression>(Expression.OrElse);
    return Expression.Lambda<Func<T, bool>>(body, parameter);
}

and top level query helper:

public static IQueryable<T> OfTypes<T>(this IQueryable<T> source, IEnumerable<Type> types)
    => source.Where(types.MakeFilter<T>());

Applying it to your sample:

var types = new[] { typeof(Dog), typeof(Monkey) };
var query = db.Set<Animal>().OfTypes(types);

produces the following debug view expression

DbSet<Animal>()
    .Where(e => (e is Dog) || (e is Monkey))

and query for SqlServer provider:

SELECT [a].[Id], [a].[Name], CASE
    WHEN [m].[Id] IS NOT NULL THEN N'Monkey'
    WHEN [d].[Id] IS NOT NULL THEN N'Dog'
    WHEN [b].[Id] IS NOT NULL THEN N'Bear'
END AS [Discriminator]
FROM [Animals] AS [a]
LEFT JOIN [Bears] AS [b] ON [a].[Id] = [b].[Id]
LEFT JOIN [Dogs] AS [d] ON [a].[Id] = [d].[Id]
LEFT JOIN [Monkeys] AS [m] ON [a].[Id] = [m].[Id]
WHERE ([d].[Id] IS NOT NULL) OR ([m].[Id] IS NOT NULL)

It's not perfect (has room for optimizations by EF Core team - the did that for TPH with adding a way to tell the discriminator is "complete", but TPT still lacks such capability), but does the filtering database side as wanted.

user_52025630
Related