LINQ query orderBy a subquery

How do I order by date using LINQ query for an included table

    public async Task<IList<TaskSchedule>> GetTask(int id)
    {    
         var taskSchedule = await _context.TaskSchedules
            .Where(u => u.Id == id)
            .Include(ts => ts.Notes.OrderBy(i => i.DateCreated))   //this is what needs to be in ascneding order of date
            .Include(ts => ts.Attachments)
            .Include(c => c.customer)
            .ToListAsync();      
            
        return taskSchedule;  
    }

Below code works, however, it does not sort out the notes in date ascending order

   public async Task<IList<TaskSchedule>> GetTask(int id)
    {    
         var taskSchedule = await _context.TaskSchedules
            .Where(u => u.Id == id)
            .Include(ts => ts.Notes)
            .Include(ts => ts.Attachments)
            .Include(c => c.customer)
            .ToListAsync();
       
        return taskSchedule;  
    }

The error message i get is a 500 (Internal Server Error)

System.ArgumentException: Expression of type ‘System.Linq.IOrderedQueryable1[Schedular.API.Models.Note]' cannot be used for return type 'System.Linq.IOrderedEnumerable1[Schedular.API.Models.Note]’

This is the API data that comes back when I use the working code. The notes need to be in the order of the date it was created. Currently, it’s the other way around.

[
{
    "id": 102,
    "title": "this should display",
    "start": null,
    "end": null,
    "isClosed": false,
    "highPriority": false,
    "hasTimeLimit": false,
    "customer": null,
    "customerId": null,
    "notes": [
        {
            "id": 70,
            "notesInfo": "add some notes first",
            "dateCreated": "2020-11-17T12:20:00",
            "user": null,
            "userId": 1,
            "taskScheduleId": 102
        },
        {
            "id": 72,
            "notesInfo": "add some notes second",
            "dateCreated": "2020-11-18T16:35:00",
            "user": null,
            "userId": 1,
            "taskScheduleId": 102
        },
        {
            "id": 73,
            "notesInfo": "add some notes third",
            "dateCreated": "2020-11-19T18:35:00",
            "user": null,
            "userId": 1,
            "taskScheduleId": 102
        }
    ],
    "attachments": [],
    "userCurrentAssignedId": 1,
    "userCurrentAssigned": null,
    "userLastEditId": 1,
    "userLastEdit": null,
    "userLastEditDate": "2020-11-19T15:37:00",
    "taskCreatedDate": "2020-11-19T15:37:00"
}

]

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

It is not valid use OrderBy inside Include,change like below:

var taskSchedule = await _context.TaskSchedules
        .Where(u => u.Id == id)
        .Include(ts => ts.Notes)
        .Include(ts => ts.Attachments)
        .Include(c => c.customer)
        .ToListAsync();

taskSchedule.ForEach(t => t.Notes = t.Notes.OrderBy(n => n.DateCreated).ToList());

Method 2

To sum-up this question and comments.

This query originally supported only by EF Core 5 and later. Include is not a subquery it’s a directive for EF Core to load related entities and evolution of this functionality is introduced in EF Core 5.

Anyway, usually it is not needed to do such queries because they are related to DTO mapping. So just do such mapping by hands and this query should work for EF Core 3.x also.

 var taskSchedule = 
    from ts in await _context.TaskSchedules
    where ts.Id == id
    select new TaskScheduleDTO
    {
        Notes = ts.Notes.OrderBy(n => n.DateCreated).ToList(),
        Attachments = ts.Attachments.ToList(),
        Cutomser = ts.Customer
    }

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0