Multiple WHERE's in same LINQ 2 SQL Method

Question

I have the below LINQ Method I am trying to create. The issue seems to be the Second WHERE clause. I am getting this error -->

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<MatrixReloaded.Data.CMO.tblWorkerHistory>' to 'bool'

I also had && there vs WHERE but I was getting a similar error. I don't NEED anything from tblWorkerHistories except the EndDate stuff.

There is a Many To Many relationship between the 2 tables with EnrollmentID being a FK on both.

public static DataTable GetCurrentWorkersByEnrollmentID(int enrollmentID)
    {
        using (var context = CmoDataContext.Create())
        {
            context.Log = Console.Out;

            var currentWorkers = from enrollment in context.tblCMOEnrollments
                                 where enrollment.EnrollmentID == enrollmentID
                                 where enrollment.tblWorkerHistories.Where(a => a.EndDate == null || a.EndDate > DateTime.Now)
                                 select
                                     new
                                         {
                                             enrollment.CMONurseID,
                                             enrollment.CMOSocialWorkerID,
                                             SupportWorkerName = enrollment.tblSupportWorker.FirstName + " " + enrollment.tblSupportWorker.LastName,
                                             SupportWorkerPhone = enrollment.tblSupportWorker.Phone
                                         };

            return currentWorkers.CopyLinqToDataTable();
        }
    }

Answer 1

I suspect you mean .Any instead of .Where in the sub-query; the outermost .Where (i.e. the second where) expects a predicate expression, but yours is currently a selector - try:

where enrollment.tblWorkerHistories.Any(
      a => a.EndDate == null || a.EndDate > DateTime.Now)

Answer 2

This is the problem:

where enrollment.tblWorkerHistories.Where(/* stuff */)

Where returns a sequence... whereas you need something that will return a Boolean value. What are you trying to do with that embedded Where clause?

As Marc says, it could be that you just need an Any call instead of Where... but if you could explain what you're trying to do, that would make it a lot easier to help you. Note that Any does return a Boolean value, instead of a sequence.

EDIT: Okay, so in SQL you'd use a join, but you don't need an explicit join here because LINQ is implicitly doing that for you, right? If you're trying to find enrollments where any of the histories match the date, and you don't care about the histories themselves, then Any is indeed what you want:

var currentWorkers = from enrollment in context.tblCMOEnrollments
                     where enrollment.EnrollmentID == enrollmentID
                     where enrollment.tblWorkerHistories.Any
                           (a => a.EndDate == null || a.EndDate > DateTime.Now)
                     select ...