我有一个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; }
}
如果改成:
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();