LINQ ; Search with culture invariant

Question

Here is my problem. We've got a list of enterprises, users or anything and we must search into it with a "StartsWith" search type. So on our site, we ain't got a Search field such as a Textbox but we've got a search header including 27 buttons "#", "A", "B", "C", [...] "Z".

The problem we have is that if user click on "E" button, when we querying to get value from database, entreprises name may start with an "É", "È", "Ê", bacause yes, our site is in french. Any idea of how to do it in LINQ.

This is important to know too that we're using LLBLGen Pro. So I guess it need to have something he can convert into a valid SQL Query.

Here is what we've already tryied :

IList<Enterprise> enterprises;
switch (searchChar){
  [...]
  case "E":
     enterprises = from ent in ourContext.Enterprises
                   where "eèéêë".Any(param => ent.name[0] == param)
                   select ent;
     break;
  [...]
}

Which give us this error something relatively to a unconvertable query:
Unable to cast object of type 'System.Linq.Expressions.ConstantExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression'.

So we've tried to make it basicaly with a simple LINQ query, without querying to DB to know if it's possible.

IList<string> test = new List<string>() { "École", "enlever", "avoir" };
IList<string> result = (from value in test
                        where "eéèêë".Contains(value[0].ToString())
                        select value).ToList();

What is weird with this query, is that it ain't crash. But, it ain't work too! When debugging, it go throught it, but when we try to see what is into "result" list, it's like if there's nothing in it. I mean, the list is simply null. But nothing fail into the try catch!

Please help !

score 1 · Answer 1 · answered Mar 28 '11 at 14:12

1

The real solution is here is to create an extra column in your database for a searchable name, so whenever you add a new company you also add a value to the searchable name column. You would convert all characters to upper (or lower if you like) invariant and add the "clean" name to the searchable name column. You could also remove punctuation at this point too, as that is often a pain in searches.

This will leave you with a column you will never display but it will be much easier (and also much quicker) to search for matches in this column as you will never need to worry about accented characters at search time.

answered Mar 28 '11 at 14:12

Steve

8,469
1
26
37

Never thought of that but this is a good idea. Because it's a system with already existing data in it, we can't do this. But in futur, this would be considerate. – Simon Dugré Mar 28 '11 at 14:15
You can create additional table with search string and ForignKey to that source table. – Viacheslav Smityukh Mar 28 '11 at 14:20

score 0 · Answer 2 · answered Mar 28 '11 at 13:52

0

Just use StartsWith method of the string

IList<string> test = new List<string>() { "École", "enlever", "avoir" };

var result = test
    .Where(s => s.StartsWith("e", StringComparison.CurrentCultureIgnoreCase))
    .ToList();

answered Mar 28 '11 at 13:52

Viacheslav Smityukh

5,652
4
24
42

Yea, I've tryied this with the simple LINQ query. But when we do it with the first query (with LLBLGen) it can't convert it into a valid SQL Query! – Simon Dugré Mar 28 '11 at 13:54
@Simon You can bipass LLBL and create the query yourself. For some reason LLBL seems to have made that ridiculously difficult to do in their framework. – Yuriy Faktorovich Mar 28 '11 at 13:56
@Yuriy Faktorovich Well, but how exactly? It begins to give me headaches – Simon Dugré Mar 28 '11 at 13:59
Is your current locale is Franch? – Viacheslav Smityukh Mar 28 '11 at 14:01
Can you see result SQL query? – Viacheslav Smityukh Mar 28 '11 at 14:03
My computer is in "en-CA" and database is "SQL_Latin1_General_CP1_CI_AS" – Simon Dugré Mar 28 '11 at 14:04
I'm thinking you should have FR locale to perform InvariantSearch – Viacheslav Smityukh Mar 28 '11 at 14:07
Got it. Take a look soon to my response of this question – Simon Dugré Mar 28 '11 at 14:07
You can add additional field "firstInvariantLetter" to the data base and use it to perform search, it will increase search performance. You will convert letter onece on record update. – Viacheslav Smityukh Mar 28 '11 at 14:09

UserControl · Answer 3 · 2011-03-28T14:03:04.263

0

If i got you right here is what you want:

var result = test.Where(x => "eéèêë".Contains(Char.ToLowerInvariant(x.FirstOrDefault())));

edited Mar 28 '11 at 14:03

answered Mar 28 '11 at 13:56

UserControl

14,766
20
100
187

Isn't it simply to do : "eéèêë".Contains(value[0].ToString()) ? – Simon Dugré Mar 28 '11 at 13:58

score 0 · Accepted Answer · answered Mar 28 '11 at 14:13

0

The Any seem to be not working. Use Contains instead. This is workin.

enterprises = from ent in ourContext.Enterprises
              where "eèéêëEÈÉÊËE".Contains(ent.name[0])
              select ent;

answered Mar 28 '11 at 14:13

Simon Dugré

17,980
11
57
73

score 0 · Answer 5 · answered Mar 28 '11 at 14:14

I'm not sure whether you have any control over the database, and which RDMBS you are using, but an easy way seems to be using a case insensitive, accent insensitive collation in your query - this way SQL does the hard work.

-- Assuming that your existing table is Accent Sensitive
create table Enterprises
(
name nvarchar(200) collate SQL_Latin1_General_CP1_CI_AS
)
GO

-- We can still use an Accent Insensitive View
create view vEnterprises
as
    select name collate SQL_Latin1_General_CP1_CI_AI AS name
        from Enterprises

GO

insert into Enterprises values ('e')
insert into Enterprises values ('è')
insert into Enterprises values ('é')
insert into Enterprises values ('ê')
insert into Enterprises values ('ë')

insert into Enterprises values ('E')
insert into Enterprises values ('É')
insert into Enterprises values ('È')
insert into Enterprises values ('Ê')


-- returns 'e' and 'E'
select * from Enterprises where name like 'e%'

-- returns everything
select * from vEnterprises where name like 'e%'

i.e. Assuming that you can add the accent insensitive view to LLBLGen you can just pass 'e' to the query.

Thank you nonnb. We don't have full control over our database. So I'm not sure we can't do this stuff. At the same time as you post this answer, I've answered too and found out a way to do it. But this will be considerate if needed further on another project. — Simon Dugré, Mar 28 '11 at 14:19

LINQ ; Search with culture invariant

5 Answers5