我有兩張桌子
上學
| 嘗試 ID | 用戶身份 | 評分 |
|---|---|---|
| 1 | 1 | 15 |
| 2 | 1 | 20 |
網路用戶
| 用戶身份 | 名 | 姓 |
|---|---|---|
| 1 | ... | ... |
| 2 | ... | ... |
我想從資料庫（mysql）中獲得所有用戶全名的最佳評價。
這是我的 LINQ：
from attempts in (from q in Schoolattempts
group q by q.UserId into g
select g.OrderByDescending(c => c.Rating).First())
join users in Aspnetusers on attempts.UserId equals users.Id
select new
{
FullName = users.LastName " " users.FirstName " " users.MiddleName,
Rating = attempts.Rating
}
但 EF Core 無法將其轉換為 SQL；
這是錯誤：
InvalidOperationException: The LINQ expression 'DbSet<Schoolattempts>()
.GroupBy(s => s.UserId)
.Select(g => g
.AsQueryable()
.OrderByDescending(e => e.Rating)
.First())
.Join(
inner: DbSet<Aspnetusers>(),
outerKeySelector: e0 => e0.UserId,
innerKeySelector: a => a.Id,
resultSelector: (e0, a) => new TransparentIdentifier<Schoolattempts, Aspnetusers>(
Outer = e0,
Inner = a
))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
uj5u.com熱心網友回復：
您需要記住，執行 GroupBy 陳述句后，它將在鍵下對多行進行分組。
這里有兩個例子，我認為會給你想要的結果，但是設定它希望更容易理解發生了什么。
獲取帶有總評分的串列：
Schoolattempts
.Join(
Aspnetusers,
x => x.UserID,
y => y.UserID,
(x, y) => new {
Name = y.LastName " " y.FirstName " " y.MiddleName,
Id = x.UserID,
Rating = x.Rating
})
.GroupBy(x => x.User) //Grouping the data
.Select(x => new {
Name = x.First().Name,
Rating= x.Sum(y => y.Rating) //Getting the sum of the ratings of the user
})
.OrderByDescending(x => x.Rating) //Order the result from highest to lowest rating
獲取每個用戶的最高評分而不是總和的串列，最后一個選擇塊將如下所示：
.Select(x => new {
Name = x.First().Name,
Rating= x.Max(y => y.Rating) //Getting the highest rating of the user
})
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/522806.html
標籤：实体框架林克实体框架核心pomelo-entityframeworkcore-mysql
上一篇：如何逐行從簡單文本中獲取資料