c# - Entity Framework Core - 迁移到 .net core 后首先返回重复结果

Question

所以我在 ef core 中有一个以下查询，这会导致重复结果，即使我先调用或默认调用也很难

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 中的 Entity 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; }
}

Answer 1

不幸的是，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')