如何在视图中展示多实体数据？以User与Comment实体为例

1. 创建DTO类（数据传输对象） #

你的SQL查询返回的是Comment与User的组合数据，但现有Comment实体没有用户名称字段，所以需要新建一个DTO来映射查询结果：

public class CommentWithUserDto
{
    public int CommentID { get; set; }
    public int ProductID { get; set; }
    public int UserID { get; set; }
    public string Name { get; set; } = string.Empty;
    public string CommentText { get; set; } = string.Empty;
    public DateTime DateAdded { get; set; }
}

2. 编写数据访问逻辑 #

方式1：ADO.NET实现 #

在数据访问层执行SQL查询并映射到DTO：

public List<CommentWithUserDto> GetCommentsByProductId(int productId)
{
    var comments = new List<CommentWithUserDto>();
    // 替换为你的数据库连接字符串
    using (var conn = new SqlConnection("YourConnectionString"))
    {
        conn.Open();
        var sql = @"SELECT CommentID,
                            ProductID,
                            C.UserID,
                            U.Name,
                            Text AS CommentText,
                            DateAdded
                    FROM [Comment] C
                    JOIN [User] U ON C.UserID = U.UserID
                    WHERE ProductID = @ProductID";
        using (var cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.AddWithValue("@ProductID", productId);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    comments.Add(new CommentWithUserDto
                    {
                        CommentID = reader.GetInt32(reader.GetOrdinal("CommentID")),
                        ProductID = reader.GetInt32(reader.GetOrdinal("ProductID")),
                        UserID = reader.GetInt32(reader.GetOrdinal("UserID")),
                        Name = reader.GetString(reader.GetOrdinal("Name")),
                        CommentText = reader.GetString(reader.GetOrdinal("CommentText")),
                        DateAdded = reader.GetDateTime(reader.GetOrdinal("DateAdded"))
                    });
                }
            }
        }
    }
    return comments;
}

方式2：Entity Framework Core实现 #

如果使用EF Core，可直接用FromSqlRaw执行查询：

public async Task<List<CommentWithUserDto>> GetCommentsByProductIdAsync(int productId)
{
    var comments = await _context.Set<CommentWithUserDto>()
        .FromSqlRaw(@"SELECT CommentID,
                            ProductID,
                            C.UserID,
                            U.Name,
                            Text AS CommentText,
                            DateAdded
                    FROM [Comment] C
                    JOIN [User] U ON C.UserID = U.UserID
                    WHERE ProductID = {0}", productId)
        .ToListAsync();
    return comments;
}
// 注意：需在DbContext中配置CommentWithUserDto为无键实体，或直接使用查询投影

3. 控制器逻辑 #

同步请求（页面刷新） #

处理按钮点击的同步请求，将数据传递给视图：

public IActionResult ShowComments(int productId)
{
    var comments = _commentRepository.GetCommentsByProductId(productId);
    return View(comments);
}

异步请求（无刷新加载） #

如果需要无刷新效果，控制器返回JSON数据：

public IActionResult GetComments(int productId)
{
    var comments = _commentRepository.GetCommentsByProductId(productId);
    return Json(comments);
}

4. 视图渲染实现 #

同步刷新场景 #

创建ShowComments.cshtml视图，接收List<CommentWithUserDto>模型：

@model List<CommentWithUserDto>

<h3>商品评论</h3>
@if (Model.Count == 0)
{
    <p>暂无评论</p>
}
else
{
    <table class="table">
        <thead>
            <tr>
                <th>评论ID</th>
                <th>用户名称</th>
                <th>评论内容</th>
                <th>评论时间</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var comment in Model)
            {
                <tr>
                    <td>@comment.CommentID</td>
                    <td>@comment.Name</td>
                    <td>@comment.CommentText</td>
                    <td>@comment.DateAdded.ToString("yyyy-MM-dd HH:mm:ss")</td>
                </tr>
            }
        </tbody>
    </table>
}

在按钮所在页面添加跳转链接：

<a asp-action="ShowComments" asp-route-productId="1" class="btn btn-primary">查看评论</a>

无刷新AJAX场景 #

在按钮所在页面添加容器和JS逻辑：

<div id="commentsContainer"></div>
<button onclick="loadComments(1)" class="btn btn-primary">查看评论</button>

<script>
function loadComments(productId) {
    fetch(`/YourController/GetComments?productId=${productId}`)
        .then(response => response.json())
        .then(data => {
            const container = document.getElementById('commentsContainer');
            if (data.length === 0) {
                container.innerHTML = '<p>暂无评论</p>';
                return;
            }
            let table = '<table class="table"><thead><tr><th>评论ID</th><th>用户名称</th><th>评论内容</th><th>评论时间</th></tr></thead><tbody>';
            data.forEach(comment => {
                const date = new Date(comment.dateAdded);
                const formattedDate = date.toLocaleString();
                table += `<tr>
                            <td>${comment.commentID}</td>
                            <td>${comment.name}</td>
                            <td>${comment.commentText}</td>
                            <td>${formattedDate}</td>
                          </tr>`;
            });
            table += '</tbody></table>';
            container.innerHTML = table;
        })
        .catch(error => console.error('加载评论失败:', error));
}
</script>

内容的提问来源于stack exchange，提问作者DaBeau96