linq - 在 EF Core Linq 查询中连接表

Question

我目前正在尝试使用 EF Core 制作一个 Web Api，并且在加入我收集的表格时遇到了一些问题。我正在使用以下数据库图:


我目前从我的 API 返回的数据看起来是这样的:

[  
   {  
      "postId":1,
      "postDate":"2018-10-21T21:56:43.9838536",
      "content":"First entry in posts!",
      "user":{  
         "userId":1,
         "creationDate":"2018-10-21T21:56:36.3539549",
         "name":"Hansel"
      },
      "comments":[  
         {  
            "commentId":1,
            "postDate":"0001-01-01T00:00:00",
            "content":"Nice!",
            "user":null
         },
         {  
            "commentId":2,
            "postDate":"0001-01-01T00:00:00",
            "content":"Cool, here's another comment",
            "user":null
         },
         {  
            "commentId":3,
            "postDate":"0001-01-01T00:00:00",
            "content":"and the last one for the night",
            "user":null
         }
      ]
   },
   {  
      "postId":2,
      "postDate":"2018-10-22T21:56:44.0650102",
      "content":"Its good to see that its working!",
      "user":{  
         "userId":2,
         "creationDate":"2018-10-16T21:56:36.4585213",
         "name":"Chris"
      },
      "comments":[  

      ]
   }
]

如您所见，它几乎可以工作，我的问题是评论中的空值，我也希望能够让用户退出。但由于某种原因我不能。

我当前的查询看起来像这样(我正在使用 DTO 来清理生成的 JSON):

var result = from post in context.Posts
                join user in context.Users 
                on post.User.UserId equals user.UserId
                join comment in context.Comments                            
                on post.PostId equals comment.Post.PostId into comments
                select new PostDTO
                {
                    Content = post.Content,
                    PostDate = post.PostDate,
                    User = UserDTO.UserToDTO(user),
                    Comments = CommentDTO.CommentToDTO(comments.ToList()),
                    PostId = post.PostId
                };

如果我正在使用 SQL，我会将用户加入“评论”，但我不能，所以我尝试了一个类似的解决方案，我认为它会起作用。

var result = from post in context.Posts
                join user in context.Users on post.User.UserId equals user.UserId
                join comment in 
                    (from u in context.Users
                    join c in context.Comments
                    on u.UserId equals c.User.UserId select c)
                on post.PostId equals comment.Post.PostId into comments
                select new PostDTO
                {
                    Content = post.Content,
                    PostDate = post.PostDate,
                    User = UserDTO.UserToDTO(user),
                    Comments = CommentDTO.CommentToDTO(comments.ToList()),
                    PostId = post.PostId
                };

然而，虽然这个查询确实执行结果与我写的第一个查询相同，但问题是用户没有加入评论

TLDR；我可以加入用户发帖，评论发帖，但我不能绑定(bind)用户评论

我希望你能帮助我，在此先感谢:)

编辑:这是我的模型

public class Comment
{
    public int CommentId { get; set; }
    public DateTime PostDate { get; set; }
    public string Content { get; set; }

    public User User { get; set; }

    public Post Post { get; set; }
}

  public class User
    {
        public int UserId { get; set; }
        public DateTime CreationDate { get; set; }
        public string Name{ get; set; }

        public ICollection<Post> Posts { get; set; }
        public ICollection<Comment> Comments { get; set; }
    }

public class Post
{
    public int PostId { get; set; }
    public DateTime PostDate { get; set; }
    public string Content { get; set; }
    public User User { get; set; }

    public ICollection<Comment> Comments { get; set; }
}

Answer 1

要获取带有评论的帖子(以及每个评论的用户)和帖子用户，只需使用 .Include()

var res = this._dbContext.Posts
        .Include(p => p.User)
        .Include(p => p.Comments)
            .ThenInclude(c => c.User)
        .ToList();

如果您不喜欢引入第 3 方库，例如 AutoMapper ，您可以创建一个 convertPostToDto功能和用途。

res.Select(p => convertPostToDto(p))

转换 res结果