EF Core: How to provide efficient (queryable) access to indirectly related entities from domain?

Question

Suppose we have three entities A, B, C with a many-to-one relationship A -> B and a many-to-one relationship B -> C.

In our domain source of A we then have:

class A 
{
   public ICollection<B> relatedBs { get; private set; }
}

Since the relationship is mapped via HasOne(...).WithMany(...) a call to dbContext.As.SelectMany(a => a.relatedBs) will be translated to a join on sql level, especially returning an IQueryable<B> which I can extend without enforcing client side evaluation.

The situation with B.relatedCs is analogous.

Now, the call

dbContext.As.SelectMany(a => a.relatedBs.SelectMany(b => b.relatedCs))

will be translated into a join of all three tables as expected.

But: if we add the following property to A:

public IEnumerable allRelatedCs => relatedBs.SelectMany(b => b.relatedCs)

which is ignored in the db configuration because there is no direct relation, the expression

dbContext.As.SelectMany(a => a.allRelatedCs).ToQueryString()

throws:

InvalidOperationException: "The LINQ expression 'ag => ag.Vorgaenge' could not be translated."

The point is, that I need the property A.allRelatedCs since it is part of the domain logic, but I also want that the above call correctly resolves to the same IQueryable as the two-layer-select-many above which resolves to the same SQL query.

My questions:

Is this possible?
Why does it not work this way? As far as I see, I am just writing the same expression in another way, so what goes wrong? I thought it could be because allRelatedCs is an IEnumerable, but the resulting expression is in fact an IQueryable, only the call to ToQueryString() fails (from which I conclude that further LINQ calls force client side evaluation)

It is possible but with usage of third party libraries, which corrects Expression Tree before translating by EF Core, like [LINQKit](https://github.com/scottksmith95/LINQKit). If you interested will prepare answer. — Svyatoslav Danyliv, Aug 01 '22 at 08:47
How does your class B refers to class A? Do you have something like this: public virtual ClassA A { get; set; } ? And the same to your class C? — Ricardo Rodrigues, Aug 01 '22 at 09:36
@SvyatoslavDanyliv It sounds interesting, but is there any reference which states that pure ef is not able to do that? — T_01, Aug 02 '22 at 08:21
@RicardoRodrigues Yes, The many to one relations are configured both ways. — T_01, Aug 02 '22 at 08:21
EF cannot look into method body and predict which query to build. This is common sense. Also I'm good in LINQ translation and I know how EF Core works and which limitations it has. — Svyatoslav Danyliv, Aug 02 '22 at 10:42

score 1 · Answer 1 · answered Aug 02 '22 at 10:57

I would suggest to use LINQKit for such task. For activating you can use DbContextOptionsBuilder:

builder
    .UseSqlServer(connectionString) // or any other provider
    .WithExpressionExpanding();     // enabling LINQKit extension

Decorate property with ExpandableAttribute:

public class A
{
    // other properties

    [Expandable(name(allRelatedCsImpl))]
    public IEnumerable<C> allRelatedCs 
        => relatedBs.SelectMany(b => b.relatedCs);

    // we return Expression which will be embedded into the main query
    private static Expression<Func<A, IEnumerable<C>>> allRelatedCsImpl()
        // a == this
        => a => a.relatedBs.SelectMany(b => b.relatedCs);
}

Then your query should be translated sucessfully:

dbContext.As.SelectMany(a => a.allRelatedCs).ToQueryString();

LINQKit is not alone and there are other libraries which can do the same thing. Brief overview is here, it can be simpler to use DelegateDecompiler, for example.

EF Core: How to provide efficient (queryable) access to indirectly related entities from domain?

1 Answers1