aloussase
aloussase
Explore posts from servers
CC#
Created by aloussase on 7/26/2024 in #help
✅ EFCore generates unwieldy query
Hi all! I have the following EF code:
var user = await _db.Users
.AsNoTracking()
.TagWith("Get user with permissions")
.Where(x => x.Usuario == request.Username)
.Select(x => new User
{
Id = x.Id,
Nombres = x.Nombres,
Apellidos = x.Apellidos,
Usuario = x.Usuario,
Permissions = x.Roles
.SelectMany(role => role.Permissions)
.Select(permission => _mapper.Map<Permission>(permission))
})
.FirstAsync();
var user = await _db.Users
.AsNoTracking()
.TagWith("Get user with permissions")
.Where(x => x.Usuario == request.Username)
.Select(x => new User
{
Id = x.Id,
Nombres = x.Nombres,
Apellidos = x.Apellidos,
Usuario = x.Usuario,
Permissions = x.Roles
.SelectMany(role => role.Permissions)
.Select(permission => _mapper.Map<Permission>(permission))
})
.FirstAsync();
Which is generating this SQL:
SELECT t0.IDKEEPUSUARIO, t0.NOMBRES, t0.APELLIDOS, t0.USUARIO, t4.IDKEEPPERMISO, t4.MODULO, t4.NOMBRE, t4.IDKEEPROL, t4.IDKEEPUSUARIO, t4.IDKEEPROL0, t4.IDKEEPPERMISO0, t4.IDKEEPROL1
FROM (
SELECT t.IDKEEPUSUARIO, t.NOMBRES, t.APELLIDOS, t.USUARIO
FROM EKEEP.T_KEEP_USUARIO AS t
WHERE t.USUARIO = @__request_Username_0 FETCH FIRST 1 ROWS ONLY
) AS t0
LEFT JOIN (
SELECT t3.IDKEEPPERMISO, t3.MODULO, t3.NOMBRE, t1.IDKEEPROL, t1.IDKEEPUSUARIO, t2.IDKEEPROL AS IDKEEPROL0, t3.IDKEEPPERMISO0, t3.IDKEEPROL AS IDKEEPROL1
FROM EKEEP.T_KEEP_USUARIOROLES AS t1
INNER JOIN EKEEP.T_KEEP_ROL AS t2 ON t1.IDKEEPROL = t2.IDKEEPROL
INNER JOIN (
SELECT t6.IDKEEPPERMISO, t6.MODULO, t6.NOMBRE, t5.IDKEEPPERMISO AS IDKEEPPERMISO0, t5.IDKEEPROL
FROM EKEEP.T_KEEP_ROLPERMISOS AS t5
INNER JOIN EKEEP.T_KEEP_PERMISO AS t6 ON t5.IDKEEPPERMISO = t6.IDKEEPPERMISO
) AS t3 ON t2.IDKEEPROL = t3.IDKEEPROL
) AS t4 ON t0.IDKEEPUSUARIO = t4.IDKEEPUSUARIO
ORDER BY t0.IDKEEPUSUARIO, t4.IDKEEPROL, t4.IDKEEPUSUARIO, t4.IDKEEPROL0, t4.IDKEEPPERMISO0, t4.IDKEEPROL1
SELECT t0.IDKEEPUSUARIO, t0.NOMBRES, t0.APELLIDOS, t0.USUARIO, t4.IDKEEPPERMISO, t4.MODULO, t4.NOMBRE, t4.IDKEEPROL, t4.IDKEEPUSUARIO, t4.IDKEEPROL0, t4.IDKEEPPERMISO0, t4.IDKEEPROL1
FROM (
SELECT t.IDKEEPUSUARIO, t.NOMBRES, t.APELLIDOS, t.USUARIO
FROM EKEEP.T_KEEP_USUARIO AS t
WHERE t.USUARIO = @__request_Username_0 FETCH FIRST 1 ROWS ONLY
) AS t0
LEFT JOIN (
SELECT t3.IDKEEPPERMISO, t3.MODULO, t3.NOMBRE, t1.IDKEEPROL, t1.IDKEEPUSUARIO, t2.IDKEEPROL AS IDKEEPROL0, t3.IDKEEPPERMISO0, t3.IDKEEPROL AS IDKEEPROL1
FROM EKEEP.T_KEEP_USUARIOROLES AS t1
INNER JOIN EKEEP.T_KEEP_ROL AS t2 ON t1.IDKEEPROL = t2.IDKEEPROL
INNER JOIN (
SELECT t6.IDKEEPPERMISO, t6.MODULO, t6.NOMBRE, t5.IDKEEPPERMISO AS IDKEEPPERMISO0, t5.IDKEEPROL
FROM EKEEP.T_KEEP_ROLPERMISOS AS t5
INNER JOIN EKEEP.T_KEEP_PERMISO AS t6 ON t5.IDKEEPPERMISO = t6.IDKEEPPERMISO
) AS t3 ON t2.IDKEEPROL = t3.IDKEEPROL
) AS t4 ON t0.IDKEEPUSUARIO = t4.IDKEEPUSUARIO
ORDER BY t0.IDKEEPUSUARIO, t4.IDKEEPROL, t4.IDKEEPUSUARIO, t4.IDKEEPROL0, t4.IDKEEPPERMISO0, t4.IDKEEPROL1
I ain't no SQL expert, but that query seems overcomplicated to me. Is there something I can do to optimize it?
2 replies