Core EF Outer Join,Count & Group

Question

I'm trying to convert this SQL Query into Core 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

I have tried multiple solutions, including several here. My latest attempt looks like:

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
    });

I have also tried adding t3.count(m => m.something != null), but that throws an error.

Answer 1

One major point of EF is to map the relationship between entities so that you can leverage LINQ and let EF compose an SQL query rather than trying to replace SQL with LINQ-QL.

If your ShippingBatch is mapped with a collection of ShippingOrders...

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();

If your ShippingBatch does not have a collection of ShippingOrders, but your ShippingOrder reference an optional 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();

That should hopefully get you moving in the right direction. If not, expand your question to include details of what you are seeing, and what you expect to see along with definitions for the applicable entities.