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
I have one repository per table
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);
}
}
CodePudding user response:
It's supposed to work like/you're supposed to set it up so you can work like:
public async Task<IActionResult> GetRolesCompletos() =>
context.PermisoEnRols.Select(per =>
new {
PermisoEnRol = per,
per.Permiso,
per.Rol
}
.ToList();
That's it; EF will do the joins for you based on seeing you use related entities per.Permiso
and per.Rol
in the Select. It makes for a slightly confusing graph because the anonymous type's PermisoEnRol property has Permiso and Rol properties that are repeated on the anonymous type
Note that this is exposing your db entities to the caller; normally we would have some other object for the front end's use and map the db entities to it