Use Linq Where on child entity in lambda expression?

Question

I have this model:

public class User
{
    public int Id {get; set;}
    public string Name {get; set;}
    public virtual ICollection<UserProperty> Properties {get; set;}
}

public class UserProperty
{
    public int Id {get; set;}
    public int UserId {get; set;}
    public int PropertyId {get; set;}
    public virtual User User {get; set;}
    public virtual Property Property {get; set;}
}

public class Property
{
    public int Id {get; set;}
    public string Name {get; set;}
    public bool IsActive {get; set;}
}

I have repository method:

public virtual IQueryable<User> Get(Expression<Func<User, bool>> predicate, params Expression<Func<User, object>>[] include)
{
    var set = include.Aggregate<Expression<Func<User, object>>, IQueryable<User>>
                (dbSet, (current, expression) => current.Include(expression));

    return set.Where(predicate);
}

I'm trying to get a list of user properties where the properties' IsActive is true, so I'm doing:

public IEnumerable<UserProperty> GetSearches(int userId)
{
    return userRepository.Get(x => x.Id == userId, 
                              x => x.Properties.Where(p => p.Property.IsActive).Select(p => p.Property)).Properties;
}

However, I'm getting this exception:

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path

What am I doing wrong?

EDIT

The following alternative works:

return userRepository.Get(x => x.Id == userId,
                          x => x.Properties.Select(p => p.Property)).Properties.Where(p => p.Property.IsActive);

However, the where clause is not included in the SQL statement to the db but executed after all records have been retrieved.

I would like to limit the number of records retrieved directly in the db.

Maybe this will help http://stackoverflow.com/questions/16798796/ef-include-with-where-clause — Marcin J, Apr 22 '15 at 08:20

Omar · Accepted Answer · 2015-04-22T09:12:42.760

1

I'd do something simpler:

public IEnumerable<UserProperty> GetSearches(int userId)
{
    return userRepository.Where(x => x.Id == userId).Select(x => x.Properties.Where(p => p.IsActive)).Single(); //I assumed userId is unique
}

If you need to group more user's properties then use a GroupBy.

edited Apr 22 '15 at 09:12

answered Apr 22 '15 at 08:20

Omar

16,329
10
48
66

I would like to use the repository method above. UserRepository is not IDbSet but a class that inherits from a generic repository. – Ivan-Mark Debono Apr 22 '15 at 08:27
Well then you should be able to get a table from the repo as IEnumerable or IQueryable. – Omar Apr 22 '15 at 09:01
`Select(p.Properties.Where(x => x.IsActive))` doesn't compile. – Ivan-Mark Debono Apr 22 '15 at 09:09

Use Linq Where on child entity in lambda expression?

1 Answers1