how to concert SQL query to LINQ with count , group by and isnull clause

Question

I have the following SQL Query which I am trying to translate to LINQ Query

SELECT C.NAME,C.MOBILEPHONE,ISNULL (SUM(P.PAYMENTAMOUNT),0)  AS 
PAYAMOUNT,BILLAMOUNT ,B.ID,BILLNO , BILLDATE FROM CUSTOMERS C
JOIN BILLS B ON B.CUSTOMERID=C.ID
LEFT JOIN BILLPAYMENTS P ON P.BILLID=B.ID
GROUP BY C.NAME ,B.BILLAMOUNT,B.ID,BILLNO,BILLDATE,C.MOBILEPHONE
HAVING B.BILLAMOUNT> ( ISNULL(SUM(P.PAYMENTAMOUNT),0)) 

How do you represent this in LINQ ?

I have seen the typical implementation this

var query = from c in db.Customers
                    join b in db.Bills on c.Id equals b.CustomerId
                     join p in db.BillPayments on b.Id equals p.BillId into cs
                     from xx in cs.DefaultIfEmpty()

                    group xx by new { c.Name, c.MobilePhone, b.BillAmount, b.BillNo, b.Id, b.BillDate } into g
                    where g.Sum(p => p.PaymentAmount) < g.Key.BillAmount
                    select new
                    {
                        Received = g.Key,
                        ReceivedTotal =   g.Sum(p => p.PaymentAmount) 

                    };

but am unsure how to implement the following :

 HAVING B.BILLAMOUNT> ( ISNULL(SUM(P.PAYMENTAMOUNT),0)) 

Answer 1

So you have a sequence of Customers, where every Customer has zero or more Bills, and every Bill belongs to exactly one Customer: a straightforward one-to-many relation.

Furthermore, every Bill has zero or more BillPayments, where every BillPayment belongs to exactly one Bill, also a one-to-many relation.

Alas you forgot to tell us your classes. If you followed the entity framework code first conventions, you'll have something similar to:

class Customer
{
     public int Id {get; set;}
     public string Name {get; set;}
     ...

     // every Customer has zero or more Bills (one-to-many)
     public virtual ICollection<Bill> Bills {get; set;}
}
class Bill
{
     public int Id {get; set;}
     public int BillNo {get; set;}
     public decimal BillAmount {get; set;}
     ...

     // every Bill belongs to exactly one Customer, using foreign key
     public int CustomerId {get; set;}
     public virtual Customer Customer {get; set;}

     // every Bill has zero or more BillPayments (one-to-many)
     public virtual ICollection<BillPayment> BillPayments {get; set;}
}
class BillPayment
{
     public int Id {get; set;}
     ...

     // every BillPayment belongs to exactly one Bill, using foreign key
     public int BillId {get; set;}
     public virtual Bill Bill{get; set;}

     // every Bill has zero or more BillPayments (one-to-many)
     public virtual ICollection<BillPayment> BillPayments {get; set;}
}

In entity framework, the columns of your table are represented by non-virtual properties, the virtual properties represent the relations between the tables.

You also forgot the requirements of your query. It seems to me, that you want the following:

Give me certain properties of Bills (Id, BillNo, BillDate, BillAmount), with certain properties of the Customer of this Bill (Name and MobilePhone), of all Bills that are not fully paid yet. Or in other words, of all Bills where the sum of all payments is less than the BillAmount.

One of the nice things about entity framework, is that you don't have to do the joins yourself, you can use the virtual properties. Entity Framework knows the relations between the tables and does the proper joins for you.

Just for fun, we'll add the original BillAmount, the AmountPaid, and the RemainingAmount, so you can tell your Customer how much he still has to pay when you phone him on his mobile phone

In the requirement you see the central role of Bills, so let's use that as starting point:

// (1) from all bills, calculate the AmountPaid; remember the original bill data:
var notFullyPaidBills = myDbContext.Bills
    .Select(bill => new
    {
        BillData = bill,
        AmountPaid = bill.BillPayments
          .Select(billPayment => billPayment.PaymentAmount)
          .Sum(),
    })
    // (2) Keep only those bills that are not fully paid yet
    .Where(bill => bill.Bil.BillAmount > bill.AmountPaid)

    // (3) from the remaining bills select the required properties:
    .Select(bill => new
    {
        // Customer properties:
        CustomerName = bill.BillData.Customer.Name,
        MobilePhone = bill.BillData.Customer.MobilePhone,

        // bill properties:
        BillId = bill.BillData.Id,
        BillNo = bill.BillData.BillNo,
        BillDate = bill.BillData.Date,

        // Amounts:
        BillAmount = bill.BillData.BillAmount,
        AmountPaid = bill.AmountPaid,
        RemainingAmount = bill.BillData.BillAmount - bill.AmountPaid,
    });

See? When using the virtual properties of your entity framework classes, the queries will look much simpler and more intuitive than when you are doing the (group)joins yourself.