LINQ查询顺序通过子查询

Question

如何使用包含表的LINQ查询按日期排序

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

然而，下面的代码是有效的，它不会按照日期升序对钞票进行排序

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

我得到的错误消息是一个500(内部服务器错误(

System.ArgumentException:类型为"System.Linq.IOrderedQueryable1[Schedular.API.Models.Note]' cannot be used for return type 'System.Linq.IOrderedEnumerable1[Schedul.neneneba API.Models.Note]"的表达式

这是当我使用工作代码时返回的API数据。注释需要按照创建日期的顺序排列。目前，情况正好相反。

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

]

Answer 1

在Include中使用OrderBy是无效的，更改如下：

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

Answer 2

总结这个问题和评论。

此查询最初仅由EF Core 5及更高版本支持。Include不是子查询，它是EF Core加载相关实体的指令，该功能的演变在EF Core 5中介绍。

无论如何，通常不需要进行这样的查询，因为它们与DTO映射有关。因此，只需手动进行这样的映射，这个查询也应该适用于EF Core 3.x。

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
}