Unicode Collations problem ?

Question

(.NET 3.5 SP1, VS 2008, VB.NET, MSSQL Server 2008)

I'm writing a small web app to test the Khmer Unicode and Lao Unicode. I have a table that store text in Khmer Unicode with the following structure :

[t_id] [int] IDENTITY(1,1) NOT NULL
[t_chid] [int] NOT NULL
[t_vn] [int] NOT NULL
[t_v] [nvarchar](max) NOT NULL

I can use Linq to SQL to do CRUD normally. The text display properly on the web page, even though I didn't change the default collation of MSSQL Server 2008.

When it comes to search the column [t_v], the page will take a very long time to load and in fact, it loads every row of that column. It never compares with the "key word" criteria that I use for the search. Here's my query for the search :

Public Shared Function SearchTestingKhmerTable(ByVal keyword As String) As DataTable

    Dim db As New BibleDataClassesDataContext()
    Dim query = From b In db.khmer_books _
                From ch In db.khmer_chapters _
                From v In db.testing_khmers _
                Where v.t_v.Contains(keyword) And ch.kh_book_id = b.kh_b_id And v.t_chid = ch.kh_ch_id _
                Select b.kh_b_id, b.kh_b_title, ch.kh_ch_id, ch.kh_ch_number, v.t_id, v.t_vn, v.t_v


    Dim dtDataTableOne = New DataTable("dtOne")
    dtDataTableOne.Columns.Add("bid", GetType(Integer))
    dtDataTableOne.Columns.Add("btitle", GetType(String))
    dtDataTableOne.Columns.Add("chid", GetType(Integer))
    dtDataTableOne.Columns.Add("chn", GetType(Integer))
    dtDataTableOne.Columns.Add("vid", GetType(Integer))
    dtDataTableOne.Columns.Add("vn", GetType(Integer))
    dtDataTableOne.Columns.Add("verse", GetType(String))

    For Each r In query
        dtDataTableOne.Rows.Add(New Object() {r.kh_b_id, r.kh_b_title, r.kh_ch_id, r.kh_ch_number, r.t_id, r.t_vn, r.t_v})
    Next
    Return dtDataTableOne


End Function

Please note that I use the exact same code and database design with Lao Unicode and it works just fine. I get the returned query as expected for the search.

I can't figure out what the problem with searching for query in Khmer table.

Answer 1

I was being slow; the problem is (I think) the joins; do you have navigation properties? You should be able to use something like

         from book in db.Books
         from chapter in book.Chapters
         from verse in chapter.Verses

etc - or (worst case) use the LINQ join syntax. I think that will work significantly better than the composite WHERE; like so:

    From b In db.khmer_books
    Join ch In db.khmer_chapters On b.kh_b_id Equals ch.kh_book_id Into ch
    Join v In db.testing_khmers On ch.kh_ch_id Equals v.t_chid Into v
    Where v.t_v.Contains(keyword)
   {your select}

---

original:

The first thing to do is to get the generated query; you can assign db.Log = Console.Out before running the query, which will push the TSQL to the console (or you can use any other TextWriter). I'd be very interested to see what it is generating...

You can then test this TSQL at the database (management studio) to see what it is getting so horribly wrong.

At worst case, you could write a UDF to do the search (with fixed TSQL), and call that from LINQ-to-SQL.

Can you post the TSQL please?

Answer 2

Here's the TSQL that I got from SqlServerQueryVisualizer :

SELECT [t0].[kh_b_id], [t0].[kh_b_title], [t1].[kh_ch_id], [t1].[kh_ch_number], [t2].[t_id], [t2].[t_vn], [t2].[t_v] FROM [dbo].[khmer_book] AS [t0], [dbo].[khmer_chapter] AS [t1], [dbo].[testing_khmer] AS [t2] WHERE ([t2].[t_v] LIKE '%ដាវីឌ%') AND ([t1].[kh_book_id] = [t0].[kh_b_id]) AND ([t2].[t_chid] = [t1].[kh_ch_id])

I run this in managment studio and I didn't get anything even though there are ដាវីឌ in the table.

Answer 3

I used the following Linq to join the table :

Dim query = From b In db.khmer_books _
    Join ch In db.khmer_chapters On b.kh_b_id Equals ch.kh_book_id _
    Join v In db.testing_khmers On ch.kh_ch_id Equals v.t_chid _
    Where v.t_v.Contains(keyword) _
    Select b.kh_b_id, b.kh_b_title, ch.kh_ch_id, ch.kh_ch_number, v.t_id, v.t_vn, v.t_v

But I still got all the rows from that table. Any idea ? Thanks a lot.

Answer 4

I've got this probelm too , I know in sqlserver 2005 it does not support for Unicode Khmer becuase I've query [ SELECT Alias, * FROM sys.syslanguages ] in sqlserver it does not include Khmer or Catalan language, so first i think we should enable sqlserver with Catalan language , than we should perform query by using fulltext search and specify language in parameter of (contains or freetext). i've also visit microsoft.com but i didn't found this language package. any have any idea to help!