3表中的Linq分组 [英] Grouping with 3 tables in Linq

问题描述

我很新的LINQ，并具有极大的麻烦我的分组表，所以一切都加在一起。我试图让这显示所有已购买超过$ 50个，即使他们有多次购买的客户。在SQL数据库使用LINQ。以下是我迄今为止，请大家帮忙。

 公共类TopCustomerVM
 {
    公共字符串名称{;组; }
    公共小数DollarsSold {获得;组; }

    公共静态列表＆LT; TopCustomerVM＆GT; GetResults（）
    {
        ACEEntities DB =新ACEEntities（）;
        返回（从C在db.Customers
                加盟澳中db.Orders上c.CustomerId等于o.CustomerId
                加入l在db.OrderLines上o.OrderId等于l.OrderId

                成X个
                选择新TopCustomerVM {
                        名称= c.FirstName + c.LastName，
                        DollarsSold = x.Sum（ASDF =＆GT; asdf.UnitCost * asdf.Quantity）
                       }）
                       .OrderByDescending（LKJ =＆GT; lkj.DollarsSold）
                       。凡（LKJ =＆GT; lkj.DollarsSold→50）
                       .ToList（）;
    }
}
 

解决方案

我相信这样的事情会给你所需的输出：

 从纪录
    （从C的客户
    加盟澳在订单上c.Id等于o.CustomerId
    加入醇的OrderLines上o.Id等于ol.OrderId
    让customerAndLine =新{名称= c.FirstName +''+ c.LastName，DollarsSold = ol.UnitCost * ol.Quantity}
    组customerAndLine通过customerAndLine.Name到GRP
    选择新{名称= grp.Key，DollarsSold = grp.Sum（R =＆GT; r.DollarsSold）}）
其中，record.DollarsSold＆GT; 50
排序依据record.DollarsSold降
选择记录
 

I am very new to LINQ and having great trouble grouping my tables so everything adds up together. I am trying to get this to display all the customers that have purchased more than $50 EVEN IF THEY HAVE MULTIPLE PURCHASES. in the SQL database using linq. Here is what I have so far, please help.

 public class TopCustomerVM
 {
    public string Name { get; set; }
    public decimal DollarsSold { get; set; }

    public static List<TopCustomerVM> GetResults()
    {
        ACEEntities db = new ACEEntities();
        return (from c in db.Customers
                join o in db.Orders on c.CustomerId equals o.CustomerId
                join l in db.OrderLines on o.OrderId equals l.OrderId

                into x
                select new TopCustomerVM {                           
                        Name = c.FirstName + c.LastName,
                        DollarsSold = x.Sum(asdf => asdf.UnitCost * asdf.Quantity)
                       })
                       .OrderByDescending(lkj => lkj.DollarsSold)
                       .Where(lkj => lkj.DollarsSold > 50)
                       .ToList();            
    }
}

解决方案

I believe something like this will give you the desired output:

from record in 
    (from c in Customers
    join o in Orders on c.Id equals o.CustomerId
    join ol in OrderLines on o.Id equals ol.OrderId
    let customerAndLine = new { Name = c.FirstName + ' ' + c.LastName, DollarsSold = ol.UnitCost * ol.Quantity }
    group customerAndLine by customerAndLine.Name into grp  
    select new { Name = grp.Key, DollarsSold = grp.Sum(r => r.DollarsSold) })
where record.DollarsSold > 50
orderby record.DollarsSold descending
select record

这篇关于3表中的Linq分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！