ViewModel with three different tables, how to join the three tables by lambda into one result?

Question

I have the following ViewModel:-

public class PostCommentReplyViewModel
{
     public List<POST> PostViewModel { get; set; }
     public List<COMMENT> CommentViewModel { get; set; }
     public List<REPLY> PostViewModel { get; set; }
}

And I have the following Action in my Controller:-

EntityDataModel db = new EntityDataModel();
var vm = new PostCommentReplyViewModel();
vm.PostViewModel = db.POSTs.Join(db.COMMENTs, b => b.user_id, c => c.user_id, (b, c) => b).ToList();
return View(vm);

Now this action return result of the two join tables but when I try to join the three tables into one result I get no result, How can I join the three tables into one result?

Linq Query Result to ViewModel

Thanks in advance :)

Answer 1

Thanks for your help, the most helpful solution I found to the problem is to create separate queries from ViewModel and then I attach them to one variable by the magic word Union and then I ask the ViewModel for the objects.

Here is the magic:

var resultOne = db.POSTs.Join(db.COMMENTs, b => b.user_id, c => c.user_id, (b, c) => b);
var resultTwo = db.POSTs.Join(db.REPLYs, b => b.user_id, c => c.user_id, (b, c) => b);
var resultUnion = resultOne.Union(resultTwo);
vm.PostViewModel = resultUnion.ToList();
return View(vm);

Answer 2

You are on the right way, you only need to add 3rd join:

vm.PostViewModel = db.POSTs
            .Join(db.COMMENTs, b => b.user_id, c => c.user_id, (b, c) => b)
            .Join(db.REPLYs, b => b.user_id, c => c.user_id, (b, c) => b)
            .ToList();

The reason of no results might be that you don't have data in all 3 tables that can be INNER JOINed by the same key.