The expression below does the following:
First, it groups the records by name and amount (fine or paid).
Then, for each group:
It selects all the FineAmt rows but skips as many as there are PaidAmt rows also in the same group, so that if there are unpaired FineAmt rows, these will be the only elements in the list. If there are more PaidAmt rows than FineAmt rows, this results in an empty list.
It then does the same thing in reverse for PaidAmt, and concatenates this with the previous list (only one of these lists will have any rows.)
The resulting list, for each group, consists of any unpaired rows in the input.
NOTE: The query assumes that at least one of r.FineAmt or r.PaidAmt in each row has a value other than null.
var filteredRows = lstEmpDetails
.GroupBy(r=>new {r.EmpName, Amount=r.FineAmt ?? r.PaidAmt})
.SelectMany(g=>
g.Where(r=>r.FineAmt.HasValue)
.Skip(g.Count(r=>!r.FineAmt.HasValue))
.Concat(g.Where(r=>!r.FineAmt.HasValue)
.Skip(g.Count(r=>r.FineAmt.HasValue))));
Update:
To instead get the remaining sums of all the EmpName rows:
This statement groups the rows by name only, then computes the balance for each group.
Finally, it selects only those groups with non-zero balance, then generates the FineAmt, PaidAmt columns accordingly.
NOTE: This assumes you are using int as the data type; if not, change the last Select statement accordingly.
var filteredRows = lstEmpDetails
.GroupBy(r=>r.EmpName)
.Select(g=>new{
EmpName=g.Key,
Balance=g.Sum(r=>(r.FineAmt ?? 0) - (r.PaidAmt ?? 0))
})
.Where(r=>r.Balance != 0)
.Select(r=>new{
r.EmpName,
FineAmt = r.Balance > 0 ? (int?)r.Balance : null,
PaidAmt = r.Balance < 0 ? (int?)-r.Balance : null
});