Linq - get compare with transliterated value of fields

Question

Hello!

I have a table (sql server). One of its columns ('Name') Contains Cyrillic values.

From parameters i recieve value, that contains in this fields but the are transliterated.

i mean something like: 'Вася' -> 'Vasya'. And i need to transliterate 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

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

Answer 2

I resolved with 2 linq queries.

1. i created a new small class

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

And after i implemented next 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...

Answer 3

I've never seen "CTransliteration.Transliterate" - am I right in assuming that this comes from your own software/a 3rd party library?

The Linq query that you are targeting at the DataContext is transformed into SQL, and then executed on SQL Server. You wouldn't have a way to express the query that you want to run in SQL: CTransliteration.Transliterate is not part of the SQL language.

I think there are two ways you can go: a) Split the query into a part that runs on SQL Server, and do the transliteration in .NET. For the SELECT you've listed that's not a problem:

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

For the WHERE clause, this makes a poor choice as you'd need to transfer everything to the application server and then do the filtering.

b) Move the transliteration functionality into a CLR funtion in SQL Server, and run the query via SQL, not Linq.