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.
}

Continue reading...