Lambda: using .Include() with linked table data in EF

Question

I am trying to filter linked table data using lambda .Include() with the following:

var jobs = db.jobs.Include(d => d.docs)
           .Where(d => d.docs.startdate >= date1 && d => d.docs.enddate <= date1);
return View(jobs);

This doesn't work though. Does anyone know what the correct syntax is to filter the linked table data correctly? Thanks!

UPDATE:

d.docs.startdate
d.docs.enddate

".startdate" and ".enddate" are not showing up in intellisense when I use d.docs.startdate or d.docs.enddate. I have linked the table relationships and saved the changes but even after using the .Include() command in code, intellisense cannot find the startdate and enddate fields in the docs table through intellisense.

ERROR: 'System.Collections.Generic.ICollection' does not contain a definition for 'duedate' and no extension method 'duedate' accepting a first argument of type 'System.Collections.Generic.ICollection' could be found (are you missing a using directive or an assembly reference?)

...Not sure what I'm missing here?

Answer 1

Include() after the Where() statement:

var jobs = db.jobs.Where(d => d.docs.startdate >= date1 && d.docs.enddate <= date1)
             .Include(d => d.docs);
return View(jobs);

Answer 2

You should not have two lambdas within the Where(). So your query should look like this:

var jobs = db.jobs.Where(d => d.docs.startdate >= date1)
                  .Where(d => d.docs.enddate <= date1)
                  .Include(d => d.docs);
return View(jobs);

or both the Where's in one lambda (what you like more, both has the same result):

var jobs = db.jobs.Where(d => d.docs.startdate >= date1 && d.docs.enddate <= date1)
                  .Include(d => d.docs);
return View(jobs);

The Where() and the Include() does not mean the same. Where() filters your data, while Include() just eager load your data.