CODEWITHSUNDEEP
sql-serverlinq-to-entitiescollation
John Miner
John Miner

Reputation: 893

SQL to Entities - cannot make 'String.Contains' return 'case-INSENSITIVE' results

I have a C# application that performs 'LINQ to Entities' queries.

The database it is connected to is MS SQL 2012. The database collation is 'Hebrew CI'.

And still - if I perform a query like:

return users.Where(us => us.FirstName.Contains("joh"));

it does not fetch the records with 'FirstName' that has 'John'. Only the ones with first name 'john'.

I thought that this is determined on the SQL Server-side by the database collation, but obviously I am wrong ...

Also - I have checked the 'FirstName' column's collation, and it is set to 'Database Default'.

What am I missing here ?

.

[ UPDATE ]

After reading some comments - I need to clarify :

I am using LINQ-TO-ENTITIES, not a local query.

The full piece of code is :

public List<User>   GetUsersByName(IMyDBEntities context, String filterBy)
{
    IEnumerable<User> users = context.Users;

    return users.Where(us => us.FirstName.Contains("filterBy"));  // filterBy = 'joh'
}

The full function contains more filters (filter by age, filter by address, etc...)

which all happen on the 'server' list of users, and only when I do '.ToList()' does the query actually fire with all the filters.

Upvotes: 1

Views: 374

Answers (3)

usr
usr

Reputation: 171178

Likely, you are executing this Where call on a local collection, meaning you get LINQ to Objects semantics. You need to move it to a database query.

Seeing the updated code the problem is apparent:

IEnumerable<User>

By declaring the sequence variable to be a local sequence, not a query, you make the compiler use local query operators, not remote query operators.

The compile-time type must be IQueryable<User>.

Upvotes: 1

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

If this linq is mapped to sql, it will use sql server settings...but in your case, users is already a list of objects (otherwise your string compare would be database server dependent), so you can do something like this: 

return users.Where(us => us.FirstName.Contains("joh", StringComparer.OrdinalIgnoreCase));

Upvotes: 0

Oded
Oded

Reputation: 498904

Here is one way to achieve this:

return users.Where(us => us.FirstName.ToLower().Contains("joh"));

Upvotes: 0

Related Questions