我必须找回股票和最近的4家供应商。 我在一个清单中得到了库存。 与另一个列表中的供应商订购。 想要获得所有股票的合并结果与他们最近的4家供应商的购买(如果有的话)。 我对LINQ不好,这也是NHibernate
这是我最新的工作。 求如何编写第三个LINQ sql来获取此信息。
IEnumerable<StockDetailReportModal> stockDetail = Session.Query<Stock>() .Where(predicate) .ToList() .Select(n => new StockDetailReportModal { Id = n.Id, Number = n.Number, PaddedNumber = n.PaddedNumber, Buyer = n.Buyer == null ? string.Empty : n.Buyer.FullName, SalesTaxCode = n.SalesTaxCode == null ? null : n.SalesTaxCode.Code, PurchasingUnitOfMeasure = n.PurchasingUnitOfMeasure, InventoryUnitOfMeasure = n.InventoryUnitOfMeasure, CatalogueDescription = n.CatalogueDescription, BrandDescription = n.BrandDescription, EconomicOrderQuantity = n.EconomicOrderQuantity, LastYearPurchasePrice = n.LastYearPurchasePrice, ThisYearIssuePrice = n.ThisYearIssuePrice, NextYearIssuePrice = n.NextYearIssuePrice, Description = n.StockCommodityCode == null ? string.Empty : n.StockCommodityCode.Code + "-" + n.StockCommodityCode.Description, }).OrderBy(o => o.VendorNumber); var vendorhistoryList = Session.Query<PurchaseOrderLineItem>() .Where(pOpredicate) .Where(p => p.Stock.Number != null) .ToList() .Select(c => new { stockId = c.Id, LastPurchaseDate = c.PurchaseOrder.OrderDate.ToString("yyyy/MM/dd"), VendorNumber = c.PurchaseOrder.PurchaseOrderVendor.Vendor.Number, LastPurchasePrice = c.UnitPrice, LastTransactionDate = c.LastTransactionDate != null ? c.LastTransactionDate.Value.ToString("yyyy/MM/dd") : null, LeadTimeDays = GetDays(c.PurchaseOrder.OrderDate, c.LastTransactionDate), }).GroupBy(d => d.VendorNumber).Select(gpo => gpo .OrderByDescending(x => x.LastPurchaseDate) .Take(4));当vendorhistoryList对象没有显示任何属性时,我的问题从这里开始。 我在这做错了什么? 我试图在stock.id上与vendorhistoryList一起加入stockDetail,其中包含一个或多个最近有4个供应商的库存行。 请建议我该怎么办?
这是我需要的最后一部分。
var stockdetailResult = from sd in stockDetail join vh in vendorhistoryList on sd.Id equals vh.Id into sv from vs in sv.DefaultIfEmpty() ??? .select ( c=> c.fields1....c.fields2...)I have to retrieve stocks and recent 4 vendors. I got stock in one list. Got purchase order with vendors in another list. Want to get a merge result of all stocks with their recent 4 vendors in purchasing if any. I am not good with LINQ and this is NHibernate as well
This is my updated work. Seeking how to write third LINQ sql to get this.
IEnumerable<StockDetailReportModal> stockDetail = Session.Query<Stock>() .Where(predicate) .ToList() .Select(n => new StockDetailReportModal { Id = n.Id, Number = n.Number, PaddedNumber = n.PaddedNumber, Buyer = n.Buyer == null ? string.Empty : n.Buyer.FullName, SalesTaxCode = n.SalesTaxCode == null ? null : n.SalesTaxCode.Code, PurchasingUnitOfMeasure = n.PurchasingUnitOfMeasure, InventoryUnitOfMeasure = n.InventoryUnitOfMeasure, CatalogueDescription = n.CatalogueDescription, BrandDescription = n.BrandDescription, EconomicOrderQuantity = n.EconomicOrderQuantity, LastYearPurchasePrice = n.LastYearPurchasePrice, ThisYearIssuePrice = n.ThisYearIssuePrice, NextYearIssuePrice = n.NextYearIssuePrice, Description = n.StockCommodityCode == null ? string.Empty : n.StockCommodityCode.Code + "-" + n.StockCommodityCode.Description, }).OrderBy(o => o.VendorNumber); var vendorhistoryList = Session.Query<PurchaseOrderLineItem>() .Where(pOpredicate) .Where(p => p.Stock.Number != null) .ToList() .Select(c => new { stockId = c.Id, LastPurchaseDate = c.PurchaseOrder.OrderDate.ToString("yyyy/MM/dd"), VendorNumber = c.PurchaseOrder.PurchaseOrderVendor.Vendor.Number, LastPurchasePrice = c.UnitPrice, LastTransactionDate = c.LastTransactionDate != null ? c.LastTransactionDate.Value.ToString("yyyy/MM/dd") : null, LeadTimeDays = GetDays(c.PurchaseOrder.OrderDate, c.LastTransactionDate), }).GroupBy(d => d.VendorNumber).Select(gpo => gpo .OrderByDescending(x => x.LastPurchaseDate) .Take(4));My problem starts here when vendorhistoryList object does not show any property. What I am doing wrong here? I am trying to left join stockDetail with vendorhistoryList on stock.id with 1 or more stock rows with upto 4 recent vendors. Please suggest what should I do?
Here is the last part I need.
var stockdetailResult = from sd in stockDetail join vh in vendorhistoryList on sd.Id equals vh.Id into sv from vs in sv.DefaultIfEmpty() ??? .select ( c=> c.fields1....c.fields2...)最满意答案
这是我遇到的一个非常类似的问题,所以回答了这个问题,也可以在这里应用。 附上该问题的链接。
交易日期最后4家供应商的产品
This was a very similar problem I was facing so just answered to that question which can be applied here as well. Attaching a link of that question.
Product with last 4 vendors on transaction date
更多推荐
发布评论