Answers

I was thinking that the LINQ version and the foreach version were equivalent but I missed a key difference. In the LINQ version the filtering is happening at the SQL level (in a where clause) but in the foreach version, the filtering is happening in .NET.

But that still didn’t explain why the LINQ version didn’t work. I did some searching and found that when using Entity Framework and LINQ, since the filtering happens on the database side, the database defines how to do the string comparison and not the code.
So basically when I use StringComparison.OrdinalIgnoreCase in a LINQ query, it’s totally ignored and the string comparison being case sensitive or insensitive depends on how the database is defined (COLLATION option). [The reason your code worked must be because
in your case this COLLATION option ignores the case for string comparison. To validate this, try removing the StringComparison.OrdinalIgnoreCase and your code should still work].

This is exactly what was happening when EF generated a query for Effort, it didn’t pass in the special case insensitive comparison option and hence the comparison failed resulting into 0 rows.

If the above was true then removing the StringComparison.OrdinalIgnoreCase from the LINQ query and using ToUpper() should work so I tried that and sure enough the following worked:

Lilia Gong <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
Thanks
MSDN Community Support

Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

Please check whether the data is correct. And you can share the table structure and the regarding data so that we could do a test on it.

Regards.

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click
HERE to participate the survey.

I was thinking that the LINQ version and the foreach version were equivalent but I missed a key difference. In the LINQ version the filtering is happening at the SQL level (in a where clause) but in the foreach version, the filtering is happening in .NET.

But that still didn’t explain why the LINQ version didn’t work. I did some searching and found that when using Entity Framework and LINQ, since the filtering happens on the database side, the database defines how to do the string comparison and not the code.
So basically when I use StringComparison.OrdinalIgnoreCase in a LINQ query, it’s totally ignored and the string comparison being case sensitive or insensitive depends on how the database is defined (COLLATION option). [The reason your code worked must be because
in your case this COLLATION option ignores the case for string comparison. To validate this, try removing the StringComparison.OrdinalIgnoreCase and your code should still work].

This is exactly what was happening when EF generated a query for Effort, it didn’t pass in the special case insensitive comparison option and hence the comparison failed resulting into 0 rows.

If the above was true then removing the StringComparison.OrdinalIgnoreCase from the LINQ query and using ToUpper() should work so I tried that and sure enough the following worked:

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click
HERE to participate the survey.