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.

Reed Copsey · Accepted Answer · 2013-07-15 17:34:09Z

8

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.

answered Jul 15, 2013 at 17:34

Reed Copsey

567k80 gold badges1.2k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

evanmcdonnal · Accepted Answer · 2013-07-15 18:03:38Z

5

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.

edited Jul 15, 2013 at 18:03

answered Jul 15, 2013 at 17:35

evanmcdonnal

48.5k17 gold badges111 silver badges119 bronze badges

2 Comments

Legion Over a year ago

Normally I would. However in this case I'm indexing 13 different arrays in the for loop.

evanmcdonnal Over a year ago

@Legion that's unfortunate. I don't think there are any options better than assigning to local variables.

Vladimir Gondarev · Accepted Answer · 2013-07-15 18:17:09Z

0

Why wouldn't you write something like that:

var query = from d in db.Departments
                from t in teams
                where d.DepartmentID == t.DepartmentID && d.Team == t.TeamName
                select d;

This can be easily converted to Expression tree and finally to SQL.

answered Jul 15, 2013 at 18:17

Vladimir Gondarev

1,2537 silver badges14 bronze badges

Collectives™ on Stack Overflow

LINQ query with array element in Where clause

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related