How to avoid OrderByDescending in query linq

Question

I have the linq query:

var ed = db.table
.GroupBy(x => x.Sn)
.Select(g => g.OrderByDescending(x => x.Date).FirstOrDefault());

I need to rewrite this query for server-side evaluation.

My table:

Sn    Value      Data     
150   180.3    01/06/2020  
150   195.0    01/05/2020  
149   13.3     01/06/2020  
345   27.5     27/06/2013
....

What is the question here? what issue you are facing with this query? What error are you getting? — Chetan Ranpariya, Jun 22 '20 at 04:25
If i execute this query I get this exception: OrderByDescending(x => x.Date) could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. — Disshell, Jun 22 '20 at 04:40
i need select all objects with max date for each ID. I already asked a similar question here, but that answer work only client side. Unfortunately my table contains a lot of data and i can't afford client side evaluation. — Disshell, Jun 22 '20 at 06:19
Use a window function with PARTITION BY to group by the Sn column, then get the first row of each partition by using the ROW_NUMBER() function. This cannot be done with Linq — Jonas Høgh, Jun 22 '20 at 07:17

score 2 · Answer 1 · answered Jun 22 '20 at 05:21

2

.Select(g => g.OrderByDescending(x => x.Date).FirstOrDefault())

is probably just:

.Select(g => g.Max(x => x.Date))

Which the parser probably handles better

answered Jun 22 '20 at 05:21

Marc Gravell

927,783
236
2,422
2,784

Will it select max Date for each objects or max date in the table? – Disshell Jun 22 '20 at 05:52
@disshell as written: the max in the group, which is the same thing as "order by descending, first or default" – Marc Gravell Jun 22 '20 at 05:56
Unfortunately, this query return IQueryable, but i need get full object from my table. – Disshell Jun 22 '20 at 06:10

score 0 · Answer 2 · answered Jun 22 '20 at 07:33

You can try using Aggregate

var ed = db.table
           .GroupBy(x => x.Sn)
           .Select(x => x.Aggregate((max, cur) => max.Date > cur.Date ? max : cur));

This might help you to know more How to use LINQ to select object with minimum or maximum property value.

score 0 · Answer 3 · answered Jun 22 '20 at 09:10

It depends on whether dt.Table is IQueryable or not.

Normally an IQueryable is to be executed by a different process, quite often a database management system. It that is the case, you'll have to use OrderBy followed by a FirstOrDefault.

Luckily proper database management systems are extremely optimized to sort. If you are not satisfied with the efficiency of the sort, and you don't change your table too often, consider adding an extra index in DbContext.OnModelCreating:

modelBuilder.Entity<Customer>()
    .HasIndex(customer => customer.Name)

Your database management system knows this extra index, and can immediately return the element that the last item of the index refers to.

Whenever you change the name, or add a new Customer, the index has to be recreated. So don't do this if you are changing customer names often, like 10 times a second.

If dt.table is not IQueryable, but IEnumerable, OrderBy is relatively slow. Alas there is no Enumerable.Max for you, but you can use on of the overloads of Enumerable.Aggregate.

As you are certain that every group contains at least one element, you can use the overload without Seed:

var result = db.table.GroupBy(x => x.Sn)
   .Aggregate( (maxItem, nextitem) =>(nextItem.Date > maxItem.Date) ?? nextItem : maxItem)

If you use this quite often, consider to create an extension method. Creating an extension method is quite easy. See extension methods demystified

public static T MaxOrDefault<T, TProperty> MaxPropertyOrDefault(
    this IEnumerable<T> source,
    Func<TSource, TProperty> propertySelector)
{
     return MaxPropertyOrDefault(source, propertySelector, null)
}

Overload with comparer: if comparer equals null, use default comparer

public static T MaxOrDefault<T, TProperty> MaxPropertyOrDefault(
    this IEnumerable<T> source,
    Func<TSource, TProperty> propertySelector,
    IComparer<TProperty) comparer)
{
    // TODO: what to do if source == null?
    // TODO: what to do if propertySelector == null?
    if (comparer == null) comparer = Comparer<TProperty>.Default();
    
    var enumerator = source.GetEnumerator();
    if (!enumerator.MoveNext)
    {
        // empty source, return default:
        return default(T);
    }
    else
    {
        TProperty maxPropertyValue = propertySelector(enumerator.Current);
        T maxValue = enumerator.Current();

        while (enumerator.MoveNext())
        {
             TProperty currentPropertyValue = propertySelector(enumerator.Current);
             if (comparer.Compare(currentPropetyValue, maxPropertyValue) > 0)
             {
                 maxPropertyValue = currentPropertyValue;
                 maxValue = enumerator.Current;
             } 
        }
        return maxValue;
    }
}

Usage:

var ed = db.table.GroupBy(x => x.Sn)
           .Select(group => group.MaxOrDefault(groupElement => groupElement.Date);

How to avoid OrderByDescending in query linq

3 Answers3