需要将 sql 服务器查询更改为 linq

本文关键字：linq 查询 服务器 sql | 更新日期: 2023-09-27 18:29:19

select TeamName, [Description], COUNT(u.UserId)
from Team t
left outer join [User] u on u.TeamId=t.TeamId
group by TeamName, Description, UserId

到目前为止，我有这么多，但无法做到这一点。

var countUser = (from t in db.Teams
                 join u in db.Users on u.TeamId equals t.TeamId
                 group TeamName, Description, UserId by select  
                 new
                 {
                     u.UserId
                 }).Count();

需要将 sql 服务器查询更改为 linq

这应该可以做到：

Teams.Join(Users.DefaultIfEmpty().
        t => t.TeamId,
        u => u.TeamId,
        (t, u) => new { t.TeamName, t.Description, UserId = u == null ? null:(int?)u.UserId })
    .GroupBy(x => x)
    .Select(g => new { g.Key.TeamName, g.Key.Description, Count = g.Count() });

RePierre 我将窃取您的部分答案(+1(，因为我想我理解 OP 在说什么，尽管问题文本没有传达它。

你可以做这样的事情：

// Model class for View
public class UsersPerTeamCount
{
    public string TeamName { get; set; }
    public string Description { get; set; }
    public int Count { get; set; }
}
// ...
public ActionResult PlayersPerTeam()
{
    var model = from t in db.Teams
                    join u in db.Users on t.TeamId equals u.TeamId into joinedRecords
                    select new UsersPerTeamCount()
                    {
                        Name = t.TeamName,
                        Description = t.Description,
                        PlayerCount = joinedRecords.Count()
                    };
    return View(model);
}

至于在OP评论中"请尝试像......"一样写......"这只是语法上的区别，你用哪种方式编写它并不重要 - 无论是流畅的还是查询语法(至少我认为它被称为查询语法(

相关文章：