I’ve noticed that when using FindAsync to load an aggregate that envolves several tables, the generated SQL tries to order the returned result by each of the entities’ ID fields. For instance, suppose you have this mapping:

public sealed class GuiaAbateTypeConfiguration: IEntityTypeConfiguration<GuiaAbate> {
    public void Configure(EntityTypeBuilder<GuiaAbate> builder) {
        builder.ToTable("GuiaAbate");

        // other mappings omitted
        
        builder.HasMany(g => g.Equipamentos)
               .WithMany( )
               .UsingEntity(
                   "GuiaAbateEquipamento",
                   r => r.HasOne(typeof( Equipamento )).WithMany( ).HasForeignKey("IdEquipamento").HasPrincipalKey(nameof(Equipamento.Id)),
                   l => l.HasOne(typeof( GuiaAbate )).WithMany( ).HasForeignKey("IdGuiaAbate").HasPrincipalKey(nameof(GuiaAbate.Id)));
        builder.Navigation(g => g.Equipamentos).AutoInclude( );
    }
}

Equipamento itself has several relationships with other entities/tables.

Here’s the generated SQL when I try to load a GuiaAbate by calling the FindAsync method:

Executed DbCommand (35ms) [Parameters=[@__p_0='1'], CommandType='Text', CommandTimeout='30']
      SELECT [t].[IdGuiaAbate], [t].[Data], [t].[Login], [t4].[IdEquipamento], ...
      FROM (
          SELECT TOP(1) [g].[IdGuiaAbate], [g].[Data], [g].[Login]
          FROM [GuiaAbate] AS [g]
          WHERE [g].[IdGuiaAbate] = @__p_0
      ) AS [t]
      LEFT JOIN (
          SELECT [g0].[IdEquipamento], [g0].[IdGuiaAbate], [t0].[IdEquipamento] AS [IdEquipamento0], ...
          FROM [GuiaAbateEquipamento] AS [g0]
          INNER JOIN (
              SELECT [e].[IdEquipamento], [e].[Data], [e].[DataAquisicao], [e].[DataFimGarantia], ...
              FROM [Equipamento] AS [e]
              LEFT JOIN [Computador] AS [c] ON [e].[IdEquipamento] = [c].[IdEquipamento]
              LEFT JOIN [EquipamentoActivo] AS [e0] ON [e].[IdEquipamento] = [e0].[IdEquipamento]
              LEFT JOIN ...
          ) AS [t0] ON [g0].[IdEquipamento] = [t0].[IdEquipamento]
          LEFT JOIN [Fabricante] AS [f] ON [t0].[IdFabricante] = [f].[IdFabricante]
          LEFT JOIN .....
      ) AS [t4] ON [t].[IdGuiaAbate] = [t4].[IdGuiaAbate]
      ORDER BY [t].[IdGuiaAbate], [t4].[IdEquipamento], [t4].[IdGuiaAbate], [t4].[IdEquipamento0], [t4].[IdFabricante0], [t4].[IdFornecedor0], [t4].[IdFuncionarios], [t4].[IdLocalTrabalho0], [t4].[CodUnidadeOrganica], [t4].[CodUnidadeOrganica0], [t4].[IdLocalTrabalho1], [t4].[CodUnidadeOrganica1], [t4].[IdTipoComputador0], [t4].[IdTipoEquipamentoActivo0], [t4].[IdTipoEquipamento0], [t4].[IdTipoImpressora0], [t4].[IdContactosFornecedores], [t4].[IdContactosFuncionarios]

Anyone knows if this behavior can be controlled? In my case, ordering by IdGuiaAbate would be enough…

thanks.