aloussase Posts - Answer Overflow

aloussase

Explore posts from servers

•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

Gaming

Programming