Entity Framework related entities generate unnecessary joins of the same table

Question

I am running into issue of EF Core 3.1 generating multiple INNER JOINs to the same table even though it has enough information to avoid it.

Simplified model with mapping :

internal class PRecord
{
    public int Id { get; private set; }
    public string Name { get; private set; }

    public PImport Import { get; private set; }

    private PRecord()
    {
    }

    internal PRecord(string name)
    {
        Name = name;
    }

    public class PRecordDbMapping : IEntityTypeConfiguration<PRecord>
    {
        public void Configure(EntityTypeBuilder<PRecord> builder)
        {
            builder.ToTable("p_records");
            builder.HasKey(x => x.Id);

            builder.Property(x => x.Id).HasColumnName("id");
            builder.Property<long>("PImportId").HasColumnName("p_import_id");

            builder.HasOne(x => x.Import).WithMany(x => x.Records).HasForeignKey("PImportId").IsRequired();
        }
    }
}

internal class PImport
{
    public long Id { get; private set; }
    public PSource Source { get; private set; }

    public IEnumerable<PRecord> Records { get; private set; }

    private PImport()
    {
    }

    internal PImport(PSource source)
    {
        Source = source;
    }

    public class PImportDbMapping : IEntityTypeConfiguration<PImport>
    {
        public void Configure(EntityTypeBuilder<PImport> builder)
        {
            builder.ToTable("p_imports");
            builder.HasKey(x => x.Id);

            builder.Property(x => x.Id).HasColumnName("id");
            builder.Property<int>("PSourceId").HasColumnName("p_source_id");

            builder.HasOne(x => x.Source).WithMany(x => x.Imports).HasForeignKey("PSourceId").IsRequired();
        }
    }
}

internal class PSource
{
    public int Id { get; private set; }
    public PImport CurrentImport { get; private set; }

    public IEnumerable<PImport> Imports { get; private set; }

    private PSource()
    {
    }

    internal PSource(int id)
    {
        Id = id;
    }

    public class PSourceDbMapping : IEntityTypeConfiguration<PSource>
    {
        public void Configure(EntityTypeBuilder<PSource> builder)
        {
            builder.ToTable("p_sources");
            builder.HasKey(x => x.Id);

            builder.Property(x => x.Id).HasColumnName("id");
            builder.Property<long>("CurrentPImportId").HasColumnName("current_p_import_id");

            builder.HasOne(x => x.CurrentImport).WithOne().HasForeignKey<PEPSource>("CurrentPImportId");
        }
    }
}

I tried multiple variants of the following, but with slight variations I always get the same problem in SQL :

var query = ctx.PRecords
               .Where(x => x.Name.Contains("bla"))
               .Where(x => x.Import.Source.CurrentImport == x.Import)
               .Select(x => new { Record = x, Source = x.Import.Source });

SELECT 
    [p].[id], [p].[name], [p].[p_import_id], [p1].[id], [p1].[current_p_import_id]
FROM [p_records] AS [p]
INNER JOIN [p_source_imports] AS [p0] ON [p].[p_import_id] = [p0].[id]
INNER JOIN [p_sources] AS [p1] ON [p0].[p_source_id] = [p1].[id]
INNER JOIN [p_source_imports] AS [p2] ON [p1].[current_p_import_id] = [p2].[id]
WHERE (CHARINDEX(N'bla', [p].[name]) > 0) AND ([p2].[id] = [p0].[id])

I am able to generate the SQL I want with the following code :

var query = ctx.PSources
               .Where(x => x.CurrentImport != null)
               .SelectMany(x => x.CurrentImport.Records, (x, y) => new { Record = y, Source = x })
               .Where(x => x.Record.Name.Contains("bla"));

The issue with this one is that I want to move the .Where(x => x.Record.Name.Contains("bla")) into extension method. And in this query I have to apply it on the anonymous type.

So I have two questions :

