lundi 4 avril 2022

What is the best way to join multiple tables with Entity framework?

I'm new to c# development using ASP.NET Core and Entity Framework, and I'm having trouble figuring out the correct place to write queries. I've read that writing them in a controller isn't good practice but I implemented the UoW pattern and repository pattern in a generic way so the only place that I've been able to join multiple tables is in the Rol controller.

Below is an image of the table schemes

Table Schemes

I have one repository per table

Solution Explorer

The Rol controller

[Route("api/roles")]
[ApiController]
public class RolController : ControllerBase
{
    private readonly IMapper _mapper;
    private readonly IConfiguration _config;
    private readonly IUnitOfWork _unitOfWork;

    public RolController(IUnitOfWork unitOfWork,IMapper mapper, IConfiguration config)
    {
        _unitOfWork = unitOfWork;
        _mapper = mapper;
        _config = config;
    }

    [HttpGet("GetrolesCompletos")]
    public async Task<IActionResult> GetRolesCompletos()
    {
        IQueryable<Rol> rol = await _unitOfWork.Rol.GetAll();
        IQueryable<PermisoEnRol> permisosenrol = await _unitOfWork.PermisosEnRol.GetAll();
        IQueryable<Permiso> permiso = await _unitOfWork.Permiso.GetAll();
        var query = from r in rol
                    join pr in permisosenrol on r.IdRol equals pr.Idrol
                    join p in permiso on pr.IdPermiso equals p.IdPermiso
                    select new
                    {
                        rol = r,
                        permisosenrol = pr,
                        permiso = p
                    };
        var result = query.ToList();
        return Ok(result);
    }
}

Aucun commentaire:

Enregistrer un commentaire