Tutorial :Linq - get compare with transliterated value of fields



Question:

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


Solution:1

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 function in SQL Server, and run the query via SQL, not Linq.


Solution:2

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


Solution:3

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...


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »