c# – Entity Framework Core – 迁移到.net核心后首先返回重复的结果
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了c# – Entity Framework Core – 迁移到.net核心后首先返回重复的结果,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4786字,纯文字阅读大概需要7分钟。
内容图文
所以我在ef核心中有一个以下查询,这导致重复的结果甚至很难我首先调用或默认它
DateTime maxDate = SqlDateTime.MaxValue.Value;
var results = (from r in _dbContext.audit_Results
join e in _dbContext.audit_DataEncounters on new { r.IdAudit, r.PatientFirstName, r.PatientLastName, r.PatientDOB } equals new { e.IdAudit, PatientFirstName = e.FirstName, PatientLastName = e.LastName, PatientDOB = e.DateOfBirth } into eJoin
let e = eJoin.Where(it => it.StartDate <= r.TransactionDate && r.TransactionDate <= (it.EndDate ?? maxDate))
.OrderBy(it => it.Inpatient).OrderByDescending(it => it.StartDate)
.FirstOrDefault()
where r.IdAudit == idAudit && r.PatientFirstName == "ERIC" && r.PatientLastName == "BROOKS"
select new AuditResultBulkModel()
{
IdResult = r.IdResult,
map_HasPatient = eJoin.Any(),
map_IdEncounter = e != null ? e.IdEncounter : (int?)null
}).ToList();
生成的查询如下所示:
SELECT *
FROM [audit_Results] AS [r]
LEFT JOIN [audit_DataEncounters] AS [e] ON ((([r].[IdAudit] = [e].[IdAudit]) AND (([r].[PatientFirstName] = [e].[FirstName]) OR ([r].[PatientFirstName] IS NULL AND [e].[FirstName] IS NULL))) AND (([r].[PatientLastName] = [e].[LastName]) OR ([r].[PatientLastName] IS NULL AND [e].[LastName] IS NULL))) AND (([r].[PatientDOB] = [e].[DateOfBirth]) OR ([r].[PatientDOB] IS NULL AND [e].[DateOfBirth] IS NULL))
WHERE (([r].[IdAudit] = @__idAudit_1)
ORDER BY [r].[IdAudit], [r].[PatientFirstName], [r].[PatientLastName], [r].[PatientDOB]
有人能告诉我如何从中获得正确的结果吗?
在过去(.net Framework中的实体框架) – 这用于使用OUTER APPLY生成非重复结果.
编辑:
我的EF Core版本是2.0.0.
数据模型如下:
public class audit_Result
{
[Key]
public int IdResult { get; set; }
public int IdAudit { get; set; }
public bool? map_HasPatient { get; set; }
public int? map_IdEncounter { get; set; }
public virtual audit_Audit audit_Audit { get; set; }
}
public class audit_DataEncounter: IAuditEntity
{
[Key]
public int IdEncounter { get; set; }
public int IdAudit { get; set; }
[StringLength(50)]
public string FirstName { get; set; }
[StringLength(50)]
public string LastName { get; set; }
public DateTime? DateOfBirth { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
public bool? Inpatient { get; set; }
public virtual audit_Audit Audit { get; set; }
}
public class audit_Audit
{
[Key]
public int IdAudit { get; set; }
public virtual ICollection<audit_DataEncounter> audit_DataEncounters { get; set; }
public virtual ICollection<audit_Result> audit_Result { get; set; }
}
解决方法:
不幸的是,EF Core查询翻译仍然是(截至目前为止最新的2.0)远远不是最好的.如果您打开EF Core日志记录,您将看到很多警告,表明某些表达式无法翻译并将在本地进行评估(所谓的客户评估).
我可以为这个特定查询建议的唯一解决方法是消除join和let语句并使用map_HasPatient和map_IdEncounter投影属性的子查询(具有重复的连接过滤条件):
var results = (
from r in _dbContext.audit_Results
where r.IdAudit == idAudit && r.PatientFirstName == "ERIC" && r.PatientLastName == "BROOKS"
select new AuditResultBulkModel
{
IdResult = r.IdResult,
map_HasPatient = _dbContext.audit_DataEncounters
.Any(e => r.IdAudit == e.IdAudit && r.PatientFirstName == e.FirstName && r.PatientLastName == e.LastName && r.PatientDOB == e.DateOfBirth),
map_IdEncounter = _dbContext.audit_DataEncounters
.Where(e => r.IdAudit == e.IdAudit && r.PatientFirstName == e.FirstName && r.PatientLastName == e.LastName && r.PatientDOB == e.DateOfBirth
&& e.StartDate <= r.TransactionDate && r.TransactionDate <= (e.EndDate ?? maxDate))
.OrderBy(e => e.Inpatient).ThenByDescending(e => e.StartDate)
.Select(e => (int?)e.IdEncounter)
.FirstOrDefault()
}).ToList();
这转换为SQL查询,如下所示:
SELECT [r].[IdResult], (
SELECT CASE
WHEN EXISTS (
SELECT 1
FROM [audit_DataEncounters] AS [e]
WHERE ((([r].[IdAudit] = [e].[IdAudit]) AND (([r].[PatientFirstName] = [e].[FirstName]) OR ([r].[PatientFirstName] IS NULL AND [e].[FirstName] IS NULL))) AND (([r].[PatientLastName] = [e].[LastName]) OR ([r].[PatientLastName] IS NULL AND [e].[LastName] IS NULL))) AND ([r].[PatientDOB] = [e].[DateOfBirth]))
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END
) AS [map_HasPatient], (
SELECT TOP(1) [e0].[IdEncounter]
FROM [audit_DataEncounters] AS [e0]
WHERE ((((([r].[IdAudit] = [e0].[IdAudit]) AND (([r].[PatientFirstName] = [e0].[FirstName]) OR ([r].[PatientFirstName] IS NULL AND [e0].[FirstName] IS NULL))) AND (([r].[PatientLastName] = [e0].[LastName]) OR ([r].[PatientLastName] IS NULL AND [e0].[LastName] IS NULL))) AND ([r].[PatientDOB] = [e0].[DateOfBirth])) AND ([e0].[StartDate] <= [r].[TransactionDate])) AND ([r].[TransactionDate] <= COALESCE([e0].[EndDate], @__maxDate_1))
ORDER BY [e0].[Inpatient], [e0].[StartDate] DESC
) AS [map_IdEncounter]
FROM [audit_Results] AS [r]
WHERE (([r].[IdAudit] = @__idAudit_0) AND ([r].[PatientFirstName] = N'ERIC')) AND ([r].[PatientLastName] = N'BROOKS')
内容总结
以上是互联网集市为您收集整理的c# – Entity Framework Core – 迁移到.net核心后首先返回重复的结果全部内容,希望文章能够帮你解决c# – Entity Framework Core – 迁移到.net核心后首先返回重复的结果所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。