Linq - Order by in Include

linq orderby
entity framework core loading child collection with sort order
entity framework sort related entities
ef core order by
order by entity framework
tolist orderby
linq order by sub collection
entity framework core include child collection

I have a situation where OrderBy need to be done for Include object. This is how I have tried so far

Customers query = null;

try
{
    query = _context.Customers
        .Include(x => x.CustomerStatus)
        .ThenInclude(x => x.StatusNavigation)
        .Select(x => new Customers()
        {
            Id = x.Id,
            Address = x.Address,
            Contact = x.Contact,
            Name = x.Name,
            CustomerStatus = new List<CustomerStatus>
            {
                x.CustomerStatus.OrderByDescending(y => y.Date).FirstOrDefault()
            }
        })
        .FirstOrDefault(x => x.Id == 3);
}
catch (Exception ex)
{
    throw;
}

The above code successfully ordering the include element but it is not including it's child table. Eg: Customer include CustomerStatus but CustomerStatus not including StatusNavigation tables.

I even tried with this but neither it can help me

_context.Customers
    .Include(x => x.CustomerStatus.OrderByDescending(y => y.Date).FirstOrDefault())
    .ThenInclude(x => x.StatusNavigation).FirstOrDefault(x => x.Id == 3);

What am I doing wrong please guide me someone

Even I tried this way

var query = _context.CustomerStatus
    .GroupBy(x => x.CustomerId)
    .Select(x => x.OrderByDescending(y => y.Date).FirstOrDefault())
    .Include(x => x.StatusNavigation)
    .Join(_context.Customers, first => first.CustomerId, second => second.Id, (first, second) => new Customers
    {
        Id = second.Id,
        Name = second.Name,
        Address = second.Address,
        Contact = second.Contact,
        CustomerStatus = new List<CustomerStatus> {
            new CustomerStatus
            {
                Id = first.Id,
                CustomerId = first.CustomerId,
                Date = first.Date,
                StatusNavigation = first.StatusNavigation
            }
        },
    }).FirstOrDefault(x => x.Id == 3);

but this is hitting a databases a 3 times and filtering the result in memory. First select all data from customer status and then from status and then from customer then it filter all the data in memory. Is there any other efficient way to do this??

This is how I have prepared by entity class


As @Chris Pratt mentioned once you are doing new Customer inside the select you are creating a new model. You are discarding the models build by the EntityFramework. My suggestion would be have the query just:

query = _context.Customers
    .Include(x => x.CustomerStatus)
    .ThenInclude(x => x.StatusNavigation);

Like this you would have an IQueryable object which it would not be executed unless you do a select from it:

var customer3 = query.FirstOrDefault(x=>x.Id==3)

Which returns the customer and the interlinked tables (CustomerStatus and StatusNavigation). Then you can create the object that you want:

var customer = new Customers()
    {
        Id = customer3.Id,
        Address = customer3.Address,
        Contact = customer3.Contact,
        Name = x.Name,
        CustomerStatus = new List<CustomerStatus>
        {
            customer3.CustomerStatus.OrderByDescending(y => y.Date).FirstOrDefault()
        }
    })

In this way you can reuse the query for creating different response objects and have a single querying to database, but downside is that more memory is used then the original query (even though it shouldn't be too much of an issue).

If the model that is originally return from database doesn't meet the requirements (i.e. you always need to do: CustomerStatus = new List {...} ) it might indicate that the database schema is not well defined to the needs of the application, so a refactoring might be needed.

Order a subquery in linq .include portion, Take a look here: c# - Order by with Linq + Include - Stack Overflow[^]. This method performs a stable sort; that is, if the keys of two elements are equal, the order of the elements is preserved. In contrast, an unstable sort does not preserve the order of elements that have the same key. In query expression syntax, an orderby (Visual C#) or Order By (Visual Basic) clause translates to an invocation of OrderBy. See


What I think is happening is that you are actually overriding the Include and ThenInclude. Include is explicitly to eager-load a navigation property. However, you're doing a couple of things that are likely hindering this.

First, you're selecting into a new Customer. That alone may be enough to break the logic of Include. Second, you're overriding what gets put in the CustomerStatus collection. That should ideally be just loaded in automatically via Include, but by altering it to just have the first entity, you're essentially throwing away the effect of Include. (Selecting a relationship is enough to cause a join to be issued, without explicitly calling Include). Third, the ThenInclude is predicated on the Include, so overriding that is probably throwing out the ThenIncude as well.

All this is conjecture. I haven't done anything exactly like what you're doing here before, but nothing else makes sense.

Try selecting into a new CustomerStatus as well:

CustomerStatus = x.CustomerStatus.OrderByDescending(o => o.Date).Select(s => new CustomerStatus
{
    x.Id,
    x.Status,
    x.Date,
    x.CustomerId,
    x.Customer,
    x.StatusNavigation
 })

You can remove the Include/ThenInclude at that point, because the act of selecting these relationships will cause the join.

LINQ orderby in include - MSDN, hello everyone i want to make an order by ascending inside the include can anyone help me? // Models. public class Menu { public int MenuID  The Linq OrderBy method in C# is used to sort the data in ascending order. The most important point that you need to keep in mind is this method is not going to change the data rather it is just changing the order of the data. You can use the OrderBy method on any data type i.e. you can use character, string, decimal, integer, etc.


After Reading from Couple of sources (Source 1) and (Source 2). I think what is happening is that If you use select after Include. It disregards Include even if you are using Include query data in select. So to solve this use .AsEnumerable() before calling select.

query = _context.Customers
    .Include(x => x.CustomerStatus)
    .ThenInclude(x => x.StatusNavigation)
    .AsEnumerable()
    .Select(x => new Customers()
    {
        Id = x.Id,
        Address = x.Address,
        Contact = x.Contact,
        Name = x.Name,
        CustomerStatus = new List<CustomerStatus>
        {
            x.CustomerStatus.OrderByDescending(y => y.Date).FirstOrDefault()
        }
    })
    .FirstOrDefault(x => x.Id == 3);

How to orderby an include entity in Entity Framework, Include("SubQuestions. Questions table has a one to many relationship with SubQuestions table, which I would like to order in the linq. LINQ includes five sorting operators: OrderBy, OrderByDescending, ThenBy, ThenByDescending and Reverse LINQ query syntax does not support OrderByDescending, ThenBy, ThenByDescending and Reverse. It only supports 'Order By' clause with 'ascending' and 'descending' sorting direction.


[SOLVED], The result you are trying to return is the ordered list of children. That's not what you want. Instead sort the children then return the parent: Order a subquery in linq .include portion. Rate this: Please Sign up or sign in to vote. See more: LINQ. ASP.NET-Core. Hi, On my very first project on ASP Core I have


OrderBy & OrderByDescending - Sorting Operators, LINQ includes five sorting operators: OrderBy, OrderByDescending, ThenBy, ThenByDescending and Reverse. LINQ query syntax does not support OrderByDescending, ThenBy, ThenByDescending and Reverse. It only supports 'Order By' clause with 'ascending' and 'descending' sorting direction. In a LINQ query, the first step is to specify the data source. In C# as in most programming languages a variable must be declared before it can be used. In a LINQ query, the from clause comes first in order to introduce the data source ( customers ) and the range variable ( cust ).


[SOLVED], You could load the data and sort in memory after loading it. IEnumerable<Team> teams = _ctx.Teams .Include(x => x.TeamMembers) .Include(x  1. query the data you want to order by into its own variable. 2. use that collection as the main list to get other data from e.g. club names. you just sometimes have to flip your thinking about object queries linq. Also watch how you do Include statement as you should include parent table data first.