linq和ef关于group by取最大值的两种写法
//LINQ: var temp = from p in db.jj_Credentials group p by p.ProfessionID into g select new { g.Key, MaxPrice = g.Max(p => p.CredentialsRankID) }; //EF: var temp1 = db.jj_Credentials.GroupBy(m => m.ProfessionID).Select(m => m.Max(o => o.CredentialsRankID)); foreach(var p in temp1) { Response.Write(p); } //补充:取最大值后取得当前完整记录的方法,两种写法: //写法1 var list1 = db.jj_Credentials.Where(m => m.UserId == UserContext.UserID && m.IsAudit).OrderByDescending(m => m.CredentialsRankID).ToList().GroupBy(x => x.ProfessionID) .SelectMany(t => t.Select((b, i) => new { b, i }).Where(m => m.i == 0)).Select(m => m.b); foreach (var i in list1) { Response.Write(string.Format("{0} {1} {2} {3}<br>", i.CredentialsID, i.UserId, i.CredentialsRankID, i.ProfessionID)); } //写法2 var list = db.jj_Credentials.Where(m => m.UserId == UserContext.UserID && m.IsAudit).OrderByDescending(x => x.CredentialsRankID).ToList().GroupBy(x => x.ProfessionID) .Select(g => new { g, count = g.Count() }) .SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new { CredentialsID = j.CredentialsID, UserID = j.UserId, CredentialsRankID = j.CredentialsRankID, ProfessionID = j.ProfessionID, rn = i }).Where(x => x.rn == 1)); //打印 foreach (var i in list) { Response.Write(string.Format("{0} {1} {2} {3}<br>", i.CredentialsID, i.UserID, i.CredentialsRankID, i.ProfessionID)); }