AspNetCoreOData generates duplicate JOIN statement if expanding an IQueryable that already has a join

This issue was reported by an internal customer.

They have separate classes to represent DB entities and API-visible entities:

We are experiencing major performance regression in the Expand query when migrating our code to latest ASP.Net core with latest OData 8.0.6.

I’ve minimized my code to create a simple repro:

Defined 2 entities, Incident and Alert:

public class Incident
    {
        [Key]
        public long Id { get; set; }
 
        public IEnumerable<Alert> Alerts { get; set; }
    }
 
public class Alert
    {
        [Key]
        public string Id { get; set; }
    }

Defined their DB entities:

[Table("RawIncidents")]
    public class IncidentDbEntity
    {
        [Key]
        [Column]
        public long IncidentId { get; set; }
 
        public List<AlertDbEntity> Alerts { get; set; }
    }
 
 
[Table("AlertsForPublicApi")]
    public class AlertDbEntity
    {
        [Key]
        [Column(TypeName = "varchar")]
        public string AlertId { get; set; }
 
        [Column]
        [ForeignKey(nameof(Incident))]
        public long? IncidentId { get; set; }
 
        public IncidentDbEntity Incident { get; set; }
    }

Defined a query translation from DB entities to Public facing entities:

public IQueryable<Incident> GetIncidents()
       {
           var graphIncidents = m_dbContext.RawIncidents.Select(incident => new Incident()
           {
               Id = incident.IncidentId,
               Alerts = incident.Alerts.Select(a => new Alert()
               {
                   Id = a.AlertId
               })
           });

           return graphIncidents;
       }

And we return this IQueriable in the controller with [EnableQuery] attribute, to let OData do the magic and add the query options to the SQL query.

The problem is when we add $expand=alerts to the query - it adds additional left join that is not working (more than a minute of query time) on large SQL tables (more than ~1M rows)

This is the generated query, and this is the additional query for the $expand=alerts

SELECT [t].[IncidentId], [t0].[Id], [t1].[AlertId]
FROM (
    SELECT TOP(@__TypedProperty_3) [r].[IncidentId]
    FROM [WcdTestCloudEusStg].[RawIncidents] AS [r]
) AS [t]
LEFT JOIN (
    SELECT [a].[AlertId] AS [Id], [a].[IncidentId]
    FROM [WcdTestCloudEusStg].[AlertsForPublicApi] AS [a]
) AS [t0] ON [t].[IncidentId] = [t0].[IncidentId]
LEFT JOIN (
    SELECT [t2].[AlertId], [t2].[IncidentId]
    FROM (
        SELECT [a0].[AlertId], [a0].[IncidentId], ROW_NUMBER() OVER(PARTITION BY [a0].[IncidentId] ORDER BY [a0].[AlertId]) AS [row]
        FROM [WcdTestCloudEusStg].[AlertsForPublicApi] AS [a0]
    ) AS [t2]
    WHERE [t2].[row] <= @__TypedProperty_1
) AS [t1] ON [t].[IncidentId] = [t1].[IncidentId]
ORDER BY [t].[IncidentId], [t0].[Id], [t1].[IncidentId], [t1].[AlertId]

*I guess that it is doing it to enforce the page size we defined on each entity.
*But why 2 Join queries? And why order the rows in the 2nd query and not just simple TOP as in the Incident level?