I have a User entity

public class User
{
    public int UserID { get; set; } = 0;
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Phone { get; set; } =  string.Empty;
    public string UserStatus { get; set; } =  string.Empty;
}

and I have a Comment entity.

public class Comment
{
    public int CommentID { get; set; } = 0;
    public int ProductID { get; set; } = 0;
    public int UserID { get; set; } = 0;
    public string Text { get; set; } = string.Empty;
    public DateTime DateAdded { get; set; } = DateTime.MinValue;
}

I am wanting to get the following data on a view so when a user clicks a button it will display all the comments for a given ProductID like this:

My SQL query looks like this

SELECT CommentID,
    ProductID,
    C.UserID,
    U.Name,
    CommentText,
    DateAdded
FROM [Comment] C
JOIN [User] U ON C.UserID = U.UserID
WHERE ProductID = @ProductID

How to get the data I need on the view?

I have a User entity

public class User
{
    public int UserID { get; set; } = 0;
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Phone { get; set; } =  string.Empty;
    public string UserStatus { get; set; } =  string.Empty;
}

and I have a Comment entity.

public class Comment
{
    public int CommentID { get; set; } = 0;
    public int ProductID { get; set; } = 0;
    public int UserID { get; set; } = 0;
    public string Text { get; set; } = string.Empty;
    public DateTime DateAdded { get; set; } = DateTime.MinValue;
}

I am wanting to get the following data on a view so when a user clicks a button it will display all the comments for a given ProductID like this:

My SQL query looks like this

SELECT CommentID,
    ProductID,
    C.UserID,
    U.Name,
    CommentText,
    DateAdded
FROM [Comment] C
JOIN [User] U ON C.UserID = U.UserID
WHERE ProductID = @ProductID

How to get the data I need on the view?

• c#
• asp-core
• asp-core-mvc

• Either you can use ADO.NET to execute the query/stored procedure, or you convert the write as LINQ-to-SQL. Both examples can be founded in here. Would be great if you can do some attempts and show the error if you are stucked. Thanks. – Yong Shun Commented 2 hours ago

1 Answer 1

wanting to get the following data on a view so when a user clicks a button it will display all the comments for a given ProductID

You can try to create a view model that represents the data and logic required by a view based on your requirements as below:

public class UserCommentsVM
{
    public int UserID { get; set; }
    public string Name { get; set; }
    //...
    //user basis info
    //...
    public List<Comment> Comments { get; set; }
}

And query records from database using your SQL query, construct and populate list of UserCommentsVM within your controller action.

var UserCommentsVMs = new List<UserCommentsVM>();
//...
//code logic here, query db and construct required view model
//...
return View(UserCommentsVMs);

In view page, specify the @model declaration to indicate that the view will be expecting several UserCommentsVM objects

@model IEnumerable<UserCommentsVM>