How to access a property from a base class in a LINQ query with two levels of inheritance in EF Core

Question

We use SQL Server and EF Core to map our C# objects to the database. The object model has two levels of abstraction as follows:

public abstract class Animal
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Parrot : Animal
{
    public bool CanSpeak { get; set; }
}

public abstract class Mammal : Animal
{
    public bool HasPouch { get; set; }
}

public class Dog : Mammal
{
    public bool HasTail { get; set; }
}

public class Cat : Mammal
{
    public double MaxHeight { get; set; }
}

public class Horse : Mammal
{
    public string Breed { get; set; }
    public int RacingSpeed { get; set; }
}

Main parts of the DbContext inherited class:

public DbSet<Animal> Animals { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Animal>()
        .ToTable("Animals")
        .HasDiscriminator<string>("AnimalType")
        .HasValue<Parrot>("Parrot")
        .HasValue<Dog>("Dog")
        .HasValue<Cat>("Cat")
        .HasValue<Horse>("Horse");

    // Additional configurations if needed
}

Now I'm going to show 3 types of LINQ queries out of which the first two work but the last one causes a runtime error (IvalidOperationException - something like: The LINQ expression 'DbSet<Animal>().OfType<Mammal>()' could not be translated).

This one queries a property from the very base abstract class of the hierarchy which is used in EF configuration for the Animals table:

List<Animal> list = context.Animals
    .Where(x => x.Name == "Max")
    .ToList();

No problem with it.

This one needs to query a property from a concrete class and works fine as well:

List<Animal> catsUnder50cm = context.Animals
    .Where(x => x is Cat && (x as Cat).MaxHeight <= 50)
    .ToList();

However, with this one, although it compiles OK, I get the runtime error mentioned above:

List<Animal> marsupials = context.Animals
    .Where(x => x is Mammal && (x as Mammal).HasPouch)
    .ToList();

I think this is due to the fact Mammal is an abstract class. I could rewrite it this way:

List<Animal> marsupials2 = context.Animals
    .Where(x => (x is Dog && (x as Dog).HasPouch) || (x is Cat && (x as Cat).HasPouch) || (x is Horse && (x as Horse).HasPouch) )
    .ToList();

But as you can see, the query gets quite complex and ugly at the same time. And the more classes inherit from Mammal the more complex the query is. Which is exactly our case.

The question is:

Is it possible to use a property of the "middle" abstract class which is not part of the model in LINQ queries?

If not, can we somehow "add" that abstract class to the EF data model?

The Mammal data type should map to the same table of Animals. Is that possible?

Answer 1

εὕρηκᾰ

I think I found a solution. I don't like it that much though.

As a workaround, I need to map the "middle" abstract class to the same table. And map its new property (HasPouch) to the same column.

Basically, you shall do something like this in your DbContext configuration:

EntityTypeBuilder<Mammal> mammalBuilder = modelBuilder.Entity<Mammal>();
mammalBuilder.ToTable("Animals");
mammalBuilder.Property(x => x.HasPouch).HasColumnName("HasPouch");