C#•7mo ago

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•7mo ago

Because All() returns true for an empty sequence, Any() will return false

LetieriOP•7mo 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•7mo 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

LetieriOP•7mo ago

thanks

Gaming

Programming

Retrieving Roles with Empty Permission Arrays in Entity Framework Core Repository