多表分组，排序，混合查询的linq,lamdba语句

多表分组，排序，混合查询

例如：显示每个人的父亲，对应的分数，考试的科数

linq语句

var query=from u in oae.users
join p in oae.User_Parent on u.Id equals p.UserId into
upjointemp from leftjoin in upjointemp.DefaultFEpty() //left join 语句
join s in oae.User_score on u.Id equals s.UserId
select new
{
UserName=u.UserName,
Father=leftjoin.Father,
Score=s.score
} into jointemp
group jointemp by new {jointemp.UserName,jointemp.Father}
into a
slect new AllScoreModel
{
UserName=a.key.UserName,
Father=a.key.Father ?? "无",
Count=a.Count(),
max=a.Max(b=>b.score),
min=a.Min(b=>b.score),
sum=a.Sum(b=>b.score),
avg=a.Average(b=>b.score)
}
List<AllScoreModel> result=query.ToList();
//lamdba
            var query2 = oae.Users.GroupJoin(oae.User_Parent, a => a.Id, b => b.UserId, (a, b) => new
            {
                user = a,
                parent = b
            }).SelectMany(a => a.parent.DefaultIfEmpty(), (a, b) => new
            {
                uid = a.user.Id,
                UserName = a.user.UserName,
                Father = b.Father
            }).Join(oae.User_Score, a => a.uid, b => b.UserId, (a, b) => new
            {
                UserName = a.UserName,
                Father = a.Father,
                score = b.Score
            }).GroupBy(a => new { a.UserName, a.Father}).Select(a => new AllScoreModel
            {
                UserName = a.Key.UserName,
                Father = a.Key.Father ?? "无父",
                Count = a.Count(),
                max = a.Max(b => b.score),
                min = a.Min(b => b.score),
                sum = a.Sum(b => b.score),
                avg = a.Average(b => b.score)
            });

小结：

1，前台字符串输入提示

@{
            item.sub=item.sub ?? "<span style='color:red;'>暂无科目</span>";
            @Html.Raw(item.sub)      @*作用：让引号里的标签被识别*@
                       }
2， <td>
                  @* 保留2位小数*@
            @item.avg.Value.ToString("f2")
                    @* 四舍五入*@
               @*   @Math.Round((double)item.avg,2)    *@
                </td>