select multiple objects from list based on values from another list

Question

I have two lists. One list is of type Cascade (named, cascadeList) & the other list if of type PriceDetails (named priceList), both classes are shown below. I have also given a simple example of what I'm trying to achieve below the classes.

So the priceList contains a list of PriceDetail objects where they can be multiple (up to three) PriceDetail objects with the same ISIN. When there are multiple PriceDetails with the same ISIN I want to select just one based on the Source field.

This is where the cascadeList comes in. So if there were 3 PriceDetails with the same ISIN I would want to select the one where the source has the highest rank in the cascade list (1 is the highest). Hopefully the example below helps.

Reason for the question

I do have some code that is doing this for me however its not very efficient due to my lack of skill.

In a nutshell it first creates a unique list of ISIN's from the priceList. It then loops through this list for each unique ISIN to get a list of the PriceDetails with the same ISIN then uses some if statements to determine which object I want. So hoping and pretty sure there is a better way to do this.

My Classes

class Cascade
{
     int Rank;
     string Source;
}


class PriceDetails
{
     string ISIN;
     string Sedol;
     double Price;
     string Source;
}

Example

PriceList                                Cascade
ISIN   Source    Price                   Source    Rank
BN1    XYZ       100                     ABC       1
MGH    PLJ       102                     XYZ       2
BN1    PLJ       99.5                    PLJ       3
BN1    ABC       98
MGH    XYZ       102

Result I'm looking for

PriceList
ISIN   Source   Price
BN1    ABC      98
MGH    XYZ      102

Answer 1

For getting the desired result we must do these steps:

1. Join two lists based on Source property.
2. Group the last result by ISIN property.
3. After grouping we must get the minimum rank for each ISIN.
4. Then we will use the minRank variable to compare it against the rank of an elements with the same ISIN and then select the first element.

We can write this query either with query or method syntax.

With query syntax:

var result = from pr in pricesList
             join cas in cascadesList on pr.Source equals cas.Source
             select new { pr, cas } into s
             group s by new { s.pr.ISIN } into prcd
                let minRank = prcd.Min(x => x.cas.Rank)
             select prcd.First(y => y.cas.Rank == minRank).pr;

With method syntax:

var result = pricesList.Join(cascadesList,
                  pr => pr.Source,
                  cas => cas.Source,
                  (pr, cas) => new { pr, cas })
            .GroupBy(j => j.pr.ISIN)
            .Select(g => new { g, MinRank = g.Min(x => x.cas.Rank) })
            .Select(r => r.g.First(x => x.cas.Rank == r.MinRank).pr);

Result will be same with both ways:

PriceList
ISIN   Source   Price
BN1    ABC      98
MGH    XYZ      102

P.S: I have assumed that list's name is as following: pricesList and cascadesList