Self join in Asp.net Mvc using LINQ query to get a result using two rows in the same table

I want to fetch records from a table.

I have 2 records for each invoice in a table and I want to join them so that I can show 1 record.

The situation is something like this: I have 2 invoice records in a table and they have the same GatePassNo, but one has the totalAmount and the other has null in the totalAmount.

Now what I want to do is when I fetch the record without the totalAmount invoices, I want to select the totalAmount value from the invoice which has that value.

They both have the same GatePassNo but they have the different invoiceType to distinguish them.

I have achieved it, but the way I achieved it isn't optimal and I want an optimal solution.

var query = _invoiceService.Table.Include("Items").Include("Customer").Include("Suppliers").Include("CreatedBy")
                    .Where(i => i.CompanyId == companyId);

switch (invoiceType)
{
    case InvoiceType.Sales:
        if (customerId != null)
            query = query.Where(p => p.CustomerId == customerId);
        if (deliveryOrderNo != null && deliveryOrderNo > 0)
        {
            var deliveryChallan = query.FirstOrDefault(p => p.InvoiceType == InvoiceType.DeliveryChallan && p.GatePassNo.ToString().EndsWith(deliveryOrderNo.ToString()));
            query = deliveryChallan != null ? query.Where(p => p.InvoiceType == InvoiceType.Sales && p.GatePassNo == deliveryChallan.GatePassNo) : query.Where(p => p.InvoiceType == InvoiceType.Sales);
        }
        else
            query = query.Where(p => p.InvoiceType == InvoiceType.Sales);
        break;
    
    case InvoiceType.DeliveryChallan:
        query = query.Where(p => p.InvoiceStatus != InvoiceStatus.Void);
        if (invoiceNo != null && invoiceNo > 0)
        {
            var invoice = query.FirstOrDefault(p => p.InvoiceType == InvoiceType.Sales && p.InvoiceNo.ToString().EndsWith(invoiceNo.ToString()));

            query = invoice != null ? query.Where(p => p.InvoiceType == InvoiceType.DeliveryChallan && p.GatePassNo == invoice.GatePassNo) : query.Where(p => p.InvoiceType == InvoiceType.DeliveryChallan);


        }
        else
        {
            query = query.Where(p => p.InvoiceType == InvoiceType.DeliveryChallan);

            //Here i want to fetch the totalAmount from the invoice with the type sales and map them to the invoices in the above query.

        }


        if (customerId != null)
            query = query.Where(p => p.CustomerId == customerId);
        if (_workContextService.IsMaster() && customerId.HasValue)
            showAll = true;
        break;
}

I still have to apply some sorting on the query so i cannot convert it into toList here. I need to do this wihtout converting it into list.

What i tried was i fetch all the invoices and then made a function to fetch the invoice with the sales type again and then map the totalAmount to the invoices list.

public void RetrieveTotalInvoiceAmount(List<Invoice> invoices)
{
    foreach (var item in invoices)
    {
        var result = _invoiceService.Table.Where(i => i.GatePassNo == item.GatePassNo && i.InvoiceType == InvoiceType.Sales).FirstOrDefault();
        if (result != null)
        {
            item.TotalAmount = result.TotalAmount;
            
        }
    }
}