Ef Core filtering included query using uow and repository

Mahfoud Bouabdallah 26 Reputation points
2022-05-10T08:34:39.937+00:00

I started creating a role-based security system in my WinForm application so I began with Form navigation (Permission Navigation) and this is my entity's

public partial class User
{
    public User()
    {
        UsersToRoles = new HashSet<UsersToRole>();
    }

    public string Login { get; set; } = null!;
    public string PasswordUser { get; set; } = null!;
    public string? FullName { get; set; }
    public string? Email { get; set; }
    public int Id { get; set; }

    public virtual ICollection<UsersToRole> UsersToRoles { get; set; }
}

public partial class Role
{
    public Role()
    {
        UsersToRoles = new HashSet<UsersToRole>();
        PermissionNavigations = new HashSet<PermissionNavigation>();
    }

    public int Id { get; set; }
    public string Name { get; set; } = null!;

    public virtual ICollection<UsersToRole> UsersToRoles { get; set; }
    public virtual ICollection<PermissionNavigation> PermissionNavigations { get; set; }
}

public partial class UsersToRole
{
    public int Id { get; set; }
    public int IdUser { get; set; }
    public int IdRole { get; set; }

    public virtual Role IdRoleNavigation { get; set; } = null!;
    public virtual User IdUserNavigation { get; set; } = null!;
}

public partial class Navigation
{
    public Navigation()
    {
        PermissionNavigations = new HashSet<PermissionNavigation>();
    }

    public int Id { get; set; }
    public string Page { get; set; } = null!;
    public string Forms { get; set; } = null!;

    public virtual ICollection<PermissionNavigation> PermissionNavigations { get; set; }
}

public partial class PermissionNavigation
{
    public int Id { get; set; }
    public int IdRole { get; set; }
    public int IdNavigation { get; set; }

    public virtual Navigation IdNavigationNavigation { get; set; } = null!;
    public virtual Role IdRoleNavigation { get; set; } = null!;
}

This is my geniric GetAllIncluding method

public async Task<IEnumerable<T>> GetAllIncluding(params Expression<Func<T, object>>[] includeProperties)
    {
        try
        {
            IQueryable<T> query = dbSet;
            foreach (Expression<Func<T, object>> includeProperty in includeProperties)
            {
                query = query.Include<T, object>(includeProperty);
            }
            return await query.ToListAsync();
        }
        catch (Exception ex)
        {
            throw new Exception($"{nameof(GetAllIncluding)} properties could not be included properly: {ex.Message}");
        }
    }

And this is how I use it in my PermissionNavigationService

public async Task<IEnumerable<PermissionNavigationDto?>> 
GetAllPermissionNavigationDetailsByUserAsync(int idUser)
    {
        var permissionNavigation = await unitOfWork.PermissionNavigations.GetAllIncluding(
                           x => x.IdNavigationNavigation,
                           x => x.IdRoleNavigation,
                           x => x.IdRoleNavigation.UsersToRoles.Where(x=>x.IdUser== idUser));

        return mapper.Map<IEnumerable<PermissionNavigationDto?>>(permissionNavigation);
    }

I know that this line of code only filtering UsersToRoles entity not PermissionNavigation entity

x => x.IdRoleNavigation.UsersToRoles.Where(x=>x.IdUser== idUser)

The question is: What can be done to get all Permission Navigation related to specific user
I am looking to something's like this but in ef core

     SELECT PermissionNavigation.[Id]
              ,PermissionNavigation.[IdRole]
              ,Roles.Name
              ,Navigation.Forms
              ,[IdNavigation]
              ,UsersToRoles.IdUser
          FROM [SIM].[dbo].[PermissionNavigation]
          INNER JOIN Roles on Roles.Id=IdRole
          INNER JOIN Navigation on Navigation.id=IdNavigation
          INNER JOIN UsersToRoles on UsersToRoles.IdRole=PermissionNavigation.[IdRole]
          WHERE UsersToRoles.IdUser=@IdUser
Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,869 questions
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
721 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,278 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,605 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Mahfoud Bouabdallah 26 Reputation points
    2022-05-16T13:44:23.807+00:00

    I appreciate all the help I received.
    I decided to go this way:
    When the user has successfully logged in, I catch the Id then I make a call to get all roles related to that user after that I make another call to get all permission navigation using role Id that I got earlier.

        List<PermissionNavigationDto> navigationDtos = new();
        var userRoles = await userToRoleService.GetUserRolesAsync(LoginUserDetails.Id);          
        foreach (var role in userRoles)
        {
            var navigation = await permissionNavigationService.GetPermissionNavigationByRoleIdAsync(role.IdRole);
            navigationDtos.AddRange(navigation);
        }
    
    0 comments No comments