Results 1 to 8 of 8

Thread: [RESOLVED] LINQ Accent Insensitive

  1. #1

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Resolved [RESOLVED] LINQ Accent Insensitive

    Hi there

    Is there a way to use LINQ to filter a DataTable for rows that contain particular character but in a "Accent Insensitive" way?
    Search over the web I came with something like this:


    Code:
            Dim dt As DataTable = CType(BindingSource1.DataSource, DataTable)
            Dim SearchVAlue As String
            SearchVAlue = "e"
            Dim Result = From Src In dt.AsEnumerable()
                          Where Src.Field(Of String)("Rue").IndexOf(SearchVAlue, StringComparison.InvariantCultureIgnoreCase) > -1 _
                          Select Src
    
            Dim dv As DataView
            dv = Result.AsDataView
            BindingSource1.DataSource = dv
    Code:
    In a table that contain this
    ID    Rue
    1     "Carré"
    2     "Carre"
    3     "Triangle"
    4     "Bag"
    5     "Laptop"
    But it is not working in the way I wish, the above method return me only those with "e" not those with "é".

    Is there a way to get LINQ return me
    Code:
    1     "Carré"
    2     "Carre"
    3     "Triangle"
    Many thank!
    in advance!
    Using VS 2010 on Fw4.0

  2. #2
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    Re: LINQ Accent Insensitive

    Try this:
    Code:
    Dim r = dt.AsEnumerable() _
              .Where(Function(row) _
                  New String(row.Field(Of String)("Rue") _
                                .Normalize(NormalizationForm.FormD) _
                                    .Where(Function(c) CharUnicodeInfo.GetUnicodeCategory(c) <> _
                                                       UnicodeCategory.NonSpacingMark) _
                                    .ToArray()) _
                                .Contains("e"))

  3. #3

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: LINQ Accent Insensitive

    Quote Originally Posted by ForumAccount View Post
    Try this:
    Code:
    Dim r = dt.AsEnumerable() _
              .Where(Function(row) _
                  New String(row.Field(Of String)("Rue") _
                                .Normalize(NormalizationForm.FormD) _
                                    .Where(Function(c) CharUnicodeInfo.GetUnicodeCategory(c) <> _
                                                       UnicodeCategory.NonSpacingMark) _
                                    .ToArray()) _
                                .Contains("e"))

    Thanks for your reply.
    I'm most new with LINQ, and I don't understand where the "row" variable came from.
    Using VS 2010 on Fw4.0

  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: LINQ Accent Insensitive

    it's passed as a parameter in the lambda function.

    Code:
    Dim r = dt.AsEnumerable() _
              .Where(Function(row) _
                  New String(row.Field(Of String)("Rue") _
                                .Normalize(NormalizationForm.FormD) _
                                    .Where(Function(c) CharUnicodeInfo.GetUnicodeCategory(c) <> _
                                                       UnicodeCategory.NonSpacingMark) _
                                    .ToArray()) _
                                .Contains("e"))
    see the explanation on msdn:

    Converting to a Delegate Type

  5. #5

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: LINQ Accent Insensitive

    Finally I got something

    Code:
            Dim Result = From Src In dt.AsEnumerable()
                  Where UTF8.GetString((GetEncoding("ISO-8859-8").GetBytes(Src.Field(Of String)("Rue")))).IndexOf(SearchVAlue, StringComparison.InvariantCultureIgnoreCase) > -1 _
                  Select Src
    This expression remove every accent from the field "Rue" and search the DataTable for any occurrence of [Searchvalue]

    The next challenge is this:
    From the expression above, I search the Field "Rue" but what I'll like to get the searched fields form a Collection list. Like "Rue", "Name", "Field1", "Field2", ...
    Any idea?
    Using VS 2010 on Fw4.0

  6. #6

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: LINQ Accent Insensitive

    IN other word how to build Dymanic LINQ expresion that will do something like

    Code:
    For Each sFieldName As String In FieldsList
           Dim Result = From Src In dt.AsEnumerable()
                  Where OR UTF8.GetString((GetEncoding("ISO-8859-8").GetBytes(Src.Field(Of String)(sFieldName )))).IndexOf(SearchVAlue, StringComparison.InvariantCultureIgnoreCase) > -1 _
                  Select Src
    Next
    Need to find way to search an unknow list of field with OR

    In a SQL query I could do this:
    Code:
           For Each sField As String In Me.mSearchableField
                sSQL &= sField & " LIKE '*" & Tools.QuoteString(pValue) & "*' OR "
           Next sField
    
           If sSQL.Trim.EndsWith("OR") Then
                sSQL = sSQL.Substring(0, sSQL.Length - 3) ' remove last "OR" 
           End If
    But what with LINQ?
    Using VS 2010 on Fw4.0

  7. #7
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    Re: LINQ Accent Insensitive

    Going along what I posted earlier:
    Code:
    Imports System.Globalization
    Imports System.Text
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, _
                                  e As System.EventArgs) Handles Button1.Click
    
            Dim dt = New DataTable()
            dt.Columns.Add("Id", GetType(Integer))
            dt.Columns.Add("Rue", GetType(String))
            dt.Columns.Add("Details", GetType(String))
    
            dt.Rows.Add(1, "Carré", "Friend")
            dt.Rows.Add(2, "Carre", Nothing)
            dt.Rows.Add(3, "Triangle", DBNull.Value)
            dt.Rows.Add(4, "Bag", "Something")
            dt.Rows.Add(5, "Laptop", "My personal laptop")
    
            Dim searchText = "e"
            Dim r = From row In dt.AsEnumerable() _
                    Where Me.Evaluate(row, searchText)
    
            For Each row In r
                Console.WriteLine(row("Rue"))
            Next
    
        End Sub
    
    
        Private Function Evaluate(row As DataRow, _
                                  searchText As String) As Boolean
    
            '//change to anything
            Dim Searchable() = New String() {"Rue", "Details"}
    
            For Each column As DataColumn In row.Table.Columns
                If Array.IndexOf(Searchable, column.ColumnName) > -1 Then
                    Dim value = TryCast(row.Item(column.ColumnName), String)
                    If value IsNot Nothing Then
                        Dim normalized = Me.GetAccentless(value)
    
                        If normalized.Contains(searchText) Then
                            Return True
                        End If
                    End If
                End If
            Next
    
            Return False
        End Function
    
        Private Function GetAccentless(value As String) As String
            Return New String(value.Normalize(NormalizationForm.FormD) _
                                   .Where(Function(c) CharUnicodeInfo.GetUnicodeCategory(c) <> _
                                                      UnicodeCategory.NonSpacingMark).ToArray())
    
        End Function
    
    End Class
    Last edited by ForumAccount; Jan 19th, 2012 at 12:22 PM. Reason: Revised

  8. #8

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: LINQ Accent Insensitive

    Oh Great!

    Thanks for your help!

    I've change the GetAccentless function a bit for this
    Code:
        Private Function GetAccentless(value As String) As String
    
            Return UTF8.GetString((GetEncoding("ISO-8859-8").GetBytes(value)))
    
       
        End Function
    But it is working exactly as expected.
    Thanks again for your help, it is appreciated
    Using VS 2010 on Fw4.0

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width