[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!

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

---

Quote:

---

Originally Posted by ForumAccount

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.

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

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?

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?
:afrog:

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

---

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
:thumb: