I have a T-SQL Query that show Sum(TotalAmount) Sum(Discount) Sum(PayableAmount) from Table1 named p1 and show the Sum(PaidAmount) from Table2 named p2,

the tables p1 and p2 do not have any relationship with Forenkay and the primary key,

so I was not able to use the .net core method syntax _context.Table1.include(a=>a.table2) to join 2 tables because there is no relationship between these 2 tables,

So I have written the below code as T-SQL query and got the exact result that I wanted, but now I want to write the same T-SQL query as a LING query and use it inside my C#.net Core Controller code as query syntax or method syntax,

Here is my T-SQL Code that show Sum(TotalAmount) Sum(Discount) Sum(PayableAmount) from Table1 named p1 and show the Sum(PaidAmount) from Table2 named p2,

SELECT p1.PatientId, 
SUM(p1.TotalAmount) as TotalTreatmentAmount, 
SUM(p1.Discount) As TotalTreatmentDiscount,
SUM(p1.Payable) as TotalTreatmentPayable,
SUM(p1.Balance) as TotalTreatmentBalancce,
p2.TotalTreatmentPaidAmount
FROM PatientPayments as p1
join (SELECT p2.PatientId, SUM(p2.PaidAmount) as TotalTreatmentPaidAmount
FROM PatientPaymentHistories p2
where p2.PaymentType != 'Refund' 
group by p2.PatientId) as p2 
ON p2.PatientId = p1.PatientId
group by p1.PatientId, p2.TotalTreatmentPaidAmount

Here is my C# Code but this code does not work its wrong only the SQL code is working fine

    public IActionResult ExportPatientPaymentDataToExcel()
    {
        var PatientPayments =
        from p1 in _context.PatientPayments
        join p2 in _context.PatientPaymentHistories on p1.PatientId equals p2.PatientId
        select new
        {
            p1.PatientId,
            p1.TotalAmount,
            p1.Discount,
            p1.Payable,
            p2.PaidAmount
        };

        DataTable PatientPaymentTable = new DataTable("PatientPayment");
        PatientPaymentTable.Columns.AddRange(new DataColumn[5]
        {
              new DataColumn("Patient ID"),
              new DataColumn("Total Amount"),
              new DataColumn("Total Discount"),
              new DataColumn("Total Payable"),
              new DataColumn("Total Paid"),
        });

        foreach (var PatientPayment in PatientPayments)
        {
            PatientPaymentTable.Rows.Add(
            PatientPayment.PatientId,
            PatientPayment.TotalAmount,
            PatientPayment.Discount,
            PatientPayment.Payable,
            PatientPayment.PaidAmount

            );

        }
        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(PatientPaymentTable);
            using (MemoryStream stream = new MemoryStream())
            {
                wb.SaveAs(stream);
                return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", GetLocalDateTime().Date.ToShortDateString()   "  "   "PatientPayment_Data.xlsx");
            }
        }


    }

Please help me to change the above T-SQL Code to C# Ling query

CodePudding user response：

Based on your T-SQL, try it like this

var query = _context.PatientPayments
            .Join(_context.PatientPaymentHistories
                          .Where(s => s.PaymentType != 'Refund')
                          .GroupBy(p => p.PatientId)
                          .Select(g => select new {
                              PatientId = g.Key,
                              TotalTreatmentPaidAmount = g.Sum(x => x.PaidAmount)
                         }), 
                         p1 => p1.PatientId, 
                         p2 => p2.PatientId, 
                         (p1, p2) => new { PatientPayments = p1, PatientPaymentHistories = p2 })
            .GroupBy(s => new { s.PatientPayments.PatientId, s.PatientsPaymentHistories.TotalTreatmentPaidAmount })
            .Select(gs => new 
            {
                gs.Key.PatientId, 
                TotalTreatmentAmount = gs.Sum(x => x.PatientPayments.TotalAmount),
                TotalTreatmentDiscount = gs.Sum(x => x.PatientPayments.Discount),
                TotalTreatmentPayable = gs.Sum(x => x.PatientPayments.Payable),
                TotalTreatmentBalancce = gs.Sum(x => x.PatientPayments.Balance),
                gs.Key.TotalTreatmentPaidAmount
            };

CodePudding user response：

This should work as expected:

var query = _context.PatientPayments.Join(_context.PatientPaymentHistories
            .Where(x => x.Type != "Refund")
            .GroupBy(x => x.PatientId)
            .Select(x => new
            {
                PatientId = x.Key,
                TotalTreatmentPaidAmount = x.Sum(y => y.PaidAmount)
            }),
        x => x.PatientId,
        y => y.PatientId,
        (x, y) => new
        {
            PatientId = x.PatientId,
            TotalAmount = x.TotalAmount,
            Discount = x.Discount,
            Payable = x.Payable,
            Balance = x.Balance,
            TotalTreatmentPaidAmount = y.TotalTreatmentPaidAmount
        })
    .GroupBy(x => new { PatientId = x.PatientId, 
        TotalTreatmentPaidAmount = x.TotalTreatmentPaidAmount })
    .Select(x => new
    {
        PatientId = x.Key.PatientId,
        TotalTreatmentAmount = x.Sum(y => y.TotalAmount),
        TotalTreatmentDiscount = x.Sum(y => y.Discount),
        TotalTreatmentPayable = x.Sum(y => y.Payable),
        TotalTreatmentBalancce = x.Sum(y => y.Balance),
        TotalTreatmentPaidAmount = x.Key.TotalTreatmentPaidAmount
    });