代码之家 › 专栏 › 技术社区 › Matt Altepeter

获取找到匹配项的所有行

entity-framework linq asp.net c#

Matt Altepeter · 技术社区 · 10 年前

我正在使用以下数据库表创建一个简单的博客系统:

Posts
    PostId
    Title
    PostDate
    Body
    Draft

Categories
    CategoryId
    CategoryName

PostCategories
    PostId
    CategoryId

Authors
    AuthorId
    AuthorName

PostAuthors
    AuthorId
    PostId

Tags
    TagId
    TagName

PostTags
    PostId
    TagId

以下是我到目前为止的查询(使用LINQ):

 var results = from p in posts 
               join pc in postcategories on p.PostId equals pc.PostId 
               join c in categories on pc.PostId equals c.CategoryId 
               join a in authors on pc.PostId equals a.AuthorId 
               select new BlogViewModel { 
                                          Post = p, 
                                          Category = c, 
                                          Author = a 
                                        };

这将成功返回所有帖子、帖子所属的类别和帖子作者。我的问题是如何获取每个帖子的所有相关标签。我使用实体框架生成模型 BlogViewModel 包含:

public Post Post { get; set; }
public Category Category { get; set; }
public Author Author { get; set; }
public IEnumerable<Tag> Tags { get; set; }

我的直觉告诉我,我需要在 select new BlogViewModel 语句,类似于:

... Tags = //new LINQ statement?

有人能帮我指出正确的方向吗?

谢谢

2 回复 | 直到 10 年前

Ehsan Sajjad 10 年前

你必须加入 tags 和 posttags 然后将数据分组 PostId :

var results = from p in posts
              join pc in postcategories on p.PostId equals pc.PostId
              join c in categories on pc.PostId equals c.CategoryId
              join ap in authorposts on pc.PostId equals ap.PostId
              join a in authors on ap.AuthorId equals a.AuthorId
              join tp in tagposts on p.PostId equals tp.PostId
              join t in tags on tp.TagId equals t.TagId
              group new { t, p, c, a } by tp.PostId into g

              select new BlogViewModel
                        {
                          Post = g.First().p,
                          Category = g.First().c,
                          Author = g.First().a,
                          Tags = g.Select(x=>x.t)
                        };

看见 working fiddle example