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.IOrderedQueryable
1[Schedular.API.Models.Note]' cannot be used for return type 'System.Linq.IOrderedEnumerable
1[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"
}
]
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP