Find Min and Max value in table between given variable in C#

Question

I have the following database table:

COL-A COL-B
1     $200
2     $100
4     $50
5     $40

What i need to do is if i have the value 1.5, i need to find the two numbers that fall between it, so that would be 1 and 2.

I need the values to input into a linear equation for a pricing calculator that i am working on.

The values may not always be the same, so it could even be like so:

COL-A COL-B
    1     $200
    6     $100
    11     $50
    22     $40

If i had 9, i would then to return both 6 and 11 as 9 falls between those two values, etc etc

Visualising this i would need them put into seperate variables:

double Min = the_lowest_value;
double Max = the_highest_value;

I am using EF to do this, so it would need to be in LINQ.

Using EF doens't require you to use Linq for everything. It might be easier (depending on data volumes) to retrieve the whole table content and then find the correct value yourself. Also: is the data in your table sorted? — Dan Puzey
– Dan Puzey, Commented Aug 9, 2012 at 11:18
What if you pick a value that is in the table? For example, what values should 6 return in the second table? — Yuck
– Yuck, Commented Aug 9, 2012 at 11:19
It wont be a big table, so data volume wouldn't be a problem. Initially the data would be ordered, but on the off chance that it isn't i can sort it before hand. — xqwzid
– xqwzid, Commented Aug 9, 2012 at 11:20
It will do a lookup first to see if the value is in the table, if not, it needs to know what is. If the value is lower than the MIN value, for eg 1 or the value is higher than the MAX value 22, it won't need to be processed through the algorithm. — xqwzid
– xqwzid, Commented Aug 9, 2012 at 11:23

ken2k · Accepted Answer · 2012-08-09 11:51:22Z

6

A simple solution that requires 2 DB requests:

double the_lowest_value = Ctx.YourEntities
        .Where(z => z.ColA <= 9)
        .OrderByDesc(z => z.ColA)
        .Select(z => z.ColA)
        .First();

double the_highest_value = Ctx.YourEntities
        .Where(z => z.ColA >= 9)
        .OrderBy(z => z.ColA)
        .Select(z => z.ColA)
        .First();

Do note that if your value (9 in the above example) is in your table, both variables will have the same value (9).

EDIT: As Slauma noticed, this would crash (i.e. throw an InvalidOperationException exception) if the specified value is lower/greater than any value of ColA, as First() requires at least one element in the returned sequence. You could use FirstOrDefault() to return the default double value if the sequence is empty:

For positive values:

As default(double) is 0.0, the value would be OK for the_lowest_value (if the specified input value is 0.5 with your second example). For the_highest_value, you could test after the DB query that's it's actually greater or equal than the specified input value. If it's not the case, you'll know the value you specified was greater than every value of the ColA column.

edited Aug 9, 2012 at 11:51

answered Aug 9, 2012 at 11:23

ken2k

49.2k12 gold badges119 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ken2k Over a year ago

@Slauma true, the use of First() requires the query to return at least one value, so the OP must be sure lowest/highest values exist in DB for the specified input value.

Slauma Over a year ago

Yes, or use nullable results, like: double? the_lowest_value = ...Select(z => (double?)z.ColA).FirstOrDefault(). Result will be null then if input value is lower than the lowest value in DB (the same for the highest value).

ken2k Over a year ago

@Slauma I just edited the question and added information about FirstOrDefault() :) Your (double?)z.ColA cast is actually a very good idea. This would solve the issue with negative values, as default(double?) is null, so with that we're sure it's not a DB value (that default(double) (0.0) could be).

Maarten · Accepted Answer · 2012-08-09 11:43:06Z

This will solve it in one query, but again, the OP must ensure the given value is between two existing values.

using (var context = new YourContext()) {
    var val = 15;
    var query2 = context.Table
        .GroupBy(
            x => new {
                IsGreaterThan = x.Column > val,
                IsLessThan = x.Column < val
            },
            (key, data) => new { key = key, data = data.Select(x => x.Column) }
        )
        .Select(x => x.key.IsGreaterThan ? x.data.Min() : x.data.Max())
        .ToList();
    Console.WriteLine("First value larger than {0} = {1}", val, query2[0]);
    Console.WriteLine("First value smaller than {0} = {1}", val, query2[1]);
    Console.ReadLine();
    return;
}

Collectives™ on Stack Overflow

Find Min and Max value in table between given variable in C#

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related