Filtering with EF Core 2.1 inheritance

user_1398649 0
Viewed 5150

I'm trying to find a way to filter my results in EF Core 2.1, when using inherited objects.

I've got a base model and several inherited classes (but I've just included one): 

public class Like {
    public int Id { get; set; }
    public LikeType LikeType { get; set; }
}

public class DocumentLike : Like {
    [ForeignKey(nameof(Document))]
    public int DocumentId { get; set; }
    public virtual Document Document { get; set; }
}

LikeType is an enum which is defined as the discriminator in the dbcontext. Every Document has a boolean property .IsCurrent.

To get all items from the database, I'm using a query like:

IQueryable<Like> query = _context.Set<Like>()
    .Include(x => x.Owner)
    .Include(x => (x as DocumentLike).Document.DocumentType)
    .Include(x => (x as ProductLike).Product)
    .Include(x => (x as TrainingLike).Training)

This works beautifully, and returns all objects with the included sub-objects without any error. What I'm trying to do, is to get all items from the database for which the linked document has .IsCurrent == true. I've tried adding the following to the query above, but both result in an exception:

.Where(x => (x as DocumentLike).Document.IsCurrent == true)

And:

.Where(x => x.LikeType == LikeType.Document ? (x as DocumentLike).Document.IsCurrent == true : true)

The exception, which is thrown when I'm executing the query:

NullReferenceException: Object reference not set to an instance of an object.
    lambda_method(Closure , TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<Like, ApplicationUser>, Organisation>, Training>, Product>, Platform>, NewsItem>, Event>, Document>, DocumentType>, Course>, CourseType>, ApplicationUser> )
    System.Linq.Utilities+<>c__DisplayClass1_0<TSource>.<CombinePredicates>b__0(TSource x)
    System.Linq.Enumerable+WhereSelectEnumerableIterator<TSource, TResult>.MoveNext()
    Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities<TOut, TIn>(IEnumerable<TOut> results, QueryContext queryContext, IList<EntityTrackingInfo> entityTrackingInfos, IList<Func<TIn, object>> entityAccessors)+MoveNext()
    Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+ExceptionInterceptor<T>+EnumeratorExceptionInterceptor.MoveNext()
    System.Collections.Generic.List<T>.AddEnumerable(IEnumerable<T> enumerable)
    System.Linq.Enumerable.ToList<TSource>(IEnumerable<TSource> source)

Is there a way to do this?

UPDATE: To clarify: I'm looking to get a single query that returns all Like-objects from the database, regardless of their (sub)types. In case the subtype is DocumentLike, I only want the objects that are linked to a document that has .IsCurrent == true.

entity-framework-core ef-core-2.1 .net-core c#
3 Answers

The trick was to edit the predicate a bit, like this:

.Where(x => !(x is DocumentLike) || ((DocumentLike)x).Document.IsCurrent == true)

Thanks to Panagiotis Kanavos for the suggestion.

user_13986490

I had a similar problem with a multi-layer hierarchy of classes where using .OfType<>() was causing a "premature" (in my opinion) trip to the database to fetch all of the data so it could do the filtering in memory, which is undesirable!

This illustrates my hierarchy:

public abstract class BaseSetting {}
public abstract class AccountSetting : BaseSetting {}
public abstract class UserSetting : BaseSetting {}

public class AccountSettingA : AccountSetting {}
public class AccountSettingB : AccountSetting {}
public class UserSettingA : UserSetting {}
public class UserSettingB : UserSetting {}

And this is the set up for the DbContext:

public class DataContext : DbContext
{
  public virtual DbSet<BaseSetting> Settings { get; set; }

  protected override void OnModelCreating(ModelBuilder builder)
  {
    base.OnModelCreating(builder);

    builder.Entity<BaseSetting>(e =>
    {
        e.ToTable("Settings");
        e.HasDiscriminator<string>("Type");
    });
  }
}

Then I would try and get all the settings for a single account like this:

AccountSetting[] settings = context.Settings
    .OfType<AccountSetting>()
    .Where(s => s.Account.Id == accountId)
    .ToArray();

This results in a SQL query something like this:

SELECT *
FROM [Settings] AS [s0]
WHERE [s0].[Type] IN (N'AccountSettingA',N'AccountSettingB',N'UserSettingA',N'UserSettingB')

just before is throws a NullReferenceException in the .Where(s => s.Account.Id == accountId) bit of the query because Account is null. This could probably be "fixed" by adding a .Include(...) to the query to pull the Account through too, but that will just add to the excessive amount of data we're getting from the database. (It should be noted that if you configure the context to throw errors when trying to evaluate on the client as per @PanagiotisKanavos's comment on the original question, then you will get a QueryClientEvaluationWarning here instead).

The solution (at least for me) was to add this to the OnModelCreating method in my DbContext:

typeof(BaseSetting).Assembly.GetTypes()
  .Where(t => t != typeof(BaseSetting) && typeof(BaseSetting).IsAssignableFrom(t))
  .Each(s => builder.Entity(s).HasBaseType(s.BaseType));

This will go through all my different settings classes (that inherit from BaseSetting) and tell Entity Framework that their base type is their Type.BaseType. I would have thought that EF could work this out on it's own, but after doing this I get SQL like this (and no QueryClientEvaluationWarning exceptions!):

SELECT *
FROM [Settings] as [a]
INNER JOIN [Accounts] AS [a.Account] ON [a].[AccountId] = [a.Account].[Id]
WHERE ([a].[Type] IN (N'AccountSettingA',N'AccountSettingB',N'UserSettingA',N'UserSettingB')
AND ([a.Account].[Id] = @__accountId)

Which obviously only returns the account settings for the account I'm interested in, rather than all the account settings and all of the user settings like it was before.

user_7236450
user_49637290
Related