LINQ query with array element in Where clause

Question

I've got numerous LINQ queries inside of for loops that look like this:

Department department = db.Departments.Single(d => d.DepartmentID == teams[i].DepartmentID);

The problem is LINQ doesn't like to compare against an array element and throws the exception LINQ to Entities does not recognize the method 'get_Item(Int32)'. Is there a nicer way to get around this besides declaring local variables for each of the properties in the teams list I want to select against? I'd like to avoid filling my for loops up with things like

int departmentID = teams[i].DepartmentID;
string teamName = teams[i].TeamName;

etc.

Answer 1

I'd like to avoid filling my for loops up with things like...

Unfortunately, that is typically the best option. The expression evaluation needs to be able to convert the expression tree into SQL, and it doesn't know how to handle array elements. Making temporary variables is the simplest, most maintainable way to handle this scenario.

Answer 2

I don't know that this will fix your problem but you could try changing your for loop to a for each loop so that you have a reference to the current object rather than accessing it by index. The problem is that SQL Server has no notion of what an array is so when you try to use that valid C# syntax, it doesn't know how to translate it into an expression tree to produce the necessary SQL. If the for each suggestion doesn't work then I think you're stuck doing it how you are now.