C
C#•8mo ago
Letieri

Retrieving Roles with Empty Permission Arrays in Entity Framework Core Repository

public async Task<IEnumerable<Role>> GetAllWithPermissions()
{
    return await _context.Roles
        .AsNoTracking()
            .Include(r => r.RolePermissions)
                .ThenInclude(rp => rp.Permission)
        .Where(r => r.DeletedAt == null
            && r.RolePermissions.Any(rp => rp.DeletedAt == null && rp.Permission != null
                && rp.Permission.DeletedAt == null))
        .ToListAsync();
}
public async Task<IEnumerable<Role>> GetAllWithPermissions()
{
    return await _context.Roles
        .AsNoTracking()
            .Include(r => r.RolePermissions)
                .ThenInclude(rp => rp.Permission)
        .Where(r => r.DeletedAt == null
            && r.RolePermissions.Any(rp => rp.DeletedAt == null && rp.Permission != null
                && rp.Permission.DeletedAt == null))
        .ToListAsync();
}
When I use this repository it returns the records correctly, except for the records where the Permissions array is empty, it should also return these cases. When I use 
All.()
All.()
instead of 
Any.()
Any.()
it returns me those in which Permissions is empty but it does not return the roles in which I deleted any RolePermissions records.
4 Replies
Keswiik
Keswiik•8mo ago
Because All() returns true for an empty sequence, Any() will return false
Letieri
LetieriOP•8mo ago
so how can I do it to return the correct values?? Github Copilot gave me this suggestion. It works, but it's very big and has some null reference alerts: 
public async Task<IEnumerable<Role>> GetAllWithPermissions()
{
    return await _context.Roles
        .AsNoTracking()
            .Include(r => r.RolePermissions)
                .ThenInclude(rp => rp.Permission)
        .Where(r => r.DeletedAt == null)
        .Select(r => new Role
        {
            Uuid = r.Uuid,
            Name = r.Name,
            Description = r.Description,
            CreatedAt = r.CreatedAt,
            UpdatedAt = r.UpdatedAt,
            RolePermissions = r.RolePermissions
                .Where(rp => rp.DeletedAt == null && rp.Permission != null
                                       && rp.Permission.DeletedAt == null)
                .Select(rp => new RolePermission
                {
                    Uuid = rp.Uuid,
                    RoleUuid = rp.RoleUuid,
                    PermissionUuid = rp.PermissionUuid,
                    CreatedAt = rp.CreatedAt,
                    UpdatedAt = rp.UpdatedAt,
                    DeletedAt = rp.DeletedAt,
                    Permission = new Permission
                    {
                        Uuid = rp.Permission.Uuid,
                        Name = rp.Permission.Name,
                        Description = rp.Permission.Description,
                        CreatedAt = rp.Permission.CreatedAt,
                        UpdatedAt = rp.Permission.UpdatedAt,
                        DeletedAt = rp.Permission.DeletedAt
                    }
                }).ToList()
        })
        .ToListAsync();
}
public async Task<IEnumerable<Role>> GetAllWithPermissions()
{
    return await _context.Roles
        .AsNoTracking()
            .Include(r => r.RolePermissions)
                .ThenInclude(rp => rp.Permission)
        .Where(r => r.DeletedAt == null)
        .Select(r => new Role
        {
            Uuid = r.Uuid,
            Name = r.Name,
            Description = r.Description,
            CreatedAt = r.CreatedAt,
            UpdatedAt = r.UpdatedAt,
            RolePermissions = r.RolePermissions
                .Where(rp => rp.DeletedAt == null && rp.Permission != null
                                       && rp.Permission.DeletedAt == null)
                .Select(rp => new RolePermission
                {
                    Uuid = rp.Uuid,
                    RoleUuid = rp.RoleUuid,
                    PermissionUuid = rp.PermissionUuid,
                    CreatedAt = rp.CreatedAt,
                    UpdatedAt = rp.UpdatedAt,
                    DeletedAt = rp.DeletedAt,
                    Permission = new Permission
                    {
                        Uuid = rp.Permission.Uuid,
                        Name = rp.Permission.Name,
                        Description = rp.Permission.Description,
                        CreatedAt = rp.Permission.CreatedAt,
                        UpdatedAt = rp.Permission.UpdatedAt,
                        DeletedAt = rp.Permission.DeletedAt
                    }
                }).ToList()
        })
        .ToListAsync();
}
Keswiik
Keswiik•8mo ago
why not modify your original query and do (!r.RolePermissions.Any() || <your_original_Any()_filter>) in theory that should match any row with empty RolePermissions and, if not empty, match against your filter
Letieri
LetieriOP•8mo ago
thanks

Did you find this page helpful?