c# - 如何遍历多个左连接 LINQ to SQL

Question

当我尝试遍历此 SQL to LINQ 方法时，我只收到 forum_category 的所有行。我真正想要它做的是从 forum_category 获取所有行并返回与类别 ID 匹配的所有论坛，这是预期的结果:

FORUM_CATEGORY (categoryid, categorytitle)
 - FORUM (forumid, forumtitle, forumdescrition) -> (latest topic => topicid, topictitle) -> (latest post on that latest topic => postid, postadded, username) -> total topic count, total post count in this forum only
 - FORUM (forumid, forumtitle, forumdescrition) -> (latest topic => topicid, topictitle) -> (latest post on that latest topic => postid, postadded, username) -> total topic count, total post count in this forum only
 - FORUM (forumid, forumtitle, forumdescrition) -> (latest topic => topicid, topictitle) -> (latest post on that latest topic => postid, postadded, username) -> total topic count, total post count in this forum only

FORUM_CATEGORY
 - FORUM [...]
 - FORUM [...]
 - FORUM [...]

你明白了......

-

这是我目前得到的:

FORUM_CATEGORY
 - FORUM (forumid, forumtitle, forumdescrition) -> (latest topic => topicid, topictitle) -> (latest post on that latest topic => postid, postadded, username) -> total topic count, total post count in this forum only

FORUM_CATEGORY
 - FORUM (forumid, forumtitle, forumdescrition) -> (latest topic => topicid, topictitle) -> (latest post on that latest topic => postid, postadded, username) -> total topic count, total post count in this forum only

即每个论坛类别只有一个论坛。这是 LINQ 代码:

var forum = (from c in context.forum_category
             join f in context.forum on c.id equals f.categoryid
             join t in context.forum_topic on f.id equals t.forumid
             join tc in context.forum_topic on f.id equals tc.forumid into tcount
             join p in context.forum_posts on t.id equals p.topicid
             join pc in context.forum_posts on t.id equals pc.topicid into pcount
             join u in context.users on p.userid equals u.id
             orderby p.added descending
             select new ForumIndexModel
                        {
                            CategoryId = c.id,
                            CategoryTitle = c.title,

                            ForumId = f.id,
                            ForumTitle = f.title,
                            ForumDescription = f.description,
                            TopicId = t.id,
                            TopicTitle = t.title,

                            PostId = p.id,
                            PostAdded = p.added,

                            Username = u.username,

                            TopicCount = tcount.Count(),
                            PostCount = pcount.Count()
                        }).ToList();
return View(forum);

这只是我采用的一些不同方法的一个示例。

编辑:更具体地阐明了我想要什么。

Answer 1

我将应原始发布者的要求添加另一个答案。我正在使用 Entity Framework 并在我的示例中使用了定义的关联。我的数据模型(出于本示例的目的)是三个表，因为存在多对多关系，但您的数据模型会更简单，只有一对多关系的类别和论坛表(加上 addtl。查找表)

所以我的数据模型(物理)

• lm_m_类别
• lm_m_category_link(这是多对多表)
• lm_m_link

我在 EF 中定义了关联(nCat、nLink 用于从外部参照表导航到类别或链接)。 EF 以这种方式为您处理联接。

因此加载此代码的代码不是一个语句，但您让我向您展示我将如何使用 EF 执行此操作。我没有给出 Category 和 Link 类的示例，它们是简单的 id、desc 类，但 Category 有一个链接列表。我的物理数据模型使用 lm_m_* 表。

List<Category> Categories = new List<Category>();

Category newCategory;
Link newLink;

foreach (var category in db.lm_m_category_link.Include("nCategory").Include("nLink").ToList())
    {
    newCategory = new Category();

    newCategory.category_id = category.category_id;
    newCategory.category_name = category.nCategory.nCat.category_name;

    foreach (var link in (IEnumerable<lm_m_link>)category.nLink)
    {
        newLink = new Link();

        newLink.link_id = link.link_id;
        newLink.link_name = link.link_title;
        newLink.link_url = link.link_url;

        // add link to list
        newCategory.category_links.Add(newLink);
    }

    // add category 
    Categories.Add(newCategory);
}