Although you forgot to write your classes, it seems to me that you have a sequence of Posts. A Post has exactly one Author. Every Post has zero or more comments, and every comment belongs to exactly one Post: there are no comments that belong to nothing, there is a true one-to-many relation between Posts and Comments.
If you follow the guidelines for a one-to-many configuration, you'll have something similar to the following:
class Author
{
public int Id {get; set;}
// not certain, but very likely: every Author has zero or more Posts:
public virtual ICollection<Post> Posts {get; set;}
}
class Post
{
public int Id {get; set;}
// Every Post is posted by exactly one Author, using foreign key
public int AuthorId {get; set;}
public virtual Author Author {get; set;}
// Every Post has zero or more Comments:
public virtual ICollection<Comment> Comments {get; set;}
}
class Comment
{
public int Id {get; set;}
// every Comment belongs to exactly one Post using foreign key
public int PostId {get; set;}
public virtual Post Post {get; set;}
public string Status {get; set;}
}
Because I followed the entity framework naming conventions, this is enough to tell entity framework the primary keys, the foreign keys and the one-to-many relations. If you decide to deviate from the naming conventions, you'll probably need some attributes of fluent API, but the idea remains the same.
Back to your question
It seems to me that you want all Posts, together with their Author and only their approved Comments.
You use Include statements to get the data from the database. This is usually a waste of processing power, because you probably won't use all Post properties and all Comment Properties. It is better to explicitly name the Properties you plan to use.
var result = myDbcontext.Posts.Select(post => new
{
// query only the attributes you plan to use, for example:
Id = post.Id,
Title = post.Title,
Author = new
{
Id = post.Author.Id,
Name = post.Author.Name,
// no need to query the foreign Key Author.PostId, I already got that one in property Id
... // other author properties you need
},
Comments = post.Comments
.Where(comment => comment.Status == "approved")
.Select(comment => new
{ // again: query only the properties you plan to use:
Text = comment.Text,
...
}
.ToList(),
...
};
In my experience, if you configure your one-to-many relations properly you seldom need a join. Use the ICollections instead. Entity Framework will know that a join is needed for this.
However you can get the same results using a join:
var result = myDbContext.Posts
.Join(myDbContext.Comments.Where(comment => comment.Status = "approved"),
post => post.Id, // from every Post take the Id
comment => comment.PostId // from every Comment take the PostId
(post, comment) = new // when they match make a new object
{
Id = post.Id,
Title = post.Title,
CommentText = comment.Text,
}
etc. Note this is a real inner join. If you want the "Post with its comments" you'll need to do a GroupBy Id
Finally: if you only have a limited number of states, consider creating an enum for your status. This will prevent people from assigning "Approved" to the state instead of "approved", or worse: errors like "approved?" Internally in your database they will still be strings, but entity framework will parse them to proper enum values and you won't be able to put improper values in the database.
