需要Linq查询帮助 [英] Linq query help needed

问题描述

我有一个带有以下数据的内存数据表，使用LINQ如何选择给定值具有最大权重的RPTGroup列？例如，我的查询应该返回RPTGroup 3，因为加权是90

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

推荐答案

另一种选择，只需要一次通过行：

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

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();

不幸的是， OrderBy 运算符必须将源序列中的每个项目都存储在列表中，因为它在完全枚举源之前不知道最终的顺序。



如果您愿意使用自定义扩展方法：

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:

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:

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;

这应该有效（dt是DataTable对象）：

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

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();

如果可能发生多个RPTGroup具有相同的最大值加权然后你必须删除SingleOrDefault（） - 调用。

当然，如果那些列不是Int32类型，那么在那里替换适当的泛型类型。



编辑：根据问题下方的评论更新。

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.

Updated as per comment below question.

这篇关于需要Linq查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！