Difference in Where clause and foreach look up in C# entity framework

Question

I have a piece of code:

var component = (from components in dataContext.Component select components)
                    .Where(x => x.Name.ToString().Equals(componentCode));

where Component is very simple object.

In mysql database table (encoded in utf8 - utf8_unicode_ci)

I have record such as: szkło 3mm (with polish character ł).

When I'm trying to find this record with the code above I get nothing.

Records without polish characters works fine.But when I'm doing it with foreach I get the result:

var component = (from components in dataContext.Component select components);
foreach(Component comp in component)
{
    if (comp.Name.ToString().Equals(componentCode))
        componentPrice = (decimal)comp.Price;
}

Is there a way to get the result with just a linq code without iterating through table.?

Any reason you are using `varchar` and not `nvarchar` for the `Name` column? — Ivan Stoev, Jun 22 '16 at 18:35
What is the datatype of Component.Name in your DB. Is it nvarchar? — Shai Aharoni, Jun 22 '16 at 18:36
Type of Name is varchar. And no, there is no reason to use varchar. — user3550149, Jun 22 '16 at 19:09
Interesting that you are having the opposite problem that most people have. The linq query is doing a sql compare (which is failing apparently), whereas the for loop is forcing the execution of the query into memory, then doing a .Net comparison. Usually the sql comparison is more forgiving because it is case insensitive by default. — Derpy, Jun 22 '16 at 19:21
the problem seems to be the encoding, I kind of doubt EF can calculate a query with a parameter containing such symbols. Try to set the encoding of your column to unicode (either via connection string, fluent API or Data annotation). — DevilSuichiro, Jun 22 '16 at 19:23
I tried changing varchar into nvarchar and still the same result — user3550149, Jun 22 '16 at 19:23

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

Ok, I have found solution - I have added "Charset=utf8" in the connection string and it works. Solution found in topic: Entity Framework C# Insert Data russian encoding problems

edited May 23 '17 at 12:24

Community

1
1

answered Jun 22 '16 at 19:41

user3550149

97
2
7

Difference in Where clause and foreach look up in C# entity framework

1 Answers1