Include of entity and quering to it property generates excessive number of INNER JOINs

[mnikonov]

Next query

var news = context.News.Include(p => p.Feed).Where(p => p.Feed.ShowOnMainPage).AsNoTracking().ToList();

Generate the following SQL statements.

SELECT "p"."Id", "p"."DateAdded", "p"."Description", "p"."FeedId", "p"."FullText", "p"."Guid", "p"."IsFavourite", "p"."IsRead", "p"."Link", "p"."PubDate", "p"."Title", "f"."Id", "f"."Description", "f"."Encoding", "f"."FeedUrl", "f"."IncludeImageInNotification", "f"."Logo", "f"."SendNotifications", "f"."ShowOnMainPage", "f"."SiteUrl", "f"."Status", "f"."StoreLastItems", "f"."SyncEveryMin", "f"."Title"
FROM "News" AS "p"
INNER JOIN "Feed" AS "p.Feed" ON "p"."FeedId" = "p.Feed"."Id"
INNER JOIN "Feed" AS "f" ON "p"."FeedId" = "f"."Id"
WHERE "p.Feed"."ShowOnMainPage" = 1

And one of joins could be removed

the model is:

public class News
{
public string Description { get; set; }
public int FeedId { get; set; }
public string FullText { get; set; }
public string Guid { get; set; }
public int Id { get; set; }
public string Link { get; set; }
public DateTime? PubDate { get; set; }
public DateTime DateAdded { get; set; }
public string Title { get; set; }
public bool IsRead { get; set; }
public bool IsFavourite { get; set; }

public Feed Feed { get; set; }

}

public class Feed
{
public string Description { get; set; }
public string Encoding { get; set; }
public string FeedUrl { get; set; }
public int Id { get; set; }
public string Logo { get; set; }
public string SiteUrl { get; set; }
public FeedStatus Status { get; set; }
public string Title { get; set; }
public int StoreLastItems { get; set; }
public int SyncEveryMin { get; set; }
public bool SendNotifications { get; set; }
public bool IncludeImageInNotification { get; set; }

public List<News> News { get; set; }

}

public class DataContext : DbContext
{
public virtual DbSet Feeds { get; set; }
public virtual DbSet News { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var databaseFilePath = "sqlite.db";
    try
    {
        databaseFilePath = Path.Combine(ApplicationData.Current.LocalFolder.Path, databaseFilePath);
    }
    catch (InvalidOperationException)
    {
    }

    optionsBuilder.UseSqlite($"Data source={databaseFilePath}");
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Feed>(
        feed =>
        {
            feed.ForSqliteToTable("Feed");

            feed.HasKey(p => p.Id);

            feed.Property(p => p.Id).IsRequired().ValueGeneratedOnAdd();
            feed.Property(p => p.Title).IsRequired().HasMaxLength(500);
            feed.Property(p => p.Description).HasMaxLength(2500);
            feed.Property(p => p.SiteUrl).IsRequired().HasMaxLength(500);
            feed.Property(p => p.FeedUrl).IsRequired().HasMaxLength(500);
            feed.Property(p => p.Logo).HasMaxLength(500);
            feed.Property(p => p.Encoding).HasMaxLength(20);
            feed.Property(p => p.Status).IsRequired();
            feed.Property(p => p.StoreLastItems).IsRequired();
            feed.Property(p => p.SyncEveryMin).IsRequired();
            feed.Property(p => p.ShowOnMainPage).IsRequired();
            feed.Property(p => p.SendNotifications).IsRequired();
            feed.Property(p => p.IncludeImageInNotification).IsRequired();

            feed.HasIndex(p => p.ShowOnMainPage);
            feed.HasIndex(p => p.FeedUrl).IsUnique();

            feed.HasMany(p => p.News).WithOne(p => p.Feed).HasForeignKey(p => p.FeedId);
        });

    modelBuilder.Entity<News>(
        news =>
        {
            news.ForSqliteToTable("News");

            news.HasKey(p => p.Id);

            news.Property(p => p.Id).IsRequired().ValueGeneratedOnAdd();
            news.Property(p => p.FeedId).IsRequired();
            news.Property(p => p.Link).IsRequired().HasMaxLength(500);
            news.Property(p => p.Title).IsRequired().HasMaxLength(500);
            news.Property(p => p.DateAdded).IsRequired();
            news.Property(p => p.IsRead).IsRequired();
            news.Property(p => p.IsFavourite).IsRequired().HasDefaultValue(false);

            news.HasIndex(p => p.FeedId);
            news.HasIndex(p => p.Guid);
            news.HasIndex(p => p.PubDate);
            news.HasIndex(p => p.Link).IsUnique();
            news.HasIndex(p => p.IsFavourite);
            news.HasIndex(p => p.DateAdded);

            news.HasOne(p => p.Feed).WithMany(p => p.News).HasForeignKey(p => p.FeedId);
        });

}

}

Reproduced in EntityFramework.Sqlite 7.0.0-rc1-15948

[rowanmiller]

Created a general issue to track join elimination #3419