CODEWITHSUNDEEP
linq
jlembke
jlembke

Reputation: 13517

LINQ - Return Value From Field With A Max Value if No Rows Found

I have a table like this...

ID   Description  LowThreshold   HighThreshold
1    BAD          0.0            .69
2    MEETS        .70            .89
3    GOOD         .90            2

The object here is to write a LINQ query that will select the right Description based on a given decimal. For instance .75 is between .70 and .89, so it would return “MEETS”. BUT, the kicker is, if the Number is higher than all the ranges, automatically return the Description for the record with the highest HighThreshold. So, if I pass in 5, I should get “GOOD” returned.

I have this so far, but it errors out with scores higher than 2, obviously:

private string GetEvaluationDescription (decimal score)
{
      string evaluationText = myContext.PerformanceRanges.Where
                        (e =>
                              e.LowThreshold <= score
                              &&
                              e.HighThreshold >= score
                        )
                        .Select(eval => eval.Description).First().ToString();
}

I'd like to accomplish this with just this one query, but my imagination isn't getting me there. I attempted what I found in this post but couldn't get it to work

Upvotes: 2

Views: 1546

Answers (2)

Christian C. Salvad&#243;
Christian C. Salvad&#243;

Reputation: 827496

What about this:

var range = myContext.PerformanceRanges
            .SingleOrDefault(e=>e.LowThreshold <= score && e.HighThreshold >= score)??
            PerformanceRanges.Single(
                                      e=>e.HighThreshold == PerformanceRanges
                                                            .Max(p=> p.HighThreshold)
                                    );

string evaluationText = range.Description;

The range query will select the element that matches with the Thereshold ranges, and if the value is greater (the first query will return null), it will select the greatest range.

Upvotes: 2

albertein
albertein

Reputation: 27118

What about this:

  string evaluationText = myContext.PerformanceRanges.Where
                    (e =>
                          (e.LowThreshold <= score
                          &&
                          e.HighThreshold >= score) || 
                         (e.HighThreshold == 
                            myContext.PerformanceRanges.Max (
                                  x => x.HighThreshold) 
                            && score > e.HighThreshold )
                    )
                    .Select(eval => eval.Description).First().ToString();

Upvotes: 1

Related Questions