sql - 数据库查询 - 异常查询

Question

我正在尝试将查询传输到 LINQ

这有点复杂，我不知道我是否做对了

选择正确保存也有问题

SQL查询：

SELECT ISNULL(SUM(1),0) TotalSubmitted, 
ISNULL(SUM(CASE WHEN Pic.PhotoFileName IS NULL THEN 0 ELSE 1 END),0) TotalAssigned 
FROM tblFromPhone Ph 
INNER JOIN tblOfficer O ON Ph.OfficerCode = O.Code 
OUTER APPLY(SELECT PhotoFileName FROM tblPhotos P 
WHERE P.validityTo IS NULL 
AND P.PhotoFileName = Ph.DocName 
AND P.OfficerID = O.OfficerID)Pic 
WHERE CAST(Ph.LandedDate AS DATE) BETWEEN @FromDate AND @ToDate 
AND O.ValidityTo IS NULL 
AND DocType = N'E' 
AND OfficerCode = @OfficerCode

我的 LINQ：

var res = (from FP in context.TblFromPhone
           join O in context.TblOfficer on FP.OfficerCode equals O.Code
           join P in context.TblPhotos on FP.DocName equals P.PhotoFileName into P
           from Ph in P.Where(x => x.OfficerId == O.OfficerId).DefaultIfEmpty()
           where Ph.ValidityTo == null
           where FP.LandedDate >= DateTime.Parse(fromDate)
           && FP.LandedDate <= DateTime.Parse(toDate)
           && O.ValidityTo == null
           && FP.DocType == "E"
           && FP.OfficerCode == officerCode
           select new EnrolmentModel()
           {
               TotalSubmitted = // How to make?
               TotalAssigned = // How to make?
           })
           .FirstOrDefault();

标签： sqlsql-serverlinq