无法将指定SQL查询转换为LINQ查询，寻求技术解决方案

查询语法（Query Syntax） #

var result = from sy in context.Survey.AsNoTracking()
             join ap in context.AssetPurity.AsNoTracking()
             on new { sy.LocationId, sy.SessionUID } equals new { ap.LocationId, ap.SessionUID }
             where ap.SceneTypeCode != 5 && ap.SceneSubTypeCode != 1
                && sy.LocationId == 1
                && sy.SurveyStatusId == 1
                && sy.IsDeleted == 0
                && sy.SurveyDate >= curPeriod.startPeriodDate.Value
                && sy.SurveyDate <= curPeriod.endPeriodDate.Value
             select sy;

方法语法（Method Syntax） #

var result = context.Survey.AsNoTracking()
    .Join(
        context.AssetPurity.AsNoTracking(),
        sy => new { sy.LocationId, sy.SessionUID },
        ap => new { ap.LocationId, ap.SessionUID },
        (sy, ap) => new { sy, ap }
    )
    .Where(x => x.ap.SceneTypeCode != 5 && x.ap.SceneSubTypeCode != 1
                && x.sy.LocationId == 1
                && x.sy.SurveyStatusId == 1
                && x.sy.IsDeleted == 0
                && x.sy.SurveyDate >= curPeriod.startPeriodDate.Value
                && x.sy.SurveyDate <= curPeriod.endPeriodDate.Value)
    .Select(x => x.sy);

关键说明 #

• AsNoTracking()对应SQL中的WITH(NOLOCK)，关闭EF实体追踪以提升查询性能，若需要严格的读未提交隔离级别，需额外设置context.Database.SetTransactionIsolationLevel(IsolationLevel.ReadUncommitted)
• 多字段关联时用匿名类型匹配，确保关联字段的名称、类型完全一致
• 最终选择sy对象，对应SQL中SELECT SY.*的需求

内容的提问来源于stack exchange，提问作者Shailendra Pal