LINQ query with left outer join only want most recent record

Question

I have seen several answers to this query, but none have worked for me.

We have multiple mySql tables: shipments customers suppliers rebillingaudits

in my query I want to pull up the shipments with their customer and supplier and the most recent rebillingaudit record. This is what I have:

        var results = context.Shipments.AsNoTracking().Where(x => customerIds.Contains(x.CustomerId.Value) && x.ProcessStageFlag == "REBILL_AUDIT")
            .Join(context.Customers.AsNoTracking(),
                im => im.CustomerId,
                cu => cu.CustomerId,
                (im, cu) => new { Shipments = im, Customers = cu }).DefaultIfEmpty()
            .Join(context.Suppliers.AsNoTracking(),
                im => im.Shipments.SupplierId,
                su => su.SupplierId,
                (im, su) => new { Shipments = im.Shipments, Customers = im.Customers, Suppliers = su }).DefaultIfEmpty()
            .GroupJoin(context.RebillingAudit.AsNoTracking().OrderByDescending(x => x.LastUpdatedOn), //.Take(1),
                im => new {im.Shipments.TrackingNumber, im.Shipments.CarrierInvoiceNumber},
                rn => new {rn.TrackingNumber, CarrierInvoiceNumber = rn.InvoiceNumber},
                (im, rn) => new { MatchingAssignments = im.MatchingAssignments, Shipments = im.Shipments, Suppliers = im.Suppliers, MatchResolutions = im.MatchResolutions, Customers = im.Customers, RebillingAudit = rn })
            .SelectMany(
                x => x.RebillingAudit.DefaultIfEmpty(),
                (x, y) => new { MatchingAssignments = x.MatchingAssignments, Shipments = x.Shipments, Suppliers = x.Suppliers, MatchingResolutions = x.MatchResolutions, Customers = x.Customers, RebillingAudit = y })
            .Where(x => x.MatchingAssignments.IsRebill &&
                        x.MatchingAssignments.IsActive)
            .Select(m => new CustomerRebills()
            {
                TrackingNumber = m.Shipments.TrackingNumber,
                Customer = m.Customers.InternalCustomerName,
                CarrierInvoice = m.Shipments.CarrierInvoiceNumber,
                RebillNotes = m.RebillingAudit == null ? "" : m.RebillingAudit.Notes
            }).ToList();

This is the SQL query that I am attempting to replicate:

 FROM invoice_master im 
  JOIN customer c ON im.VPL_customer_ID = c.Customer_ID
  JOIN supplier s ON im.Supplier_ID = s.Supplier_ID
  LEFT OUTER JOIN rebilling_audit rn ON im.Tracking_Number = rn.tracking_number AND im.Invoice_Number = rn.Invoice_number AND rn.last_updated_on = 
    (SELECT MAX(last_updated_on) FROM rebilling_audit WHERE tracking_number = im.tracking_number AND Invoice_number = im.invoice_number)

I have tried adding .Take(1) and I do not get what I expect. I have tried .FirstOrDefault() and I get an error.

Thanks, Sammer

Your LINQ query and SQL query seem to be from different models and don't have the same criteria; your SQL query has no `SELECT`; please add enough information. — NetMage, Nov 22 '19 at 21:51

score 0 · Answer 1 · answered Nov 22 '19 at 21:58

Using my SQL to LINQ Recipe, translating the SQL more or less directly, I get

var results = (from im in context.InvoiceMaster
               join c in context.Customers on im.CustomerId equals c.CustomerId
               join s in context.Suppliers on im.Shipments.SupplierId equals s.SupplierId
               join rn in context.RebillingAudit on new { im.Shipments.TrackingNumber, im.Shipments.CarrierInvoiceNumber } equals new { rn.TrackingNumber, CarrierInvoiceNumber = rn.InvoiceNumber } into rnj
               from rn in rnj.Where(rn => rn.LastUpdatedOn == context.RebillingAudit.Where(ra => ra.TrackingNumber == im.Shipments.TrackingNumber && rn.InvoiceNumber == im.Shipments.CarrierInvoiceNumber).Max(rn => rn.LastUpdatedOn)).DefaultIfEmpty()
               select new CustomerRebills() {
                   TrackingNumber = im.Shipments.TrackingNumber,
                   Customer = c.InternalCustomerName,
                   CarrierInvoice = im.Shipments.CarrierInvoiceNumber,
                   RebillNotes = rn == null ? "" : rn.Notes
               }).ToList();

score 0 · Answer 2 · answered Nov 25 '19 at 17:04

This is what I did to get the latest record:

    var results = context.Shipments.AsNoTracking().Where(x => customerIds.Contains(x.CustomerId.Value) && x.ProcessStageFlag == "REBILL_AUDIT")
        .Join(context.Customers.AsNoTracking(),
            im => im.CustomerId,
            cu => cu.CustomerId,
            (im, cu) => new { Shipments = im, Customers = cu }).DefaultIfEmpty()
        .Join(context.Suppliers.AsNoTracking(),
            im => im.Shipments.SupplierId,
            su => su.SupplierId,
            (im, su) => new { Shipments = im.Shipments, Customers = im.Customers, Suppliers = su }).DefaultIfEmpty()
        .GroupJoin(context.RebillingAudit.AsNoTracking().OrderByDescending(x => x.LastUpdatedOn), //.Take(1),
            im => new {im.Shipments.TrackingNumber, im.Shipments.CarrierInvoiceNumber},
            rn => new {rn.TrackingNumber, CarrierInvoiceNumber = rn.InvoiceNumber},
            (im, rn) => new { MatchingAssignments = im.MatchingAssignments, Shipments = im.Shipments, Suppliers = im.Suppliers, MatchResolutions = im.MatchResolutions, Customers = im.Customers, RebillingAudit = rn })
        .SelectMany(
            x => x.RebillingAudit.DefaultIfEmpty(),
            (x, y) => new { MatchingAssignments = x.MatchingAssignments, Shipments = x.Shipments, Suppliers = x.Suppliers, MatchingResolutions = x.MatchResolutions, Customers = x.Customers, RebillingAudit = y })
        .Where(x => x.MatchingAssignments.IsRebill &&
                    x.MatchingAssignments.IsActive)
        .Select(m => new CustomerRebills()
        {
            TrackingNumber = m.Shipments.TrackingNumber,
            Customer = m.Customers.InternalCustomerName,
            CarrierInvoice = m.Shipments.CarrierInvoiceNumber,
            RebillNotes = m.RebillingAudit == null ? "" : m.RebillingAudit.Notes
        }).ToList().OrderByDescending(x => x.RebillNotesWhen).GroupBy(y => new {y.TrackingNumber, y.CarrierInvoice, y.BillToAccount, y.CustomerId}).Select(z => z.FirstOrDefault()).ToList();

All my changes are in the last line.

I added an OrderByDescending to get the most recent record to the top, I added a GroupBy to collect each individual role, then I only take the first with the FirstOrDefault.

This resulted in me getting one record per TrackingNumber, CarrierInvoice, BillToAccount, and CustomerId and making sure that the one record was the latest.

I hope this helps someone. Sammer

LINQ query with left outer join only want most recent record

2 Answers2