Filtering on Include reverted if I perform Select afterwards in EF Core-CodePudding

I am trying to use Filtered Includes in EF Core and I have encountered an issue which I can't seem to pinpoint to a specific cause.

My query looks something like this:

context.Users.Include(u=>u.UserRoles.Where(r => r.Role.Category == 3))
             .ThenInclude(r=>r.Role).Where(u => u.userId == currentUserId)
             .Select(u=> new UserDTO()
{
    UserDisplayName= u.Name,
    ListOfRoles = String.Join(",", u.UserRoles.Select(u => u.Role.DisplayName))
}).FirstOrDefaultAsync();

If I omit the Select part from the query and check the object, it is populated only with the appropriate UserRoles, the ones belonging to the category 3, but upon checking the result of this Select, it contains also the Roles that belong to a different category, concatenated into the ListOfRoles.

I would be grateful if anyone had any ideas what could be causing this.

Thank you

CodePudding user response：

Include only applies where you are returning the entity. When you use projection with Select you need to filter the data within the Select expression:

context.Users
    .Where(u => u.userId == currentUserId)
    .Select(u=> new UserDTO()
    {
        UserDisplayName= u.Name,
        ListOfRoles = String.Join(",", u.UserRoles
            .Where(ur => ur.Role.Catecory == 3)
            .Select(ur => ur.Role.DisplayName))
    }).SingleOrDefaultAsync();

I believe that String.Join would require client-side evaluation in EF Core. This can lead to unexpected data being loaded. A recommendation to avoid this would be to perform the concatination within the DTO so that the Linq query loads the raw data and can translate that to SQL efficiently:

context.Users
    .Where(u => u.userId == currentUserId)
    .Select(u=> new UserDTO()
    {
        UserDisplayName= u.Name,
        Roles = u.UserRoles
            .Where(ur => ur.Role.Catecory == 3)
            .Select(ur => ur.Role.DisplayName))
            .ToList();
    }).SingleOrDefaultAsync();

Where in the DTO you would have:

[Serializable]
public class UserDTO
{
    public string UserDisplayName { get; set; }
    public IList<string> Roles { get; set; } = new List<string>();
    public string ListOfRoles
    {
        get { return string.Join(",", Roles); }
    }
}

This ensures the query can run efficiently and translate fully to SQL, then moves the formatting to the DTO.

CodePudding user response：

Include will work only if you select entities directly. Once you do the projection (Select for example) Include is ignored. You can try to apply category filtering during the concatenation part:

context.Users
    .Where(u => u.userId == currentUserId)
    .Select(u=> new UserDTO()
    {
        UserDisplayName= u.Name,
        ListOfRoles = String.Join(",", u.UserRoles.Where(r => r.Role.Category == 3).Select(u => u.Role.DisplayName))
    })
    .FirstOrDefaultAsync();