How to retrive distinct rows based on a column when join in entity framework?

See more:

C#

SQL

LINQ

entity-framework

I want to join 2 tables and retrieve distinct rows based on 1 columns in entity framework.

I have read posts on web including SO.

In the code below,
1. I join 2 tables based on CurrentTurnID key
2. I filter the table by the features that I want
3. I filter the table by setting conditions on selected features
4. I select a column to calculate the sum of all it's value

I need to retrieve records that have the same CurrentTurnID just once. Here's my code:

int services = Convert.ToInt32(database.Tbl_CurrentTurn.AsNoTracking()
            .Join(database.Tbl_pay.AsNoTracking(), f => f.CurrentTurnID,
               s => s.CurrentTurnID, (f, s) => new
               {
                   f.CurrentTurnOfficialID,
                   f.CurrentTurnID,
                   f.SickID,
                   f.Remove,
                   f.Payment,
                   f.ServicePayment,
                   s.isDeposit
               })
            .Where(w => w.SickID == SickId && w.Remove != true && w.Payment == true && w.isDeposit != true)
            .Select(s => s.ServicePayment).DefaultIfEmpty(0).Sum());

If I add Distinct() after Select() statement, it would consider the ServicePayment to filter distinct rows.

The Image is attached to my Dropbox. The 2nd and the 3rd records have the same CurrentTurnID value. So I want to consider just one of them. Then sum up records on ServicePayment column. Please notice that other columns are different in these 2 records.

What I have tried:

I have read posts on the web. Non of the solutions helped me.