Joining tables in EF Core Linq Query

Question

I'm currently trying to make a Web Api with EF Core, and i'm running into some problems joining the tables i've got together. I'm working with the following Database Diagram:

And the data i'm currently getting back from my API looks this this:

[  
   {  
      "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":[  

      ]
   }
]

As you can see, its aalmost working, my issue here is the null values in the comments, i want to be able to get the users out as well. But for some reason i cant.

my current query looks like this (i'm using DTO's to clean up the JSON generated):

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
                };

If i was working with SQL i'd join the users onto 'comments', but i cant, so i've tried a similar-ish solution, which i thought would work.

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
                };

However, while this query does execute the results are the same as the first query i wrote, the issue being that user isnt joined onto comment

TLDR; I can join User to post, and comment to post, but i cant bind user to comment

I hope you will be able to help me, thanks in advance :)

Edit: Here's my models

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; }
}

Algeria

Don't join, use navigation properties. And use AutoMapper, so you can project the entity classes to DTO classes in one line of code. The current code triggers client-side evaluation, because CommentDTO.CommentToDTO() can't be translated into SQL. – Septimal

Can you post the models? As @Gert mentioned, you should really use navigation properties. The strange thing is that you seem to have them, but use them for joining. But user variable here join user in context.Users on post.User.UserId equals user.UserId is the same as post.User. If we continue, post should have ICollecton<Comment> Comments and Comment should have User User, so you should really be able to produce the result with just selects (projections). – Airdry

I've posted the models now. I've got navigation properties, yes, but i'm unsure about how to utilize them properly as i havnt been able to find any examples that have a similar database structure as mine, i've tried to make a query with includes only and it gives me waaay too much data. example: post + the comments + the comments users + the users comments and the users posts. – Algeria

@GertArnold I've seen the AutoMapper suggestion a lot, and if i were doing a professional job i'd probably use it as its faster than doing it manually. But i'm doing it for fun and because i want to learn how this stuff works :) Thanks for the suggestion though! – Algeria

AutoMapper is fun too :) But I get your point. Happy coding! – Septimal

Answer 1

To get the posts with comments (and every comment's user) and post users, simply use .Include()

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

If you don't like to introduce a 3rd party lib such as AutoMapper, you can create a convertPostToDto function and use .

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

to convert the res to the result

Tanto

Holy shit, i didnt even think about doing it that way, now i feel like a very stupid person. I'll try it out and see if it works for me :) – Algeria

All good except the last advice. Never create function and use it like suggested - it would cause client evaluation which should be avoided. – Airdry

@IvanStoev Thanks a lot. I didn't realize that it's possible to convert posts to dto on database. I'll try that later, if I can confirm all the conversion can be done on database, I'll update my anwser. – Tanto

Well, not really in the database, but when materializing the query result (it comes as DbDataReader and needs to be converted to objects). But using discoverable projections allows EF to create the necessary SQL SELECTs and avoid creation (and tracking) intermediate entity objects. Basically the difference between AutoMapper Map and ProjectTo :) Anyway, you are welcome. – Airdry

I was making a join query, and then I saw .Include(). Life saver :) @IvanStoev – Stoic