How to reorder DataRow with Extension function, Anonymous types, Lambda Expression and Linq Special Row Comparer.
I had wrote some articles about expand Linq-to-SQL with own function Мої поширення Linq-to-SQL (in Ukrainian languages) and the same articles in English you may see on this page Five amazing Linq-to-SQL extension, and also I had wrote some articles about creation Extension function to Data.DataTable class - Змінні Nullable та як обробляти DBNull з бази за допомогою Extension-функції (in Ukrainian languages).In this page I continue a description how to expand Data.DataTable class with own extension.
Main problem of using Data.DataRowCollection is reordering your collection. I many times made similar task, but in this project this task is extremely clear. Let's look to screen below.
In this WebForm I need show two record from DB.
This is a same records inside in my program as row in DataTable.
This two records produced by huge existing BLL as simple Row in DataTable and I don't have opportunity to rebuild this huge BLL.
But, if customer click on screen in one of this records, this record should pop up on the top.
So, we should to reorder record of DataTable.
I resolve this problem by expand Microsoft DataTable class with my own extension function Resort.
Below we may show my function Resort, what demonstrate common pattern of reordering records in DataTable, but this is real code, applicable to my program, what contains many specific of my project.
1: Imports Microsoft.VisualBasic
2: Imports System.Data
3: Imports System.Linq
4:
5: Public Module Extension3
6: ''' <summary>
7: ''' Resort datatable for special criteria
8: ''' </summary>
9: ''' <typeparam name="T"></typeparam>
10: ''' <param name="dt">Source Datatable (usually random)</param>
11: ''' <param name="WithoutRow_SaveItForLater">Boolean</param>
12: ''' <param name="ClientNameToTop">Session("UserN")</param>
13: ''' <param name="BasketItemID_IsNothing">Boolean</param>
14: ''' <returns>Return reordered datatable</returns>
15: <Runtime.CompilerServices.Extension()> _
16: Public Function Resort(Of T As Data.DataTable)(dt As T, WithoutRow_SaveItForLater As Boolean, Optional ClientNameToTop As String = Nothing, Optional BasketItemID_IsNothing As Boolean = False) As Data.DataTable
17:
18:
19: Dim Y = (From X In dt.AsEnumerable() Select X).ToList
20: If WithoutRow_SaveItForLater Then
21: Y = (From X In dt.AsEnumerable() Select X Where X("SaveItForLater") Is DBNull.Value).ToList
22: End If
23:
24: If Y.Count = 0 Then
25: Return New Data.DataTable
26: Exit Function
27: End If
28:
29: Dim Z As List(Of Data.DataRow)
30: If ClientNameToTop Is Nothing Then
31:
32: If BasketItemID_IsNothing Then
33: Z = (From X In Y Select X Order By X("FirstName") & " " & X("LastName")).ToList
34: Else
35: 'add second order criteria
36: Z = (From X In Y Select X Order By X("FirstName") & " " & X("LastName"), X("BasketItemID") Descending).ToList
37: End If
38:
39: Else
40:
41: Dim SpecialRowComparer1 As New SpecialRowComparer(ClientNameToTop)
42:
43: 'insert in anonymous types Q runtime interfaces System.Linq.OrderedEnumerable
44: Dim Q = Y.OrderBy(Of String)(Function(X) X("FirstName") & " " & X("LastName"), SpecialRowComparer1)
45:
46: If Not BasketItemID_IsNothing Then
47: 'add second order criteria
48: Q = Y.OrderBy(Of String)(Function(X) X("FirstName") & " " & X("LastName"), SpecialRowComparer1).ThenByDescending(Of Integer)(Function(X) X("BasketItemID"))
49: End If
50:
51: 'download Data.DataRow from System.Linq.OrderedEnumerable to List(Of Data.DataRow)
52: Z = New List(Of Data.DataRow)
53: For Each One As Data.DataRow In Q
54: Z.Add(One)
55: Next
56:
57: End If
58:
59: If Z Is Nothing Then
60: Return New Data.DataTable
61: Else
62: Return Z.CopyToDataTable
63: End If
64:
65: End Function
66:
67: End Module
68:
69:
70: Public Class SpecialRowComparer
71: Implements IComparer(Of String)
72:
73: Dim _ClientNameToTop As String
74: Public Sub New(ClientNameToTop As String)
75: _ClientNameToTop = ClientNameToTop
76: End Sub
77:
78: Public Function Compare(x As String, y As String) As Integer Implements IComparer(Of String).Compare
79: If x = _ClientNameToTop And y <> _ClientNameToTop Then
80: Return -1
81: ElseIf x <> _ClientNameToTop And y = _ClientNameToTop Then
82: Return 1
83: ElseIf x = _ClientNameToTop And y = _ClientNameToTop Then
84: Return 0
85: ElseIf x <> _ClientNameToTop And y <> _ClientNameToTop Then
86: Return -x.CompareTo(y)
87: End If
88: End Function
89: End Class
This is not simple code, need small efforts to understand it. From record 70 you may see a record comparer, that comparing DataRow records in DataTable structure by criteria, needed for me.
But firstly lets look to function definition in string 16. You may see this is Generic function for any class T as DataTable (in my application expanding class named RandomdtBasketList). For any Extension function (with attributes '<Runtime.CompilerServices.Extension()>') first parameters "dt As T" means class to expand.
In the beginning I transform DataTable to DataRow collection:
First branch from line 30 to 40 is very simple, this is only simple reordering this rows by classic Linq syntax. But line 44 is more interesting. This rows reordering by Lambda Expression, System.Linq.OrderedEnumerable and special Row Comparer. Result is stored in anonymous types Q. And before finist I assembly rows from anonymous type Q to DataRow.
Thats it! But I have many-many interesting Extension function (for example ConvertDataTableToString - simple function to debugging) and I will continue to publish their.
