SQL to LINQ语句，包括Group By和Order By

本文关键字：By Group Order 包括 to LINQ 语句 SQL | 更新日期: 2023-09-27 18:03:02

我在下面有一个SQL查询:

SELECT DISTINCT 
    Table1.Description, 
    Table2.AccountId, 
    COUNT(Table2.AccountId) AS Charges 
FROM Table2 
LEFT JOIN Table1 ON Table2.AccountId = Table1.Id 
WHERE Table2.DateTime > '3/1/2014' 
    AND Table2.DateTime < '4/1/2014' 
GROUP BY Table2.AccountId, Table1.Description 
ORDER BY Charges DESC

我正试图将其转换为ASPX.CS代码中的LINQ查询，以填充表并在图表中使用数据。

到目前为止我所拥有的如下:

var resultList = configDB.Table2
    .Where(x => x.DateTime > begDate && x.DateTime < endDate)
    .GroupBy(x => x.AccountID)
    .Select(g => new { Account = g.Key, Charges = g.Count() })
    .OrderByDescending(g => g.Charges)
    .ToList();

这只是表2的部分。我也试图加入表1，并且仍然可以从玩弄代码中获得我无法获得的收费计数。我已经找到了几个帖子和解决方案来单独做每一个，但不是一个答案，他们是通过分组和加入，同时得到一个列的计数。谁能给我一个资源，将帮助或指向正确的方向?

SQL to LINQ语句，包括Group By和Order By

我想出了这个(可能需要一些调整)

var resultList = Table2
            .Where(x => x.DateTime > begDate && x.DateTime < endDate)
            .Join(Table1, t2 => t2.AccountId, t1 => t1.Id,
                (t2, t1) => new { t2.AccountId, t1.Description })
            .GroupBy(x => x.AccountId)
            .Select(g => new { Group = g, Charges = g.Count() })
            .OrderByDescending(g => g.Charges)
            .SelectMany(g => g.Group.Select(x => new { x.Description, x.AccountId, g.Charges }))
            .ToList();

试试这样:

var resultList = configDB.Table2
                        .Where(x => x.DateTime > begDate && x.DateTime < endDate)
                        .GroupJoin(configDB.Table1,
                                    t2 => t2.AccountId,
                                    t1 => t1.Id,
                                    (t2, joined) => new
                                    {
                                        Description = joined.Select(t => t.Description).FirstOrDefault(),
                                        AccountID = t2.AccountId,
                                    })
                        .GroupBy(x => x.AccountID)
                        .Select(g => new
                        {
                            Account = g.Key, 
                            Charges = g.Count(), 
                            Description = g.Select(d=>d.Description).FirstOrDefault()
                        })
                        .OrderByDescending(g => g.Charges)
                        .ToList();
相关文章：