c# - LINQ Left 加入 group by 并计数

Question

我对 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(),

Answer 1

最简单的方法是使用subqueries :

    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();

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

这是一个完整的 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;}
    }

}