实体框架的核心 - 迁移到.NET的核心后，首先返回重复结果

问题描述：

，所以我必须在EF核心下面的查询，这会导致重复的结果，即使艰难IM呼叫的第一或默认的就可以了实体框架的核心 - 迁移到.NET的核心后，首先返回重复结果

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

如果你正在得到不需要的重复，你可以不使用不同的删除它们吗？看看这篇文章：https://stackoverflow.com/questions/4539668/entity-framework-select-distinct-name – BastianBuhrkall

请发布在查询中使用的实体模型。另外指定EF Core版本。 –

@IvanStoev我将自己的数据模型 –

答

不幸的是EF核心查询转换仍然是（如最新的在这个时候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')

实体框架的核心 - 迁移到.NET的核心后，首先返回重复结果

相关推荐