Linq - get comparison with transliterated field value

Question

I have a table (sql server). One of its columns ("Name") contains Cyrillic meanings.

From the parameters I get the value that is contained in these fields, but transliterated.

I mean something like: "Vasya" → "Vasya". And I need to transliterate the value field first;

var q = from r in m_DataContext.Table where CTransliteration.Transliterate(r.Name).Contains(trans_text) select r; 

or

 m_DataContext.Table.Select(r => r.name=CTransliteration.Transliterate(r.Name)) 

Answer 1

I have never seen CTransliteration.Transliterate - do I correctly assume this comes from your own software / third-party library?

The Linq query that you target on the DataContext is converted to SQL and then executed on SQL Server. You wouldn’t have a way to express the query you want to run in SQL: CTransliteration.Transliterate is not part of the SQL language.

I think you can go in two ways: a) Divide the query into the part that runs on SQL Server and transliterate into .NET. For SELECT, you indicated that this is not a problem:

 m_DataContext.Table .ToList() .Select(r => r.name=CTransliteration.Transliterate(r.Name)); 

For the WHERE clause, this makes a bad choice, since you will need to transfer everything to the application server and then filter.

b) Move the transliteration functionality to the CLR function in SQL Server and run the query through SQL, not Linq.

Answer 2

Can you find more luck with string.Compare(r.Name, "Your Value", CultureInfo.xxx, CompareOptions.xxx) ?

Answer 3

I resolved with 2 linq queries.

• i created a new small class

  public class SmallClass {public int Id {get; set; } public String Name {get; set; }}

And after I performed the following logic:

...

 var q = from r in GetAllTable() select new SmallClass{ Id = r.ID, Name = CTransliteration.Transliterae(r.Name)) }; var rr = from r in q where r.Name.Contains(trans_text) select r; 

i't 'working ...