LINQ double INNER JOIN on query translation when using selectMany

I have the following LINQ statement:

var repoActivityRowsTest = appManager.GetRepository<ActivityRow>();
var activityRowsTest = await repoActivityRowsTest.Search(f => f.ExcelReport.uploadPhase == RPToolConstants.Phase_Planning, includeProperties: "PlanningInfo")
                                        .Where(f => iso3Alpha3List.Contains(f.ExcelReport.countryOfficeIso3Alpha3))
                                        .SelectMany(sm => sm.PlanningInfo).Select(s => new { s.Year, s.Count, s.ActivityRow.UnitCost })
                                        .GroupBy(g=>new { g.Year }).Select(sg=>new { sg.Key.Year, Total = sg.Sum(sum => sum.UnitCost * sum.Count) })
                                        .ToListAsync();

Which uses the repository pattern. The search function is the one below:

public IQueryable<TEntity> Search(Expression<Func<TEntity, bool>> filter = null,
                                 string includeProperties = "", bool trackChanges = false)
{
    IQueryable<TEntity> query = context.Set<TEntity>();

    if (filter != null)
    {
        query = query.Where(filter);
    }

    foreach (var includeProperty in includeProperties.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
    {
        query = query.Include(includeProperty.Trim());
    }

    if (!trackChanges)
    {
        query = query.AsNoTracking();
    }
    return query;
}

When I inspect the command that arrives in SQL Server I see that the query is translated in the following SQL:

SELECT [a0].[Year], SUM([a1].[UnitCost] * CAST([a0].[Count] AS decimal(18,2))) AS [Total]  
FROM [ActivityRows] AS [a]  
INNER JOIN [ExcelReports] AS [e] ON [a].[ExcelReportId] = [e].[Id]  
INNER JOIN [ActivityRowPlanningInfo] AS [a0] ON [a].[Id] = [a0].[ActivityRowId]  
INNER JOIN [ActivityRows] AS [a1] ON [a0].[ActivityRowId] = [a1].[Id]  
WHERE ([e].[uploadPhase] = N'planning') 
AND [e].[countryOfficeIso3Alpha3] IN (N'AFG', N'DZA', N'AGO', N'ARM', N'BGD')  
GROUP BY [a0].[Year]

It works perfectly, but why there is an inner join duplicated:

INNER JOIN [ActivityRows] AS [a1] ON [a0].[ActivityRowId] = [a1].[Id]

is a non-sense to me!

If I remove it from the SQL it works as before. Is there any issue in my LINQ query that causes this strange SQL?

here is the definition of the entities:

public class ActivityRow : Entity<int>
{
    public string Description { get; set; }

    public int ExcelReportId { get; set; }

    [ForeignKey("ExcelReportId")]
    public virtual ExcelReport ExcelReport { get; set; }

    public int ActivitySubTypeId { get; set; }

    [ForeignKey("ActivitySubTypeId")]
    public virtual ActivitySubType ActivitySubType { get; set; }

    public int? ActivityCategoryId { get; set; }

    [ForeignKey("ActivityCategoryId")]
    public virtual ActivityCategory ActivityCategory { get; set; }

    public string ResponsibleEntity { get; set; }

    [Column(TypeName = "decimal(18,2)")]
    public decimal UnitCost { get; set; }
    public string Notes { get; set; }


    public virtual ICollection<ActivityRowReportingInfo> ReportingInfo { get; set; }
    public virtual ICollection<ActivityRowPlanningInfo> PlanningInfo { get; set; }  
}


public class ActivityRowPlanningInfo : Entity<int>
{
    public int ActivityRowId { get; set; }

    [ForeignKey("ActivityRowId")]
    public virtual ActivityRow ActivityRow { get; set; }

    public int Year { get; set; }
    public int Quarter { get; set; }
    public int Count { get; set; }
}

and here the definition of the relationships with fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    //activities
    modelBuilder.Entity<ActivityRow>()
       .HasMany(b => b.ReportingInfo)
       .WithOne(t => t.ActivityRow)
       .OnDelete(DeleteBehavior.Cascade);

    modelBuilder.Entity<ActivityRow>()
       .HasMany(b => b.PlanningInfo)
       .WithOne(t => t.ActivityRow)
       .OnDelete(DeleteBehavior.Cascade);

    ...etc.
}

Answer

Rewrite query via LINQ Query syntax and you can simplify your query with ease. The following query do not create non wanted joins:

var repoActivityRowsTest = appManager.GetRepository<ActivityRow>();
var activityRows = repoActivityRowsTest
  .Search(f => true);

var resultQuery = 
  from ar in activityRows
  where 
    ar.ExcelReport.uploadPhase == RPToolConstants.Phase_Planning
    && iso3Alpha3List.Contains(ar.ExcelReport.countryOfficeIso3Alpha3)
  from pi in ar.PlanningInfo
  group new { ar, pi } by new { pi.Year } into g
  select new 
  {
     g.Key.Year,
     Total = g.Sum(x => x.ar.UnitCost * x.pi.Count)
  };

var result = await resultQuery.ToListAsync();

Advertisement

Answer