EF Core: LINQ Select "many-to-many-to-many" to "many-to-many"

Question

I do have a table "Reference" and a table "Article" where a article references other articles.

I do have simple references like: A -> B

SQL:

select ab.*
from Article a
inner join Reference ab on ab.ArticleFromId = a.Id
inner join Article b on b.Id = ab.ArticleToId
where a.ArticleNo = "1234"

C# LINQ:

_context.Reference
   .Where(r => r.ArticleFromNavigation.ArticleNo.Equals("1234"));

I also do have reference-chains like: A -> B -> C (Lets assume there's only a maximum of 3 articles in a chain)

SQL:

select ab.ArticleFromId, bc.ArticleToId
from Article a
inner join Reference ab on ab.ArticleFromId = a.Id
inner join Article b on b.Id = ab.ArticleToId
inner join Reference bc on bc.ArticleFromId = b.Id
inner join Article c on c.Id = bc.ArticleToId
where a.ArticleNo = "1234"

This is easy in SQL, as the result just multiplies with the additional joins, but I don't know how to write that in LINQ.

I want it to be something like this (which wont work):

_context.Reference
   .Where(r => r.ArticleFromNavigation.ArticleNo.Equals("1234"))
   .Select(r => new Reference
   {
       ArticleFromNavigation = r.ArticleFromNavigation, //this is article "A"
       ArticleToNavigation = r.ArticleToNavigation.ReferenceArticleToNavigations //this wont work as it's a iCollection
   }).AsNoTrackable();

Here I want new results of the type "Reference" for "A -> C". I guess I have to include/theninclude/join/select/selectmany(?) the collection before the "new Reference"-section, but I have no clue.

Is there any way I can archive that?

Answer 1

Well, you can do it exactly as in SQL, but use navigation properties instead of joins.

I'll use LINQ query syntax because it better shows the similarity, and also method syntax is quite convoluted and hard to read for such type of queries:

from a in _context.Article
from ab in a.ReferenceArticleFromNavigations
let b = ab.ArticleToNavigation
from bc in b.ReferenceArticleFromNavigations
let c = bc.ArticleToNavigation
where a.ArticleNo = "1234"
select new Reference
{
    ArticleFromNavigation = a,
    ArticleToNavigation = c,
}

The let statements are not strongly needed (you could use reference navigation property directly), I've included them just to make the LINQ query closer to SQL query.

Actually the method equivalent is not that bad in this case - flatten several levels with nested SelectMany and project the (top, bottom) pair using the SelectMany overload allowing that:

_context.Article
    .Where(a => a.ArticleNo = "1234")
    .SelectMany(a => a.ReferenceArticleFromNavigations
        .SelectMany(ab => ab.ArticleToNavigation.ReferenceArticleFromNavigations)
        // include as many `SelectMany` like the above as you wish until you hit the desired level of nesting
        .Select(bc => bc.ArticleToNavigation),
    (a, c) => new Reference
    {
        ArticleFromNavigation = a,
        ArticleToNavigation = c,
    });