无法在EF中排序子对象

本文关键字:排序 对象 EF | 更新日期: 2023-09-27 17:50:11

我有一个linq查询,在内存中对数据执行时工作,但在使用EF时不能正常工作。

首先,这是我的查询(注意,我已经将查询更改为仅对单个值 justOneDcn 进行操作,而不是整个context.RepairDocuments,以便生成的SQL更易于阅读):

var justOneDcn = context.RepairDocuments.Where(d => d.Dcn = "00001");
var q = from dcn in justOneDcn
    let orderedHistory = dcn.History.OrderBy(h => h.Date)
    let xdaHistoryPoint = orderedHistory.FirstOrDefault(h => h.Status == DocumentStatus.Xda)
    select xdaHistoryPoint;

我已经删除了不必要的额外代码。问题是EF生成了这个SQL:

SELECT 
    [... col list ...]
    CASE WHEN ([Element1].[id] IS NOT NULL) THEN [Element1].[date] ELSE @p__linq__0 END AS [C2]
    FROM   (SELECT [Extent1].[id] AS [id]
        FROM [dbo].[repair_document] AS [Extent1]
        WHERE N'00001' = [Extent1].[dcn] ) AS [Filter1]
    OUTER APPLY  (SELECT TOP (1) 
        [... col list ...]
        FROM [dbo].[repair_document_history] AS [Extent2]
        WHERE ([Filter1].[id] = [Extent2].[repair_document_id]) AND (4 =  CAST( [Extent2].[status] AS int)) AND ( CAST( [Extent2].[status] AS int) IS NOT NULL) ) AS [Element1]
-- p__linq__0: '1/1/0001 12:00:00 AM' (Type = DateTime2)

但是上面的SQL给出了错误的数据,应该在where子句后面添加ORDER BY [Extent2].date。下面修改的SQL给了我正确的结果:

 SELECT 
    [... col list ...]
    CASE WHEN ([Element1].[id] IS NOT NULL) THEN [Element1].[date] ELSE @p__linq__0 END AS [C2]
    FROM   (SELECT [Extent1].[id] AS [id]
        FROM [dbo].[repair_document] AS [Extent1]
        WHERE N'00001' = [Extent1].[dcn] ) AS [Filter1]
    OUTER APPLY  (SELECT TOP (1) 
        [... col list ...]
        FROM [dbo].[repair_document_history] AS [Extent2]
        WHERE ([Filter1].[id] = [Extent2].[repair_document_id]) AND (4 =  CAST( [Extent2].[status] AS int)) AND ( CAST( [Extent2].[status] AS int) IS NOT NULL) 
        ORDER BY [Extent2].date) AS [Element1]
-- p__linq__0: '1/1/0001 12:00:00 AM' (Type = DateTime2)

注意结尾的ORDER BY

我快疯了!我做错了什么?此外,将数据加载到内存不是一个选项,我有大约5000个Dcns,每个dcn在它的历史表中有10-15条记录。

谢谢!

编辑:

下面是实体声明

public class RepairDocument {
   /* .... */
   public virtual ICollection<RepairDocumentHistory> History { get; set; }
}

无法在EF中排序子对象

如果改成:

var justOneDcn = context.RepairDocuments.Where(d => d.Dcn = "00001").OrderBy(d => d.History.Date);

从下面的命令中删除 OrderBy

你能不能这样做:

var item =
(from r in context.RepairDocuments
from h in r.History
where r.Dcn == "00001" && h.Status == DocumentStatus.Xda
orderby h.Date
select h).FirstOrDefault();