LINQ: How to get a min value from a DataTable

I have a DataTable which returned two columns, both decimals.

Column 1: Upper Bound Amount
Column 2: Fee Amount

I want to use the LINQ MIN function to get the minimum Fee Amount where Upper Bound Amount < $25.00 (for example).

So far, I have the following:
DataTable dt = Mapper.GetAllFees();
decimal feeAmount = dt.AsEnumerable()
	.Where(ub => ub.Field<decimal>("UpperBoundAmount") > 25.00)
	.Select(fa => fa.Field<decimal>("FeeAmount"))
	.FirstOrDefault();

Open in new window

How do I incorporate the MIN function into that LINQ statement to say something like:

.Min(ub => ub.Field<decimal>("UpperBoundAmount"))
LVL 8
pzozulkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi pzozulka;

If I understand your question, not clear from the post, the following should do what you want.
DataTable dt = Mapper.GetAllFees();
decimal feeAmount = dt.AsEnumerable()
                      .Where(ub => ub.Field<decimal>("UpperBoundAmount") < 25.00)
	              .Select(fa => fa.Field<decimal>("FeeAmount"))
	              .Min(fa => fa);

Open in new window

0
pzozulkaAuthor Commented:
Sorry I wasn't clear.

Here's what I need, based on the following sample:

UpperBoundAmount        FeeAmount
100.00                                  19.00
90.00                                     17.00
50.00                                     16.00
22.00                                      9.00
20.00                                      8.00
11.00                                      7.00
11.00                                      7.00

I need to select all rows where "UpperBoundAmount" < 25.00. The results are below:

UpperBoundAmount        FeeAmount
22.00                                      9.00
20.00                                      8.00
11.00                                      7.00
11.00                                      7.00

Based on the remaining list, I need to select the row, with the MIN(UpperBoundAmount). Results are:
UpperBoundAmount        FeeAmount
11.00                                      7.00
11.00                                      6.00

Next, from those results, I need to select the FirstOrDefault() FeeAmount.
0
Fernando SotoRetiredCommented:
Hi pzozulka;

Here you go, should do what you want.

// Initial Query to get all records which have a UpperBoundAmount less then 25.00
var queryResult = dt.AsEnumerable().
                  Where(ub => ub.Field<decimal>("UpperBoundAmount") < 25.00m).
                  Select(data => new {
                      UpperBoundAmount = data.Field<decimal>("UpperBoundAmount"),
                      FeeAmount = data.Field<decimal>("FeeAmount")
                  }).ToList();

// Find out what is the lowest value in UpperBoundAmount
var minValue = queryResult.Min(m => m.UpperBoundAmount);
// Get a list of the records who has the same lowest value in case there are more one.
var minList = queryResult.Where(m => m.UpperBoundAmount == minValue).ToList();

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
käµfm³d 👽Commented:
I would suggest installing the MinBy function that Jon Skeet (et al.) created. It allows you to group objects by a particular property rather than the objects themselves. Doing that, you could do something like:

using MoreLinq;

...

var minRows = dt.AsEnumerable()
                .Where(row => row.Field<decimal>("UpperBoundAmount") < 25M)
                .GroupBy(row => row.Field<decimal>("UpperBoundAmount"))
                .MinBy(group => group.Key)
                .Select(group => group);

Open in new window


MinBy is available in NuGet.
0
Fernando SotoRetiredCommented:
Hi  pzozulka;

Are you still having issues with this question, or has it been resolved?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.