LINQ 左加入分组依据和计数

本文关键字：LINQ | 更新日期: 2023-09-27 18:34:37

我对 LINQ 查询很感兴趣，并且有点坚持找出正确的方法来获取关联条目的计数。

我有以下 LINQ 查询和

var result = (from OR in orders
                join OE in order_entries on OR.id equals OE.order_id into temp
                from LOE in temp.DefaultIfEmpty()
                group LOE  by new {OR.user_id, OR.site } into g
                select new {
                   col1 = g.Key.user_id,
                   col2 = g.Key.site,
                   count = g.Count() ,
                   cost = g.Sum( oe => oe.cost)
                }
);

这变成了

SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [user_id], 
    [GroupBy1].[K2] AS [site], 
    [GroupBy1].[A1] AS [C2], 
    [GroupBy1].[A2] AS [C3]
    FROM ( SELECT 
        [Extent1].[user_id] AS [K1], 
        [Extent1].[site] AS [K2], 
        COUNT(1) AS [A1], 
        SUM([Extent2].[cost]) AS [A2]
        FROM  [dbo].[orders] AS [Extent1]
        LEFT OUTER JOIN [dbo].[order_entries] AS [Extent2] ON [Extent1].[id] = [Extent2].[order_id]
        GROUP BY [Extent1].[user_id], [Extent1].[site]
    )  AS [GroupBy1]

我在这里试图实现的是将 Count(1( 替换为 Count([Extent2]。id]( 所以如果没有与订单关联的条目，我想显示 0 而不是 1。

有人可以帮我更新 LINQ 查询以实现此目的吗？

更新：

替换为以下内容将返回我想要的结果，但这也会使我的SQL查询执行速度变慢。

g.Where(i => i.orders != null).Count(),

LINQ 左加入分组依据和计数

最简单的方法是使用子查询：

    var qry = from o in orders
        select new {
            oid = o.ID,
            uid = o.UserId,
            site = o.Site,
            count = order_entries.Where(oe=>oe.OrderId == o.ID).Count(),
            cost = order_entries.Where(oe=>oe.OrderId == o.ID).Sum(oe=>oe.Cost)
            };

但是，如果您想连接两个数据集，请使用以下命令：

    var qry = (from o in orders join oe in order_entries on o.ID equals oe.OrderId into grp 
        from g in grp.DefaultIfEmpty()
        select new{
            oid = o.ID,
            uid = o.UserId,
            site = o.Site,
            count = grp.Count(),
            cost = grp.Sum(e=>e.Cost)
            }).Distinct();

我坚信可以使用组语句以最简单的方式编写第二个查询。

下面是一个完整的 LinqPad 示例：

void Main()
{
    List<TOrder> orders = new List<TOrder>{
        new TOrder(1, 1, "Site1"),
        new TOrder(2, 1, "Site1"),
        new TOrder(3, 2, "Site2"),
        new TOrder(4, 2, "Site2"),
        new TOrder(5, 3, "Site3")
        };
    List<TOrderEntry> order_entries = new List<TOrderEntry>{
        new TOrderEntry(1, 1, 5.5),
        new TOrderEntry(2, 1, 6.2),
        new TOrderEntry(3, 1, 4.9),
        new TOrderEntry(4, 1, 55.15),
        new TOrderEntry(5, 1, 0.97),
        new TOrderEntry(6, 2, 2.23),
        new TOrderEntry(7, 2, 95.44),
        new TOrderEntry(8, 2, 3.88),
        new TOrderEntry(9, 2, 7.77),
        new TOrderEntry(10, 3, 25.23),
        new TOrderEntry(11, 3, 31.13),
        new TOrderEntry(12, 4, 41.14)
        };
//      var qry = from o in orders
//          select new {
//              oid = o.ID,
//              uid = o.UserId,
//              site = o.Site,
//              count = order_entries.Where(oe=>oe.OrderId == o.ID).Count(),
//              cost = order_entries.Where(oe=>oe.OrderId == o.ID).Sum(oe=>oe.Cost)
//              };
//      qry.Dump();
        var qry = (from o in orders join oe in order_entries on o.ID equals oe.OrderId into grp 
            from g in grp.DefaultIfEmpty()
            //group g by g into ggg
            select new{
                oid = o.ID,
                uid = o.UserId,
                site = o.Site,
                count = grp.Count(),
                cost = grp.Sum(e=>e.Cost)
                }).Distinct();
        qry.Dump();
}

// Define other methods and classes here
class TOrder
{
    private int iid =0;
    private int uid =0;
    private string ssite=string.Empty;
    public TOrder(int _id, int _uid, string _site)
    {
        iid = _id;  
        uid = _uid;
        ssite = _site;
    }
    public int ID
    {
        get{return iid;}
        set{iid = value;}
    }
    public int UserId
    {
        get{return uid;}
        set{uid = value;}
    }
    public string Site
    {
        get{return ssite;}
        set{ssite = value;}
    }
}

class TOrderEntry
{
    private int iid = 0;
    private int oid = 0;
    private double dcost = .0;
    public TOrderEntry(int _iid, int _oid, double _cost)
    {
        iid = _iid;
        oid = _oid;
        dcost = _cost;
    }
    public int EntryId
    {
        get{return iid;}
        set{iid = value;}
    }
    public int OrderId
    {
        get{return oid;}
        set{oid = value;}
    }
    public double Cost
    {
        get{return dcost;}
        set{dcost = value;}
    }
}

LINQ 左 加入分组依据和计数

LINQ 左加入分组依据和计数