What ways exist with linq to compare if a case insensitive and culture using string exists within another

Question

Comparing case insensitive strings with linq is quite easy, even to find out if a specific string is within another. The problems only start when it is also needed (as in my case) to differentate between ss and ß. As far as I have seen linq and string offer only 1 viable option there: Contains but the problem is the only contains overload that takes for example: StringComparer.CurrentCultureIgnoreCase as parameter does not take string as parameter for the part that is searched within the "calling" string but instead takes ONLY a char value.

As it was asked: I'm using linq to gather info from a SQL database thus:

var results = (from c in myEntity.myTablename where 
(c.MyStringTextColumn.Contains(myStringTextToCompareWith)) 
select c.MyStringTextColumn).Distinct().ToList();

is how I'm comparing the strings originally but like I said the problem is this does not differentiate between ss and ß. Thus in the database there are different versions in regards to ss and ß stored, and I need to only find the "correct" one. Even though they are essentially the same one, an example would be strasse and straße (as names are stored in the database I couldn't use them in this example). If I type in strass I only want to find those strings that contain ss and not those with ß (thus straße should not be found)

So my question is: What options exist there?

Maybe a duplicate of http://stackoverflow.com/questions/444798/case-insensitive-containsstring/15464440#15464440? — dbc, Sep 10 '14 at 06:33
sadly not. I already tried the solutions there and linq says that it can't transform the indexof operation to something it knows. — Thomas, Sep 10 '14 at 06:38
I think OP is using linq to sql which doesn't support StringComparison usage — Yuliam Chandra, Sep 10 '14 at 06:42
@YuliamChandra that is correct. I updated the question there. Didn't know that there is a huge difference between linq to sql and other versions to linq (only used it for getting data from sql sources so far) — Thomas, Sep 10 '14 at 06:45
yup, they are different, most of .net methods are not supported for linq to sql / linq to entities, so if in the database there is `ss` and in application you find by `ß`, the expected result would be returning `ss`? — Yuliam Chandra, Sep 10 '14 at 06:53
@YuliamChandra no I want to find only the ß then if I search for that in the database (the problem is that ß and ss are treated as equal in default linq). I expanded the post a bit to better define what I need — Thomas, Sep 10 '14 at 06:58
You might be out of luck: http://msdn.microsoft.com/en-us/library/vstudio/bb882672%28v=vs.100%29.aspx - "Unsupported String methods in general: Culture-aware overloads" — dbc, Sep 10 '14 at 07:15
@dbc would it be possible then via extensions? (thus if I do my own extensions)? — Thomas, Sep 10 '14 at 07:26
or differently even though it is not optimized at all that way. Is it possible to use linqtodb to get all data reagardless of ss and ß and then to use "normal" linq on the generated list to differentate between ss and ß ? — Thomas, Sep 10 '14 at 07:35

score 0 · Answer 1 · answered Sep 10 '14 at 06:40

0

You should be able to make use of the IndexOf property, which returns a non-negative int if the substring is found (it returns the index of the first character if found, otherwise -1), and also takes a StringComparison argument.

http://msdn.microsoft.com/en-us/library/ms224425(v=vs.110).aspx

answered Sep 10 '14 at 06:40

Rufus L

36,127
5
30
43

You mean something like this?c.myStringText.IndexOf(myCompareToString, StringComparer.CurrentCultureIgnoreCase)) – Thomas Sep 10 '14 at 06:42

Yuliam Chandra · Answer 2 · 2014-09-10T07:33:51.203

Linq to sql doesn't support culture / case sensitive comparison, but it will rely on the database collation and the column type.

Queries do not account for SQL Server collations that might be in effect on the server, and therefore will provide culture-sensitive, case-insensitive comparisons by default. This behavior differs from the default, case-sensitive semantics of the .NET Framework. - MSDN

One option that you probably have is creating a stored procedure to do the searching like.

create proc sp_TableName
    (@name as varchar(max)) -- notice the type is varchar, not nvarchar
as
    select * from TableName
    where 
        cast(ColumnName as varchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS
        like '%' + @name + '%'

And using SqlQuery<T> to get the result.

var db = ...; // the context

var param = new SqlParameter("name", "ß");
var results = db.Database.SqlQuery<TableModel>("exec sp_TableName @name", param)
    .Distinct()
    .ToList();

Or you can just execute all the data first, so that you can do the comparing as linq to objects, not linq to sql.

var results = (from c in myEntity.myTablename.AsEnumerable() where 
    (c.MyStringTextColumn.Contains(myStringTextToCompareWith)) 
    select c.MyStringTextColumn).Distinct().ToList();

@Thomas, did you try this approach ? – Yuliam Chandra Sep 10 '14 at 10:41 — Yuliam Chandra, Sep 10 '14 at 10:41

What ways exist with linq to compare if a case insensitive and culture using string exists within another

2 Answers2