How to use linq to filter to the max of a group

Question

I have this list

var commonContractsList = (
        from i in referredDilutions
        join f in dilutionsList
            on i.Contract equals f.Contract
        select f)
    .ToList();

The list commonContractsList has several fields and three in particular, contract, instalment and amount. We can have several instalments for one contract. Every contract/instalment has an amount.

Now, what I want to do is get, for each contract, a list of all records of contracts where the instalment has the higher amount.

Source Data

contract    instalment    amount
1           1             100
1           2             1000
2           1             100
3           1             1000
4           1             200
4           2             100
5           1             1000

So i need,

Results

contract    instalment    amount
1           2             1000
2           1             100
3           1             1000
4           1             200
5           1             1000 

My knowledge of linq is limited and I'm having a hard time with project.

Any ideas?

Answer 1

The following code should give you the expected results:

var data = new[]
{
    new { contract = 1, installment =  1, amount = 100},
    new { contract = 1, installment =  2, amount = 1000},
    new { contract = 2, installment =  1, amount = 100},
    new { contract = 3, installment =  1, amount = 1000},
    new { contract = 4, installment =  1, amount = 200},
    new { contract = 4, installment =  2, amount = 100},
    new { contract = 5, installment =  1, amount = 1000},
};

var result = from d in data
             group d by d.contract into g
             let highestInstallment = (from x in g
                                       orderby x.amount descending
                                       select x).First()
             select new
             {
                 contract = g.Key,
                 installment = highestInstallment.installment,
                 amount = highestInstallment.amount
             };   

You group by contract and then within each group you check for the highest amount. I've added the let clause so you don't have to repeat the sub query for both installment and amount.