LINQ ; Search with culture invariant

Here is my problem. We've got a list of enterprises, users or anything and we must search into it with a "StartsWith" search typ开发者_Go百科e. 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 !

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.

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();

If i got you right here is what you want:

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

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;

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.