TransWikia.com

LINQ query orderBy a subquery

Stack Overflow Asked by GolfBravo on December 9, 2020

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"
}

]

2 Answers

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

Correct answer by Rena on December 9, 2020

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
    }

Answered by Svyatoslav Danyliv on December 9, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP