-
Jan 18th, 2012, 04:05 PM
#1
Thread Starter
Frenzied Member
[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!
-
Jan 18th, 2012, 06:41 PM
#2
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"))
-
Jan 18th, 2012, 08:39 PM
#3
Thread Starter
Frenzied Member
Re: LINQ Accent Insensitive
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.
-
Jan 18th, 2012, 08:52 PM
#4
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jan 19th, 2012, 08:56 AM
#5
Thread Starter
Frenzied Member
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?
-
Jan 19th, 2012, 10:26 AM
#6
Thread Starter
Frenzied Member
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?
-
Jan 19th, 2012, 12:17 PM
#7
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
-
Jan 19th, 2012, 01:14 PM
#8
Thread Starter
Frenzied Member
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|