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

RRailway

•Created by aloussase on 3/18/2024 in #✋｜help

Getting "Layer already exists" for project built with docker

My Docker image is working as expected locally, but it seems to be stuck on an older version on Railway. The build logs look fine, but when publishing the image there are a bunch of layer already exists errors. My project id is: b74571d8-e862-4ac2-ad76-e19a69371a8e

53 replies

Gaming

Programming