Core EF Outer Join，Count & Group

Question

我正在尝试将此SQL查询转换为核心EF：

SELECT w.IdShippingBatch, w.BookingNumber, COUNT(c.IdShippingOrder) AS ShippingOrders, w.CreatedOn, w.ModifiedOn
    FROM dbo.Shipping`enter code here`Batch AS w LEFT OUTER JOIN
            dbo.ShippingOrders AS c ON w.IdShippingBatch = c.IdShippingBatch
    WHERE (w.IdCompany = 2) AND (w.IdDealer = 1)
    GROUP BY w.IdShippingBatch, w.BookingNumber, w.CreatedOn, w.ModifiedOn

我尝试了多种解决方案，其中包括多种解决方案。我的最新尝试看起来像：

var data = (from w in _context.ShippingBatch
    join c in _context.ShippingOrders on w.IdShippingBatch equals c.IdShippingBatch into t1
    where w.IdCompany == idCompany && w.IdDealer == idDealer
    from t2 in t1.DefaultIfEmpty()
    group t2 by new { w.IdShippingBatch, w.BookingNumber, w.CreatedOn, w.ModifiedOn } into t3
    select new ShippingBatchDTO
    {
        IdShippingBatch = t3.Key.IdShippingBatch,
        BookingNumber = t3.Key.BookingNumber,
        ShippingOrders = t3.Count(),
        CreatedOn = t3.Key.CreatedOn,
        ModifiedOn = t3.Key.ModifiedOn
    });

我也尝试添加t3.count(m => m.something != null)，但这会引发错误。

Answer 1

EF的一个主要点是映射实体之间的关系，以便您可以利用LINQ并让EF组成SQL查询，而不是试图用LINQ-QL替换SQL。

如果您的Shiptshingbatch绘制了一系列运输货物的映射...

var batches = _context.ShippingBatch
  .Where(x => x.IdCompany == idCompany && x.IdDealer == idDealer)
  .Select(x => new ShippingBatchDTO
  {
        IdShippingBatch = x.IdShippingBatch,
        BookingNumber = x.BookingNumber,
        ShippingOrders = x.ShippingOrders.Count(),
        CreatedOn = x.CreatedOn,
        ModifiedOn = x.ModifiedOn    
  }).ToList();

如果您的Shiptshingbatch没有运输货币的集合，但是您的船运货参考了可选的ShippingBatch。

var batches = _context.ShippingOrder
  .Where(x => x.ShippingBatch != null 
    && x.ShippingBatch.IdCompany == idCompany 
    && x.ShippingBatch.IdDealer == idDealer)
  .GroupBy(x => x.ShippingBatch)
  .Select(x => new ShippingBatchDTO
  {
        IdShippingBatch = x.Key.IdShippingBatch,
        BookingNumber = x.Key.BookingNumber,
        ShippingOrders = x.Count(),
        CreatedOn = x.Key.CreatedOn,
        ModifiedOn = x.Key.ModifiedOn    
  }).ToList();

应该希望您朝着正确的方向前进。如果不是，请扩展您的问题以包括您所看到的内容，以及您期望看到的内容以及适用实体的定义。