c# linq OrderByDescending with inner LastOrDefault

Question

Is there a way to order, let's say, all customers by the date of the last purchase?

For example

ctx.Customers.OrderByDescending(e => e.Purchases.LastOrDefault().DateTime);

It would be something like this, however, this doesn't work. It throws the exception

LINQ to Entities does not recognize the method 'Purchase
LastOrDefault[Purchase]
(System.Collections.Generic.IEnumerable`1[Purchase])'
method, and this method cannot be translated into a store expression

edit:

public class Customer
{
    public Customer()
    {
        Purchases = new List<Purchase>();
    }

    public int Id { get; set; }
    public string Name { get; set; }

    [JsonIgnore]
    public virtual IList<Purchase> Purchases { get; set; }
}

public class Purchase
{
    public int Id { get; set; }
    public int IdCustomer { get; set; }
    public DateTime DateTime { get; set; }

    public virtual Customer Customer { get; set; }
}

In Context I do have somthing like

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.HasRequired(s => s.Customer)
            .WithMany(p => p.Purchases)
            .HasForeignKey(s => s.IdCustomer);
}

Edit: Sorry, I typed wrong. The error persists even with LastOrDefault being method. — jairhumberto, Oct 15 '19 at 18:49
Can you show some code? The class holding the `Purchases` attribute and the class holding the `DateTime` attribute? — Corentin Pane, Oct 15 '19 at 18:51
@CorentinPane I could, the code I showed was supposed to work? — jairhumberto, Oct 15 '19 at 18:53
Please specify what orm and version you are using because it really important some things are not supported exactly in EF core. — Filip Cordas, Oct 15 '19 at 19:47

Austin T French · Answer 1 · 2019-10-15T20:00:18.497

2

ctx.Customers.OrderByDescending(e => e.Purchases.LastOrDefault().DateTime);

looks like a context query (Entity Framework, usually dbContext), so here you have an IQueryable not a List.

Entity Framework will try to convert this to a SQL statement before giving you results, but

SELECT * BOTTOM(X) FROM TABLE ORDER BY Purchases desc

is not an expression, but more importantly EF just doesn't recognize what you want to do.

Instead, you just want to flip the logic to:

SELECT * TOP(X) FROM TABLE ORDER BY Purchases asc

Or:

ctx.Customers.OrderBy(e => e.Purchases.FirstOrDefault().DateTime);

or you can order by on your subquery:

ctx.Customers.OrderBy(e => e.Purchases.OrderByDescending(x => x.propertyToSortOn)
.FirstOrDefault().DateTime);

Getting the last n records from the bottom of a sorted list, is actually the same as getting the top n from a list sorted the other way:

1,2,3,4,5,6 -> top 3 in ascending order = 1,2,3

6,5,4,3,2,1 -> bottom 3 in descending order = 3,2,1

edited Oct 15 '19 at 20:00

answered Oct 15 '19 at 19:50

Austin T French

5,022
1
22
40

I am not sure about this. Are related enumerations ordered in EF? This could return a different row when you run a query multiple times depending on the SQL server don't know about MySQL. – Filip Cordas Oct 15 '19 at 20:01
[I might be right on this](https://stackoverflow.com/questions/8746519/sql-what-is-the-default-order-by-of-queries) – Filip Cordas Oct 15 '19 at 20:10
@FilipCordas You are right, but so is the code. You need to *always* specify the sort column. EF will try to guess the default, st least sometimes. But it isn't nearly reliable enough when you need to rely on it. Using the inner OrderByDescending as in the last example will translate in EF 6.x at least as an inner `Select * top(x) from t order by [ENTITY2].[X] desc` or similar. – Austin T French Oct 15 '19 at 20:19
1

Your answer is correct just thought it was worth mentioning because this will have a bug unless he does order by. And first is just as random as last so his query doesn't make sense. – Filip Cordas Oct 15 '19 at 20:35

D Stanley · Answer 2 · 2019-10-15T19:58:32.247

0

LastOrDefault is not supported in Linq-to-Entities (meaning they have not yet developed a way to translate that to the equivalent SQL code). One option is to use AsEnumerable to do the ordering in memory:

ctx.Customers
   .AsEnumerable()
   .OrderByDescending(e => e.Purchases.LastOrDefault().DateTime);

However, since the order of Purchases is not deterministic, you may want to specify an order there as well:

ctx.Customers
   .AsEnumerable()
   .OrderByDescending(e => e.Purchases.OrderBy(p => p.DateTime).LastOrDefault());

or just use Max on the `Purchases':

ctx.Customers
   .AsEnumerable()
   .OrderByDescending(e => e.Purchases.Max(p => p.DateTime));

If the performance of any of those queries is not acceptable, the last resort would be to write the direct SQL and pass that to ctx.Customers.SqlQuery()

edited Oct 15 '19 at 19:58

answered Oct 15 '19 at 19:49

D Stanley

149,601
11
178
240

But this is extremely inefficient. – Filip Cordas Oct 15 '19 at 19:50
@FilipCordas How do you know that it would be much more inefficient than the equivalent SQL? Even if it is, what alternative to you propose? – D Stanley Oct 15 '19 at 19:51
@FilipCordas only maybe... If it's for a reasonable number of records IQueryable should be fine. – Austin T French Oct 15 '19 at 19:55
First I think this will pull every "Purchases" in a separate query if there is no include but I am not sure should be tested. But you could be right if he is pulling all the customers already this will probably be good enough. – Filip Cordas Oct 15 '19 at 19:56
@FilipCordas it _should_ still pull all of the customers and purchases in one query since only the ordering (not filtering) is done in-memory. – D Stanley Oct 15 '19 at 20:00
@DStanley Well you might be right I honestly don't know any more when I use EF Core things just start working in differently so I might be wrong. – Filip Cordas Oct 15 '19 at 20:03

c# linq OrderByDescending with inner LastOrDefault

2 Answers2