Linq多表合一查询

这里有四张表

//进货
public class Buy
{
	//进货表id
	public int BuyID { get; set; }
	//供应商id
	public int SupID { get; set; }
}

//进货明细
public class BuyItems
{
	//进货明细id
	public int BID { get; set; }
	//进货id
	public int BuyID { get; set; }
	//商品id
	public int ProductID { get; set; }
	//进货总数
	public int BuyCount { get; set; }

}

//退货
public class Returns
{
	//退货id
	public int ReturnID { get; set; }
	//供应商id
	public int SupID { get; set; }
}

//退货明细
public class ReturnItems
{
	//退货明细id
	public int RID { get; set; }
	//退货id
	public int ReturnID { get; set; }
	//商品id
	public int ProductID { get; set; }
	//退货总数
	public int ReturnCount { get; set; }
}

现在需要查询出供应商的所有进、退货记录，并按供应商与商品分组，最后按供应商与商品id的顺序排列，查询结果汇总在一张表中。

//BuyTable为进货表(Buy)
//BuyItemsTable为进货明细表(BuyItems)
//ReturnsTable为退货表(Returns)
//ReturnItemsTable为退货明细表(ReturnItems)

var query = BuyTable.Join(BuyItemsTable, a => a.BuyID, b => b.BuyID, (a, b) => new
{
	a.SupID,
	b.ProductID,
	BuyCount = b.BuyCount,
	ReturnCount = 0,
}).Concat(ReturnsTable.Join(ReturnItemsTable, a => a.ReturnID, b => b.ReturnID, (a, b) => new
{
	a.SupID,
	b.ProductID,
	BuyCount = 0,
	ReturnCount = b.ReturnCount,
})).GroupBy(p => new { p.SupID, p.ProductID }).Select(p => new
{
	p.Key.SupID,
	p.Key.ProductID,
	SumBuy = p.Sum(s => s.BuyCount),
	SumReturn = p.Sum(s => s.ReturnCount)
}).OrderBy(p => p.SupID).ThenBy(p => p.ProductID);