Linq query help needed

Question

0.00/5 (No votes)

See more:

I have a in memory datatable with the following data in, using LINQ how can I select the RPTGroup column with the greatest Weighting for a given value ? E.g my query should return RPTGroup 3 because Weighting is 90

BandingStart BandingEnd RPTGroup Weighting
1000	     2000	          1    81
1000	     2000	          2    80
1000	     2000	          3    90

Posted 1-Feb-16 6:58am

pkfox

Add a Solution

Comments

pkfox 1-Feb-16 13:55pm

Hi Sascha thanks for that but I need a where clause on BandingEnd as in the real application there would be multiple bands e.g 1000-2000,3000-4000 etc... But I think I can figure it out from your example. Thanks again

Sascha Lefèvre 1-Feb-16 14:32pm

You should post your comment to my solution if you want me to see it :-) Only got here again because of that other comment.
And you didn't say anything in your question about the bands :-P
I updated my answer, please take a look.

pkfox 1-Feb-16 14:42pm

Hi Sascha I'm using a tablet to post this and don't see an option to comment on a solution

Sascha Lefèvre 1-Feb-16 14:46pm

Oh alright. You might want to submit a bug report then.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2016-02-01T09:53:00

Another alternative, which only needs a single pass through the rows:

C#

int rptGroup = dt.AsEnumerable()
    .Where(row => row.Field<int>("BandingStart") == 1000 && row.Field<int>("BandingEnd") == 2000)
    .OrderByDescending(row => row.Field<int>("Weighting"))
    .Select(row => row.Field<int>("RPTGroup"))
    .FirstOrDefault();

Unfortunately, the OrderBy operator has to store every item from the source sequence in a list, since it doesn't know the final order until it has completely enumerated the source.

If you're open to using custom extension methods:

C#

public static TSource MaxBy<TSource, TKey>(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector, IComparer<TKey> keyComparer = null)
{
    if (source == null) throw new ArgumentNullException(nameof(source));
    if (keySelector == null) throw new ArgumentNullException(nameof(keySelector));
    if (keyComparer == null) keyComparer = Comparer<TKey>.Default;

    bool isEmpty = true;
    TSource current = default(TSource);
    bool canBeNull = current != null;
    if (canBeNull) source = source.Where(x => x != null);

    foreach (var value in source)
    {
        if (isEmpty)
        {
            current = value;
            isEmpty = false;
        }
        else if (keyComparer.Compare(keySelector(value), keySelector(current)) > 0)
        {
            current = value;
        }
    }

    if (!isEmpty || canBeNull) return current;

    // Max of an empty sequence of non-nullable objects is not supported.
    throw new InvalidOperationException(string.Format("Cannot calculate an aggregate for an empty sequence of the non-nullable value type '{0}'.", typeof(TSource)));
}

Your code would then become:

C#

DataRow maxRow = dt.AsEnumerable()
    .Where(row => row.Field<int>("BandingStart") == 1000 && row.Field<int>("BandingEnd") == 2000)
    .MaxBy(row => row.Field<int>("Weighting"));

int rptGroup = maxRow?.Field<int>("RPTGroup") ?? 0;

// For C# 5 or earlier:
// int rptGroup = maxRow != null ? maxRow.Field<int>("RPTGroup") : 0;

Sascha Lefèvre · Accepted Answer · 2016-02-01T07:32:00

Solution 1

This should work ("dt" is the DataTable object):

C#

Predicate<DataRow> band1000to2000 = row => row.Field<int>("BandingStart") == 1000 && row.Field<int>("BandingEnd") == 2000;
int maxWeighting = dt.AsEnumerable().Where(row => band1000to2000(row)).Select(row => row.Field<int>("Weighting")).Max();
int rptGroup = dt.AsEnumerable().Where(row => row.Field<int>("Weighting") == maxWeighting && band1000to2000(row))
                                .Select(row => row.Field<int>("RPTGroup"))
                                .SingleOrDefault();

If it could happen that there are multiple RPTGroup with the same maximum Weighting then you would have to remove the SingleOrDefault()-call.
And, of course, if those columns aren't of Int32 type, then replace the generic types there as appropriate.

Edit: Updated as per comment below question.

Posted 1-Feb-16 7:32am

Sascha Lefèvre

Updated 1-Feb-16 8:31am

v3

Comments

pkfox 2-Feb-16 3:01am

Looks good Sascha

pkfox 2-Feb-16 5:08am

Hi Sascha your solution works except for ( as you mentioned ) when there are more than one with the same maximum Weighting, if I remove the SingleOrDefault() I get an error stating that more than one element has been returned and a problem converting IEnumerable of ints to int, I then replaced SingleOrDefault() with FirstOrDefault() and all is well - so thanks very much

Sascha Lefèvre 2-Feb-16 13:16pm

You're welcome! :)
Yes, when you remove SingleOrDefault() the return type is an IEnumerable<int>, forgot to mention that :)

pkfox 3-Feb-16 2:41am

How do you get the angle brackets to show correctly in your message ?

Sascha Lefèvre 3-Feb-16 4:56am

You have to type the HTML code (or whatever it's called):
< = <
> = >

pkfox 3-Feb-16 9:40am

Ok thanks