我有以下 2 個 DTO，用于在 API 中回傳資訊，以過濾掉我不希望客戶端能夠查看的欄位
public record CommentDto
{
public int? CommentId { get; set; }
public string AuthorUid { get; set; }
public string Text { get; set; }
public int? ParentCommentId { get; set; }
public string CommentPn { get; set; }
public virtual UserDto User { get; set; }
}
public record UserDto
{
public string Uid { get; set; }
public string Username { get; set; }
}
我正在使用以下命令查詢 postgres 資料庫：
var comments = await dbSet.
Where(c => c.commentPn == sentPn)
.Select(c => new CommentDto
{
CommentId = c.CommentId,
CommentPn = c.CommentPn,
AuthorUid = c.AuthorUid,
Text = c.Text,
ParentCommentId = c.ParentCommentId,
User = new UserDto
{
Username = dbSet.Select(u => u.User.Username).Single(),
Uid = dbSet.Select(u => u.User.Uid).Single()
},
}).ToListAsync();
雖然這可行并且回傳了正確的資料，但我注意到我認為查詢中包含了不必要的呼叫。
SELECT d1.comment_id AS "CommentId", d1.comment_pn AS "CommentNiin", d1.author_uid AS "AuthorUid", d1.comment_text AS "Text", d1.parent_comment_id AS "ParentCommentId", (
SELECT u.username
FROM database_item_comments AS d
INNER JOIN users AS u ON d.author_uid = u.uid
LIMIT 1) AS "Username", (
SELECT u0.uid
FROM database_item_comments AS d0
INNER JOIN users AS u0 ON d0.author_uid = u0.uid
LIMIT 1) AS "Uid"
FROM database_item_comments AS d1
我知道這是由于我檢索用戶值的方式非常低效，在只對用戶表進行一次呼叫的同時完成此查詢的正確方法是什么？
最好直接查詢評論表，并回傳完整的評論物體無 DTO，而不必映射變數，同時為評論創建 UserDto 并映射值
uj5u.com熱心網友回復：
您的 Comment 物體應該具有對 User 物體的參考，這將使您的查詢更正為：
var comments = await dbSet.
Where(c => c.commentPn == sentPn)
.Select(c => new CommentDto
{
CommentId = c.CommentId,
CommentPn = c.CommentPn,
AuthorUid = c.AuthorUid,
Text = c.Text,
ParentCommentId = c.ParentCommentId,
User = new UserDto
{
UId = c.User.UId,
Username = c.User.Username
});
}).ToListAsync();
如果您在資料庫中有多個評論來填充用戶 DTO，那么您的示例可能會失敗，您實際上是在告訴它加載所有評論 ( DbSet.Select) 以獲取用戶并期望通過Single(). 然后是您執行了兩次，一次是選擇 ID，然后是選擇名稱。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/414961.html
標籤：
上一篇：Go中的嵌入與繼承
下一篇：物體框架連接表c#