Pfthb

I have these three tables in SQL Server:

Evaluations (id, textreview, stars, userId, BookId) 
Books (id, title, ..., userId) 
Users (id, name, ...)

And I have this code to get data from my tables:

public List<Evaluations> GetAllEvaluationsPerBook()

 string sqlCommand = "SELECT DISTINCT b.Title,ev.TextReview,ev.Stars, b.Id " +
 "FROM[Services.BookDbContext].[dbo].[Evaluations] as ev, " +
 "[Services.BookDbContext].[dbo].Books as b " +
 "WHERE b.Id = ev.Book_Id";

 using (var context = new BookDbContext())
 
 var evaluation = context.Evaluation.SqlQuery(sqlCommand).ToList();

 return evaluation;
 

Now I'm creating a WebApi project in C# with EF6. I use Actions with HttpPost. In one of them I need to retrieve some objects from the DB and send them to a client such as Fiddler in json format. More specifically, I want to get the Title of the Book along with all the Evaluations.

Now I need to create the json below using the code above:

"id":1, "textReview":"Good", "stars":3, "book":null, "user":null, 
"id":2, "textReview":"Awfull", "stars":1, "book":null, "user":null, 
"id":1, "textReview":"Good", "stars":3, "book":null, "user":null, 
"id":4, "textReview":"Very Good","stars":4, "book":null, "user":null

E.G.: Below you can see the result which I receive from DB, but i cannot make it appear in json format:

How can I do this?

I just found the answer which I wanted:

public List<Evaluations> GetAllEvaluationsPerBook()
 
 using (var context = new BookDbContext())
 
 var evaluations = context.Evaluation.ToList();
 var books = context.Book.ToList();
 var users = context.User.ToList();

 return evaluations;
 
 

So the code always runs line by line.

The variable evaluations creates a list, filling all its own properties except the Book and the User objects which are remaining null:

 
 "id":1,
 "textReview":"The Book is good.",
 "stars":3,
 "book":null,
 "user":null

After "running" the next line, it fills the list of books. But it fills also the previous list of evaluations with the new list of books:

 
 "id":1,
 "textReview":"The Book is good.",
 "stars":3,
 "book": 
 "isbn":1,
 "title":"The Tomb",
 "author":"H. P. Lovecraft",
 "user":null
 ,
 "user":null

Finally it "runs" the line with the users, (retrieving all the users from the DB and creating a list of users) and automatically it fills the previous Lists, so I have all the information retrieved from the DataBase.