Entity Framework Core LINQ Class Property/Methods can't use Included Property

user_1001328 0
Viewed 289

I have a User class with navigation property Settings and a readonly Property IsVisibleInApp.

At present this code works:

var users = context.Users.Include(x => x.Settings)
   .Where(x => x.Settings.IsApproved && x.Settings.IsAvailable && x.UserType == 2);

But I have to use these checks frequently, so I try to use a property and it gets Settings as null at runtime.

Here is my code.

public bool IsVisibleInApp
{
  get
  {
    return this.Settings.IsApproved && this.Settings.IsAvailable && this.UserType == 2;
  }
}

...
...

void SomeMethod(){
    var users = context.Users.Include(x => x.Settings)
       .Where(x => x.IsVisibleInApp).ToList();

I can see that the foreign key this.SettingsID is a valid value. I guess properties/methods can't be used in Where clauses on DBSets as they get converted to SQL? But surely there is a way around this... Or do I have to do a .ToList() first and then use a further .Where call, but that would mean pulling all the rows from the database which seems like a lot of extra work...

entity-framework-core entity-framework linq c#
1 Answers

You cannot do it the way you want (EF will not go inside your property getter and look what you are doing there), but you can reduce code repetition by wrapping that in static expression:

public static Expression<Func<User, bool>> IsVisibleInAppExpression = x =>
    x.Settings.IsApproved && x.Settings.IsAvailable && x.UserType == 2;

And then use like this:

var users = context.Users.Include(x => x.Settings)
   .Where(User.IsVisibleInAppExpression).ToList();

Or by creating extension method:

public static class Extensions {
    public static IQueryable<User> VisibleInAppOnly(this IQueryable<User> query) 
    {
         return query.Where(x => 
             x.Settings.IsApproved && x.Settings.IsAvailable && x.UserType == 2);
    }
}

And using like this:

var users = context.Users.Include(x => x.Settings).VisibleInAppOnly();
user_53117350
Related