1

As you can see here I have two entities that are linked with each other in two ways.

First way is classic many to many by two ICollection and second is by entity GroupRole which specifies role of user for particular group.

The problem is that User stores his roles for every group and Group stores roles of each user for itself.

I'd like to retrieve all groups including it's users but only with particular role, unfortunately it seems like when I use Include statement and then nest inside of it Where I cannot use Group entity representation within nested statement and it throws an error

LINQ statement couldn't be translated because of 'g' within Include statement

If anyone could drop some hint how to work around this problem I would be very glad.

Thanks

var result = await groupsRepository.Query(tracking: false)
        .Include(g =>  g.Users.Where(u => u.GroupRoles.Any(r => r.Role == GroupRolesEnum.ExampleRole && r.GroupId == g.GroupId))).
        .ToListAsync();

public class Group
{
    public string Name { get; set; }
    public Guid GroupId { get; set; }

    public virtual ICollection<User> Users { get; set; }
    public virtual ICollection<GroupRole> UsersRoles { get; set; }
}

public class User
{
    public Guid UserId { get; set; }
    public string Email { get; set; }

    public ICollection<Group> Groups{ get; set; }
    public ICollection<GroupRole> GroupRoles { get; set; }
}

public class GroupRole
{
    public Guid UserId { get; set; }
    public Guid GroupId { get; set; }

    public GroupRolesEnum Role { get; set; }

    public virtual Group Group{ get; set; }
    public virtual User User { get; set; }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cyman1998
  • 73
  • 1
  • 7
  • If the navigation property is set up properly you don't need to filter groups. It is safe to remove `&& r.GroupId == g.GroupId` party. – Eldar Aug 23 '22 at 12:09
  • I might have described it unclearly, User stores GroupRoles for every existing group so If I would remove this then it will return me users of this particular group that have given ExampleRole in any existing group. I mean these two properties Groups and GroupsRoles are kind of independent because you can be part of a group but dont have any particular role – cyman1998 Aug 23 '22 at 12:14
  • `.Include` is to explain to Entity Framework which properties to eagerly load ~ https://stackoverflow.com/a/29092178/1462295 . Filtering entities should be handled elsewhere. – BurnsBA Aug 23 '22 at 13:02
  • Ok, I get you know, I am not so sure but it might be a limitation of EF. – Eldar Aug 23 '22 at 13:03
  • @BurnsBA filtered includes is a feature of EF core 5.0 onwards – Eldar Aug 23 '22 at 13:19
  • By the when i read your question i realized that I misunderstood it. You are trying to filter on UserGroups but you actually trying to do it on Users level probably that's why EF is complaining. You need to use ThenInclude and then filter the UserGroups. It might still not work as you will loose the parent group to filter. The only option left remaining is to manually joining entities – Eldar Aug 23 '22 at 18:06

0 Answers0