EF Core: LINQ 选择“多对多对多”到“多对多”

Question

我确实有一个表“参考”和一个表“文章”，其中一篇文章引用了其他文章。

我确实有简单的引用，例如：A -> B

查询语句：

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# 链接：

_context.Reference

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

我也有参考链，例如：A -> B -> C（假设一条链中最多只有 3 篇文章）

查询语句：

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"

这在 SQL 中很容易，因为结果只是乘以额外的连接，但我不知道如何在 LINQ 中编写它。

我希望它是这样的（这是行不通的）：

_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();

在这里，我想要“A -> C”的“参考”类型的新结果。我想我必须在“新参考”部分之前包含/然后包含/加入/选择/selectmany（？）集合，但我不知道。

有什么办法可以存档吗？

Answer 1

好吧，您可以像在 SQL 中一样完成它，但使用导航属性而不是连接。我将使用 LINQ 查询语法，因为它能更好地显示相似性，而且方法语法对于此类查询来说非常复杂且难以阅读：from a in _context.Articlefrom ab in a.ReferenceArticleFromNavigationslet b = ab.ArticleToNavigationfrom bc in b.ReferenceArticleFromNavigationslet c = bc.ArticleToNavigationwhere a.ArticleNo = "1234"select new Reference{    ArticleFromNavigation = a,    ArticleToNavigation = c,}这些let语句不是很需要（您可以直接使用引用导航属性），我包含它们只是为了使 LINQ 查询更接近 SQL 查询。实际上，在这种情况下等效方法并没有那么糟糕 - 使用嵌套将多个级别展平SelectMany并使用重载投影 (top, bottom) 对，SelectMany允许：_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,    });

Answer 2

我将数据库建模为类以使语法正确。请参阅下面的代码：using System.Collections.Generic;using System.Linq;using System.Text;namespace ConsoleApplication107{&nbsp; &nbsp; class Program&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; static void Main(string[] args)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Context _context = new Context();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; string ArticleNo = "1234";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var results = (from a in _context.article.Where(x => x.Id == ArticleNo)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;join ab in _context.reference&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Where(x => (x.ArticleFromId == x.ArticleToId))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; on a.Id equals ab.ArticleFromId&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;select new { a = a, ab = ab }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ).Select(r => new Reference()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ArticleFromNavigation = r.a,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ArticleToNavigation = r.a.ReferenceArticleToNavigations.ToList()&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }).ToList();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; public class Context&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public List<Reference> reference { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public List<Article> article { get; set; }&nbsp; &nbsp; }&nbsp; &nbsp; public class Reference&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string ArticleFromId { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string ArticleToId { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public Article ArticleFromNavigation { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public List<string> ArticleToNavigation { get; set; }&nbsp; &nbsp; }&nbsp; &nbsp; public class Article&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string Id { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public List<string> ReferenceArticleToNavigations { get; set; }&nbsp; &nbsp; }}