C# LINQ MRP物料需求运算模板代码

        /// <summary>
        /// MRP运算
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<res_BOMList> MRPComputing(List<MRPModel> input)
        {
            var q_BOM = _Database.GetQueryable<tb_BOM>();//BOM主表
            var q_BOMList = _Database.GetQueryable<tb_BOMList>();//BOM明细表
            var q_Product = _Database.GetQueryable<dt_CustomerProduct>();//产品表
            var q_Inventory = _Database.GetQueryable<tb_Inventory>();//库存表

            var BOMs = input.Where(w => !String.IsNullOrWhiteSpace(w.BOMID))
                .Select(s => s.BOMID).Distinct().ToList();

            //var periodId = DateTime.Today.ToString("yyyyMM");
            //q_Inventory = q_Inventory.Where(w => w.PeriodId == periodId);

            q_BOM = q_BOM.Where(w => BOMs.Contains(w.BOMID));

            var listJoin = (from m in q_BOM //BOM主表
                            join d in q_BOMList on m.BOMID equals d.BOMID //BOM明细表

                            join p in q_Product on d.ProductCode equals p.ProductCode into tmpProduct//产品表
                            from p in tmpProduct.DefaultIfEmpty()

                            join b in q_Inventory on d.ProductCode equals b.ProductCode into tmp//库存表
                            from b in tmp.DefaultIfEmpty()

                            select new res_BOMList
                            {
                                ProductCode = p.ProductCode,
                                ProductName = p.ProductName,
                                CostPrice = d.CostPrice,//成本单价
                                StockBalance = b == null ? 0 : b.Quantity,//库存数                                                          
                                Barcode = d.Barcode,
                                BOMID = d.BOMID,
                                BOMQuantity = d.Quantity,
                                isid = d.isid,
                                CreatedBy = Loginer.CurrentUser.Account,
                                CreationDate = DateTime.Now,
                                LastUpdateDate = DateTime.Now,
                                LastUpdatedBy = Loginer.CurrentUser.Account,
                                Remark = "MRP运算结果",
                            }).ToList();

            //计算物料需求
            (from a in listJoin
             join b in input on a.BOMID equals b.BOMID
             select new { a, b }).ForEach(i =>
             {
                 i.a.Quantity = i.a.BOMQuantity * i.b.Quantity;//需求数量=BOM物料数量*成品数量
                 i.a.CostAmount = i.a.CostPrice * i.b.Quantity;//成本金额=物料成本*成品数量
                 i.a.BalanceGap = i.a.StockBalance - i.a.Quantity;//缺口=库存数-需求
             });

            //返回查询结果
            return listJoin.ToList();
        }