如何迭代LINQ到SQL的几个左联接

Question

当我尝试循环使用此SQL到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

我将根据原始海报的要求添加另一个答案。我使用实体框架，并有定义的关联在我的示例中使用。我的数据模型（就本例而言）是三个表，因为存在多对多关系，但你的数据模型会更简单，只有一对多关系的类别和论坛表（加上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);
}

Answer 2

我认为你的排序造成了问题，但经过进一步审查。。。

我有另一个想法，您的ForumIndexClass看起来不符合您的要求。您的ForumIndexModel不应该在论坛列上有列表（或IEnumerable）吗？您在应该有列表的地方指定单个值。你的课应该看起来更像。。。

  class ForumIndexModel
    {
    int CategoryId {get; set;}
    string CategoryTitle {get; set;}
    List<int> ForumIds {get; set;}
    List<string> ForumTitles {get; set;}
    List<string> ForumDescriptions {get; set;}
    ... you get the idea...
    }

要选择进入列表，请执行以下操作：

public class C
{
  A TheA {get;set;}
  List<B> TheBs {get;set;}
}

//g is an IEnumerable<B> with a key property of A
List theResult =
(
  from a in ListA
  join b in ListB on a.ValueAB = b.ValueAB into g
  select new C()
 {
    TheA = g.Key,
    TheBs = g.ToList()
  }
).ToList();