向 LINQ-2-SQL 添加非 SQL where 子句

Question

我有一个 Linq-2-SQL 可以更新 IEnumerable; LeftOverLocums 位于内部 foreach 循环之前。我需要在 Where 部分再添加一个布尔子句，但它与 SQL DB 无关。事实上，它是一个效用函数。请看下面的两个函数。第一个是主函数，第二个是我需要嵌入 Where 部分的函数。

    private void PreferenceFindJobs(ref JobCustomList jobList) {
        List<KeyValuePair<long, DateTime>> AlreadyPrefferedLocums = new List<KeyValuePair<long, DateTime>>();

        List<long> LocumsFilteringIDs = SchedulerMatrixStorage.Resources.Items.Select(col => Convert.ToInt64(col.Id)).ToList();

        IEnumerable<Locum> LeftOverLocums = null;

        foreach (JobCustom oneJob in jobList) {

            LeftOverLocums = from locums in DbContext.Locums
                                             join availabilities in DbContext.Availabilities on new {
                                                 OID = locums.OID
                                             } equals new {
                                                 OID = availabilities.LocumID
                                             }
                                             where
                                               LocumsFilteringIDs.Contains(locums.OID) &&
                                               locums.IsActive == true &&
                                               locums.IsLocumsExciteBan == false &&
                                               locums.IsGPHCBan == false &&
                                               locums.IsRPSGBBan == false &&
                                               locums.IsAdminMarkedComplete == true &&
                                               availabilities.AvailabilityStatusID == 1 &&
                                               availabilities.AvailableDate == oneJob.JobDate
                                             select locums;

            foreach (Locum oneLocum in LeftOverLocums) {
                    //This Locum can do this job

                    //Now check if he/she has been just alloted
                    if (AlreadyPrefferedLocums.Any(check => check.Key == oneLocum.OID && check.Value == oneJob.JobDate) == false) {
                        //No? Cool!
                        oneJob.LocumID = oneLocum.OID;
                        oneJob.LocumName = oneLocum.FirstName + " " + oneLocum.LastName;

                        //Add to the list to prevent double allocation
                        AlreadyPrefferedLocums.Add(new KeyValuePair<long, DateTime>(oneJob.LocumID, oneJob.JobDate));

                    } else {
                        continue;

                    }

            }

        }
    }

    public enum LocumType {
        Pharmacist = 1,
        Dispenser = 2,
        AccreditedCheckingTechnician = 3
    }

    public static Boolean IsJobTypeOK(Enumerations.LocumType _Job, Enumerations.LocumType _Locum) {
        bool ProcessResult;

        switch (_Job) {
            case  Enumerations.LocumType.Pharmacist:
            ProcessResult = _Locum == Enumerations.LocumType.Pharmacist;
            break;

            case Enumerations.LocumType.Dispenser:
            ProcessResult = _Locum == Enumerations.LocumType.Dispenser;
            break;

            case Enumerations.LocumType.AccreditedCheckingTechnician:
            ProcessResult = _Locum == Enumerations.LocumType.AccreditedCheckingTechnician || _Locum == Enumerations.LocumType.Dispenser;
            break;

            default:
            ProcessResult = false;
            break;
        }

        return ProcessResult;
    }

这是我想要的 Where 子句：

where
    IsJobTypeOK(oneJob.JobTypeID, locums.LocumTypeID) &&
    LocumsFilteringIDs.Contains(locums.OID) &&
    locums.IsActive == true &&
    locums.IsLocumsExciteBan == false &&
    locums.IsGPHCBan == false &&
    locums.IsRPSGBBan == false &&
    locums.IsAdminMarkedComplete == true &&
    availabilities.AvailabilityStatusID == 1 &&
    availabilities.AvailableDate == oneJob.JobDate

Answer 1

您根本不必使用“IsJobTypeOK”方法。

where
(
  (oneJob.JobTypeID == Enumerations.LocumType.AccreditedCheckingTechnician && 
     locums.LocumTypeID == Enumerations.LocumType.Dispenser) || 
  oneJob.JobTypeID == locums.LocumTypeID
) &&
LocumsFilteringIDs.Contains(locums.OID) &&
locums.IsActive == true &&
locums.IsLocumsExciteBan == false &&
locums.IsGPHCBan == false &&
locums.IsRPSGBBan == false &&
locums.IsAdminMarkedComplete == true &&
availabilities.AvailabilityStatusID == 1 &&
availabilities.AvailableDate == oneJob.JobDate

Answer 2

最好的方法是在从数据库调用结果后过滤结果。

//Get you LeftOverLocums like you are now
//then
LeftOverLocums = LeftOverLocums
                 .Where(l => l.IsJobTypeOK(oneJob.JobTypeID, l.LocumTypeID))

数据库上不需要任何额外的东西