We help IT Professionals succeed at work.

Linq Datasource and accent insensitive select

arthrex
arthrex asked
on
Hi experts,

in my asp.net application I have a gridview with a LinqDataSource as its source. I have a function that filters the datasource with the following command:
That works fine.
But what do I have to change to allow for accent insensitive filters?
I read about the collation SQL_Latin1_General_CP1_CI_AI  and I set my Database to this collation.
But now I need to get the COLLATE statement somehow into the query.

Who can help?
Thanks a lot
myDataSource.WhereParameters.Add(key, dbType, value);
myDataSource.Where =  key + ".Contains(@" + key + ")";

Open in new window

Comment
Watch Question

How about using the ToLower() function instead? ie. call ToLower() on the server key, and your provided key

Author

Commented:
I thought that only helps for CASE insensitive searches. I'm talking about ACCENT insensitive searches (i.e. José and Jose)
Sorry my bad :P

In short you cannot change the collation used by Linq (Linq leaves collation decisions to the database) Check out this link...

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=299305&wa=wsignin1.0
In your query you can set the collation like

select
  *
from
  people
where
  name COLLATE SQL_Latin1_General_CP1_CI_AI like '&Your name&'

Author

Commented:
Thanks waaromikniet,
but that's SQL. I use the LinqDataSourceObject. I need a statement for that
'&Your name&'

Has to be

'%Your name%'

I don't think this is possible.

Author

Commented:
Hmm..so there's no workaround?
The thing is, I have to use the LinqDataSource, otherwise I have to refactor my whole project.

@deadlydev: You say LINQ leaves the collation decision to the database... My DB has the collation SQL_Latin1_General_CP1_CI_AI. But how does that help me now?
Your collaction means that when you have data in a field in your table with an accent on a letter and you query with that letter without accent the records still will be returned
AI means accent insensitive (not matter)
One remark. When you change the database collation all text fields keep their original collation setting that was used during creation of the database. You have to create a script to also change all text fields in all of your tables. Or recreate your database with new collation setting

Author

Commented:
Ahh... OK - that makes sense.
But to make one point clear: When I change the collation of the DB it won't affect the actual content of my Field values (i.e. josé will stay josé not jose)
Yes thats correct. changing collation doesn't affect value (99% sure). The only effect is that
with accent sensitve  josé is not equal to jose
with accent insensitive josé is equal to jose
Be sure to create a backup before changing collation of columns.
If you are using excel 2007+ check out

http://www.microsoft.com/downloads/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en

I have been working with the open xml sdk for the past few weeks, and have prefered it tremendously to working with the VSTO.