使用linq连接3个列表并计算ID在列表中出现的次数的最佳方法

Question

我需要帮助解决一个问题，以学习LINQ的工作原理。我的想法是将person和customer列表按照personID进行连接，以获取客户的姓名，然后将customer和sale列表按照customerID进行连接，并计算在sale中按照customerID计数的在线商店销售额，以确定哪些人在我们的在线商店下了最多的订单。

任务：确定谁从我们的在线商店下了最多的订单。

public class Person
{
    public int PersonID { get; set; }
    public string PersonType { get; set; }
    public bool NameStyle { get; set; }
    public string Title { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }
    public string Suffix { get; set; }
}

public class Sale
{
    public int SalesOrderId { get; set; }
    public int RevisionNumber { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime DueDate { get; set; }
    public DateTime? ShipDate { get; set; }
    public bool IsOnlineOrder { get; set; }
    public int CustomerId { get; set; }
    public int? SalesPersonId { get; set; } 
    public int? TerritoryID { get; set; }
    public decimal SubTotal { get; set; }
    public decimal TaxAmt { get; set; }
    public decimal Freight { get; set; }
    public decimal TotalDue { get; set; }
}

public class Customer
{
    //CustomerID can belong to a store or a person
    public int CustomerID { get; set; }
    public int? StoreID { get; set; }
    public int? PersonID { get; set; }
    public int? TerritoryID { get; set; }
    public string AccountNumber { get; set; }
}

    public void MostOrders()
    {
        List<Person> per = Data.GetAllPersons();
        List<Sale> sale = Data.GetAllSales();
        List<Customer> cus = Data.GetAllCustomers();

        var join = (from x in per
                    join y in cus
                    on x.PersonID equals y.PersonID
                    join t in sale
                    on y.CustomerID equals t.CustomerId
                    select new
                    {
                        ......
                    });
    }

- NevrMore

Answer 1

我会创建一个类来保存所有的“商店实体”及其销售情况，我认为使用这个类的集合（在我的示例中为StoreEntity）将更容易执行各种数据操作（对于这个任务和未来的任务）。以下是我的建议：

public class StoreEntity
{
    Person PersonEntity { get; set; }
    Customer CustomerEntity { get; set; }
    List<Sale> SalesList { get; set; }

    public StoreEntity(Person p, Customer c,List<Sale> sales)
    {
        this.PersonEntity = p;
        this.CustomerEntity = c;
        this.SalesList = sales;
    }

    public int SalesCount
    {
        get
        {
            if (SalesList == null)
            {
                return 0;
            }
            else
            {
                return SalesList.Count;
            }
        }
    }
}

public List<StoreEntity> Entities { get; set; }
public void MostOrders()
{
    List<Person> per = Data.GetAllPersons();
    List<Sale> sale = Data.GetAllSales();
    List<Customer> cus = Data.GetAllCustomers();

    Entities = new List<StoreEntity>();
    foreach (Person p in per)
    {
        var cust = cus.Where(x => x.PersonID == p.PersonID).Select(x => x).SingleOrDefault();
        if (cust != null)
        {
            var sales = sale.Where(x => x.CustomerId == cust.CustomerID).ToList();
            StoreEntity entity = new StoreEntity(p, cust, sales);
            Entities.Add(entity);
        } 
    }

    // now you have list of all entities with their sales
    // it will be musch easy to manipulate with linq
    // you can do this:
    Entities = Entities.OrderBy(x => x.SalesCount).ToList();
    // or this...
    var bestEntity = Entities.Max(x => x.SalesCount);
}

- Jonathan Applebaum