Entity Framework - NotSupportedException

var depts = ctx.Departments
            .OrderBy(d => d.deptName)
            .Select(d => d.deptNo);

foreach (int deptNumber in depts) {
    var deptReports = from d in ctx.Departments
                      join r in matchingIncidents on d.deptNo equals r.deptNo
                      where r.deptNo == deptNumber
                      select r;

    int deptReportsCount = deptReports.Count();

I am completely baffled! All the questions about this error say to use == on primitive fields (such as IDs), which I'm doing. Anything I do to th开发者_如何学Pythonis query generates the exception. The exact same code worked before and I don't know what I've done to it! Could someone please explain to me what's going on?

Also, I remember there being an EntityFramework class with methods that allowed you to convert objects within a query (e.g. dates), does anyone know what this class is?

UPDATE:

Here are the changes I made (it now works).

var deptReports = from r in matchingIncidents
                  join d in ctx.Departments on r.deptNo equals d.deptNo
                  where r.deptNo == deptNumber
                  select r;

matchingIncidents looks like a local collection of a complex type (because you are using r.deptNo). That's not allowed in LINQ to Entities. You could try this instead:

foreach (int deptNumber in depts) {
    var deptReports = from d in ctx.Departments
                      join r in matchingIncidents.Select(m => m.deptNo)
                          on d.deptNo equals r
                      where r == deptNumber
                      select r;

int deptReportsCount = deptReports.Count();

matchingIncidents.Select(m => m.deptNo) is now a local collection of primitive types and deptReports is a sequence of int (assuming that deptNo is of type int). But for counting the resulting elements it should be still fine.

Edit

And you are probably searching for the static EntityFunctions class:

http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.aspx

Could the issue be:

join r in matchingIncidents on d.deptNo == r.deptNo