Linq select the items that have unique foreign key and ignore the items that has duplicated foreign key

Question

Let's say I have two tables, Comment { Id , PostId } and Post { Id }.

Now I want to retrieve the comments that have different PostId property, should not have two or more comments with same PostId in my query result.

Is that possible with Linq?

My code:

var MyPosts = _context.PostComments
                      .Include(a => a.ApplicationUser)
                      .Include(a => a.Post)
                      .OrderByDescending(a => a.DateTime)
                      .Select(a => a.Post)
                      .Take(25);
    
PostComments = _context.PostComments
                       .Include(a => a.ApplicationUser)
                       .Include(a => a.Post)
                       .OrderByDescending(a => a.DateTime)
                       .Where(a => a.Id != null 
                                   && MyPosts.Where(p => p.Id == a.PostId).Count() < 2)
                       .Take(25);

Answer 1

Try the following query:

var postComments = _context.PostComments;

var MyPosts = 
    from d in postComments.Select(d => new { d.PostId }).Distinct()
    from pc in postComments
        .Include(pc => pc.ApplicationUser)
        .Include(pc => pc.Post)
        .Where(pc => pc.PostId == d.PostId)
        .OrderByDescending(pc => pc.DateTime)
        .Take(1);
    select pc;