Support OfType<T>() within Include expressions on base-class-typed collection navigations

[John-Paul-R]

Suppose one has the following schema:

public class AppUser
{
    public long Id { get; init; }
    public ICollection<SkillBase> Skills { get; init; } = null!;
}

public abstract class SkillBase
{
    public long Id { get; set; } 
    public int SkillLevel { get; set; }
}

public class MartialSkill : SkillBase
{
    public bool HasStrike { get; set; }
}

public class MagicSkill : SkillBase
{
    public string RunicName { get; set; } = null!;
}

And wanted to quickly find information about an AppUser and all of their "Martial" skills.

Normally, I'd expect to be able to do:

long targetUserId = 5;
var user = dbCtx.AppUsers
    .Where(u => u.Id == targetUserId)
    .Include(u => u.Skills.OfType<MartialSkill>())
    .First();

This, however fails, since OfType is not in Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.SupportedFilteredIncludeOperations.

OfType<T>() is valid within Select statements, however. e.g. this would work:

long targetUserId = 5;
var userSkills = dbCtx.AppUsers
    .Where(u => u.Id == targetUserId)
    .Select(u => u.Skills.OfType<MartialSkill>())
    .First();

---

Ideally, OfType should be supported for base-typed collection navigations.

[John-Paul-R]

Full exception, for completeness's sake:

The expression 'a.GroupBases.AsQueryable().OfType()' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

[John-Paul-R]

This was also referenced here #3910 (comment), where the reporter "solved" the issue by materializing first, then filtering in memory. This is far from ideal, however, especially when dealing with either of:

1. lots of rows
2. a TPC schema, where the generated query can be significantly simplified/optimized by an OfType<TLeaf>()

[milosloub]

You can handle this by rewrite line:

.Include(u => u.Skills.OfType<MartialSkill>())
To
.Include(u => u.Skills.Where(s => s is MartialSkill))

This will generate SQL with filtered join on Discriminator (TPH, TPT...I didnt't try TPC)

[John-Paul-R]

Aha! That works like a charm on the TPC schema I'm experimenting with. Many thanks for sharing! That unsticks me in my particular scenario.

It strikes me that supporting OfType inside Include might be valuable (Nice to be able to use the same operations in Select and Include), so I'll not immediately close this.

If a maintainer feels this is resolved, though, no major gripes from me.

---

E: For slightly more context, when used on a TPC schema, the suggested filter

.Include(u => u.Skills.Where(s => s is MartialSkill))

correctly generates a query that targets only the martial_skill table (as opposed to filtering after UNION ALL-ing the martial_skill and magic_skill tables.)

[ajcvickers]

Note from triage: consider supporting the OfType syntax.

[sk-shahnawaz]

You can handle this by rewrite line:

.Include(u => u.Skills.OfType<MartialSkill>()) To .Include(u => u.Skills.Where(s => s is MartialSkill))

This will generate SQL with filtered join on Discriminator (TPH, TPT...I didnt't try TPC)

This solution is working in TPC as well, thanks!