Search code examples
c#sqldictionaryt-sqlmax

Translating SQL to C#: Select name in key-value pair with the highest value after applying a function to its value and filtering based on a condition?

I have the following almost-English SQL.

SELECT [NAME]
FROM [PAIRS]
WHERE condition([NAME])
HAVING function([SCORE]) = MAX(function([SCORE]))

Assume that:

  • PAIRS is a two-column table translating a string NAME to a numeric SCORE (i.e. in C# terms, it's a dictionary).
  • condition takes a NAME and returns a boolean.
  • function translates a numeric SCORE to some other value.

How can I re-write this SQL in C#? My best attempt so far relied on abuse of Aggregate. This felt terribly over-engineered and far inferior to my almost-English SQL.

Solution

  • Is this not...

    int foo(int i) => i;

var pairs = new List<(string name, int score)>
{
   ("a", 1),
   ("b", 2),
   ("c", 2)
};

var result = pairs
   .Where
   (
      p => p.name == "c"
      && foo(p.score) == pairs.Max(p => foo(p.score))
   );