1. Can the first query be salvaged to generate reasonable SQL, while still returning IQueryable<PRecord> or if not possible a queryable of some non-anonymous type ?
2. If (1) cannot be achieved reasonably, can I achieve the moving of the WHERE condition on the Name to some extension method anyway?

EDIT: specified EF Core 3.1 as the verswion used

EDIT: the accepted solution is enough for my case. Completely clean solution might not be possible in the current version of EF Core. As I was browsing github issues for EF Core it seems the SQL generator might have restrictions that cause the more straightforward queries to use unnecessary 'JOIN's.

So the query I went with is :

var query = ctx.PSources
               .Where(x => x.CurrentImport != null)
               .SelectMany(x => x.CurrentImport.Records, (x, y) => new SourceRecordPair() { Record = y, Source = x })
               .Where(x => x.Record.Name.Contains("bla"));

Answer 1

1. Can the first query be salvaged to generate reasonable SQL, while still returning IQueryable<PRecord> or if not possible a queryable of some non-anonymous type ?

Yes, by breaking the encapsulation and accessing directly the shadow FK property (with EF.Property method) inside the query. Error prone (you need to know/keep in sync the property name string), but can be used as workaround until gets fixed by EF Core (because it's current EF Core defect):

.Where(x => EF.Property<long?>(x.Import.Source, "CurrentPImportId") == x.Import.Id)

2. If (1) cannot be achieved reasonably, can I achieve the moving of the WHERE condition on the Name to some extension method anyway?

Yes. But you have to create two extension methods - one normal and one generic with expression argument for extracting the "source", e.g.

public static IQueryable<PRecord> FilterRecords(this IQueryable<PRecord> source)
    => source.FilterRecords(x => x);

public static IQueryable<T> FilterRecords<T>(this IQueryable<T> source, Expression<Func<T, PRecord>> recordSelector)
    => source.Where(recordSelector.Apply(
            x => x.Name.Contains("blah") // actual filter goes here
        ));

Now you can use the non generic method with your first query

var query = ctx.PRecords
    .FilterRecords() // <--
    .Where(x => EF.Property<long?>(x.Import.Source, "CurrentPImportId") == x.Import.Id) // from (1)
    .Select(x => new { Record = x, Source = x.Import.Source });

and the generic method with your second query

var query = ctx.PSources
    .Where(x => x.CurrentImport != null)
    .SelectMany(x => x.CurrentImport.Records, (x, y) => new { Record = y, Source = x })
    .FilterRecords(x => x.Record); // <--

Note that the generic method works with any type of T, and also the T is inferred by the compiler, so you can use it with both anonymous and concrete type projections.

The first method will simply call the second passing "self selector" (x => x), and the actual implementation will be in the second method. With the following detail - you need a small helper utility for composing expressions, so the Apply method here is from my own ExpressionUtils helper class which I use in many expression related answers:

public static partial class ExpressionUtils
{
    public static Expression<Func<TOuter, TResult>> Apply<TOuter, TInner, TResult>(this Expression<Func<TOuter, TInner>> outer, Expression<Func<TInner, TResult>> inner)
        => Expression.Lambda<Func<TOuter, TResult>>(inner.Body.ReplaceParameter(inner.Parameters[0], outer.Body), outer.Parameters);

    public static Expression<Func<TOuter, TResult>> ApplyTo<TInner, TResult, TOuter>(this Expression<Func<TInner, TResult>> inner, Expression<Func<TOuter, TInner>> outer)
        => outer.Apply(inner);

    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
        => new ParameterReplacer { source = source, target = target }.Visit(expression);

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression source;
        public Expression target;
        protected override Expression VisitParameter(ParameterExpression node)
            => node == source ? target : node;
    }
}

Answer 2

Unfortunately, you cannot pass anonymous types from one method to the other and still access the properties. You will need to strongly type the following:

new SomeModel { Record = y, Source = x })