转换简单的左外连接和组的SQL语句到Linq

本文关键字：SQL 语句 Linq 连接 简单 转换 | 更新日期: 2023-09-27 18:16:55

2个表: User和Alarm

表:用户UserID (int),姓名(varchar)

表:报警AssignedTo (int),解析(bool)

查询: 

SELECT u.Fullname, COUNT(resolved) as Assigned, SUM(CONVERT(int,Resolved)) as Resolved, COUNT(resolved) -  SUM(CONVERT(int,Resolved)) as Unresolved
FROM Alarm i LEFT OUTER JOIN Users u on i.AssignedTo = u.UserID
GROUP BY u.Fullname

结果:

Fullname  Assigned  Resolved  Unresolved
User1     204       4         200
User2     39        9         30
User3     235       200       35
User4     1         0         1
User5     469       69        400

对于我的生活，我不知道如何把它变成一个Linq查询。我在使用分组功能时遇到了麻烦。我看了无数的例子，没有一个是我的左外连接与分组的组合，或者它们太复杂了，我不知道如何使它与我的工作。任何帮助在这里将非常感激!!

更新:我可能没说清楚我在找什么。我正在寻找由AssignedTo列分组的警报，这是一个用户id…不过，我想用位于users表中的FullName替换那个userid。有人发布并删除了一些接近的内容，但它给了我用户表中的所有用户，这不是我想要的。

更新2:见下面我的回答

转换简单的左外连接和组的SQL语句到Linq

假设您有以下模型:

这是Alarm的模型:

public class Alarm
{
    public int id { get; set; }
    public int AssignedTo { get; set; }
    [ForeignKey("AssignedTo")] 
    public virtual User User { get; set; }
    public bool Resolved { get; set; }
}

这是User的模型:

public class User
{
    public int UserID { get; set; }
    public string FullName { get; set; }
    public virtual ICollection<Alarm> Alarms { get; set; }
    public User()
    {
        Alarms = new HashSet<Alarm>();
    }
}

这个模型将保存每个用户的告警统计信息:

public class UserStatistics
{
    public string FullName { get; set; }
    public int Assigned { get; set; }    
    public int Resolved { get; set; }    
    public int Unresolved { get; set; }    
}

你可以这样做:

var query = context.Users.Select(
    user =>
        new UserStatistics
        {
            FullName = user.FullName,
            Assigned = user.Alarms.Count,
            Resolved = user.Alarms.Count(alarm => alarm.Resolved),
            Unresolved = user.Alarms.Count(alarm => !alarm.Resolved)
        });

var result = query.ToList();

顺便说一下，您还可以修改查询并删除Unresolved = user.Alarms.Count(alarm => !alarm.Resolved)，然后使Unresolved属性成为计算属性，如下所示:

public class UserStatistics
{
    public string FullName { get; set; }
    public int Assigned { get; set; }    
    public int Resolved { get; set; }    
    public int Unresolved
    {
        get { return Assigned - Resolved; }
    }
}

这会使生成的SQL查询更简单。

我终于想通了。

: 

var results = alarms.GroupBy(x => x.AssignedTo)
.Join(users, alm => alm.Key , usr => usr.UserID, (alm, usr) => new {
    Fullname = usr.FullName,AssignedNum = alm.Count(),
    Resolved = alm.Where(t=>t.resolved == true).Select(y => y.resolved).Count(), 
    Unresolved = alm.Where(t=>t.resolved == false).Select(y => y.resolved).Count() });

reproductthis: 

SELECT u.Fullname, COUNT(resolved) as Assigned, SUM(CONVERT(int,Resolved)) as Resolved, 
       COUNT(resolved) -  SUM(CONVERT(int,Resolved)) as Unresolved
FROM Alarm i LEFT OUTER JOIN Users u on i.AssignedTo = u.UserID
GROUP BY u.Fullname

结果按assigndto (int)分组，但assigndto未被选中。相反，FullName是从连接的用户表中选择的。

非常感谢每个试图帮助的人!我从你的回答中学到了很多。

为加分点，我将如何写我的lamdbda答案在SQL语法?

试试这个:

from u in context.User
join a in context.Alarm on u.UserID equals a.AssignedTo into g1
from g2 in g1.DefaultIfEmpty()
group g2 by u.Fullname into grouped
select new { Fullname = grouped.Key, Assigned = grouped.Count(t=>t.Resolved != null), Resolved = grouped.Sum
                                    (t => int.Parse(t.Resolved)), Unresolved = (grouped.Count(t=>t.Resolved != null) - grouped.Sum
                                    (t => int.Parse(t.Resolved)))}

我猜不一定要使用"Grouping" for this query in Linq，因为"LEFT JOIN" + "GROUP BY"的组合将它们改为"INNER JOIN"。

    var results =
        from u in users
        join a in alarms on u.UserID equals a.AssignedTo into ua
        select new
        {
            Fullname = u.FullName,
            Assigned = ua.Count(),
            Resolved = ua.Count(a => a.Resolved),
            Unresolved = ua.Count(a => !a.Resolved)
        };
        foreach (var r in results)
        {
            Console.WriteLine(r.Fullname + ", " + r.Assigned + ", " + r.Resolved + ", " + r.Unresolved);
        }
相关文章：