LINQ query optimization

To expand on what Ehsan Sajjad said in his comment, sometimes to improve performance you have to do some precalculations instead of leaving it all to be calculated at the very last moment. This way you benefit from amortization of time it costs to do that calculation.

So here's a suggestion for optimizing only NetPayable at the PatientBill level, which can be done quite easily without adding a new table. Instead just add a new column to your existing PatientBill table and a few steps whenever you add/change the PatientBillDetails for a particular PatientBill record as described in more detail here:

- Add "RunningNetPayable" column, which will be the running total of the PatientBillDetails items' NetPayable.
- You must update this "RunningNetPayable" value whenever an insert or change is made to an entry in your PatientBill's PatientBillDetails records, such that it is the sum of the constituent PatientBillDetails's NetPayable. So at the code that inserts/changes the PatientBillDetails, also make it update the PatientBill's "RunningNetPayable" -or- if your database supports 'triggers' you could make use of it to do this for you too.
- Then you can use a slightly modified query:

var result= from VD in VisitDetails 
join PB in PatientBill on VD.VisitId equal PB.VisitId 
group new
{
PB
}
by new
{
VD.VisitCode
}
into data
select new
{
VisitCode= data.Key.VisitCode,
PatientPayable= data.sum(x=> x.PB.PatientPayable),
CompanyPayable= data.sum(x=> x.PB.CompanyPayable),
NetPayable= data.Sum(item=> item.PB.RunningNetPayable)
}

However, if you want to make it even faster. You could precalculate everything (PatientPayable, CompanyPaybable and NetPayable) anytime values are added/changed and stored in a separate table, like Ehsan Sajjad said.