LINQ到SQL的多对多问题

本文关键字：问题 SQL LINQ | 更新日期: 2023-09-27 18:18:54

我有三个表，它们具有以下简化的模式:

Articles
    articleID
ArticleAuthors
    articleID
    authorID
Authors
    authorID

一篇文章可以有多个作者。

给定一个输入文章，我想要查找输入文章的作者所写的所有其他文章。因此，如果Joe和Jill写了第1条，我想要Joe或Jill写的所有其他文章。我需要一些东西来满足以下函数:

public Article[] articlesBySameAuthors(Article article) {}
在SQL中，我只需要这样做: 
SELECT * FROM Articles A 
INNER JOIN ArticleAuthors AA ON A.articleID = AA.articleID
WHERE AA.authorID IN (SELECT authorID FROM ArticleAuthors 
    WHERE articleID = @articleID) AND A.articleID <> @articleID;

但是我真的想知道如何在LINQ to SQL中做到这一点。

谢谢。

LINQ到SQL的多对多问题

这些有帮助吗?

与

嵌套组

直译是愚蠢的——我假设你有一个比这更好的对象结构。如果您需要更多关于特定对象结构的详细信息，请发布。

这一行…

Var auth = db.ArticleAuthors.where(a=>a.Article == article).Select(Aa=>aa.author)
Var result = from aa in Db.articleauthors
              Were auth.Contains(aa.author)
    Select aa.Article

var list = (
    from articleAuthor in articleAuthors 
    from article in articles 
    where articleAuthor.articleID == article.articleID 
    where (from aa in articleAuthors where aa.articleID == requested_article_id select aa.authorID).Contains( articleAuthor.authorID )
    select article).Distinct().ToList();

这个伪代码函数假设您已经在其他地方获得了articles、articleAuthors和authors:

Article[] ArticlesBySameAuthors(Article article)
{
    return
        (from a in articles
        where a.ArticleID = article.ArticleID
        join articleAuthor in articleAuthors on a.ArticleID equals articleAuthor.ArticleID
        join author in authors on articleAuthor.AuthorID equals author.AuthorID
        select author).ToArray();
}
编辑:

前面的是疲惫的头脑的产物。下面是另一个例子:

Article[] ArticlesBySameAuthors(Article article)
{
    return
        (from a in articles
        where a.ArticleID = article.ArticleID
        join articleAuthor in articleAuthors on a.ArticleID equals articleAuthor.ArticleID
        join articleAuthor2 in articleAuthors on articleAuthor.AuthorID equals articleAuthor2.AuthorID
        join article2 in articles on articleAuthor2.ArticleID = article2.ArticleID
        where article2.ArticleID != article.ArticleID
        select article2).ToArray();
}

或者，假设Article类型具有Authors集合，反之亦然，则可以使用以下命令:

Article[] ArticlesBySameAuthors(Article article)
{
    return
        (from author in article.Authors
        from article2 in author.Articles
        where article2.ArticleID != article.ArticleID
        select article2).ToArray();
}
相关文章：