Dynamic Linq Query

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
Question
Sign in to vote
0
Sign in to vote
Hello Nicolas,
It is a very interesting question. After several hours research, I figured out the solution to accomplish your request by combining the two methods we talked about first, Dynamic LINQ Library and Expression Tree.
First Id like to introduce the documentation of the Dynamic LINQ Library to you. In Scotts blog, we know the library can be downloaded from VS 2008 Samples download page. The library is actually built from the project DynamicQuery under LINQ Samples folder. You can find the documentation named Dynamic Expressions API.htm under the folder ..\VB 2008 RTM Samples\Language Samples\LINQ Samples\DynamicQuery\Documentation\.
For the it operator, it represents the current instance. We can find the detailed information under the section Current Instance.
Then, let me explain my solution.
In my last post, I used VB.NET anonymous type to represent the sum of column A, column B and the group by key as column C. We can also use the Dynamic LINQ Library, because it supports create anonymous types as well, like .Select(New(CompanyName as Name, Phone)).
After creating the GroupBy query based on anonymous types, we cannot know the type name since it is dynamically created by VB.NET compiler. Under such condition, I figured out a way to manually create Expression Trees and embed the Expressions into the .Select method. For detailed information about how to embed the Expression, please see the section Dynamic Lambda Invocation of the documentation.
Here are my sample codes:
Create the table and make the dynamic Where, GroupBy, and Select query.
=====================================================================================
' Create the data table
Dim table As New DataTable
table.Columns.Add("A", GetType(Integer))
table.Columns.Add("B", GetType(Integer))
table.Columns.Add("C", GetType(DateTime))
table.Columns.Add("D", GetType(String))
table.Rows.Add(3, 2, New DateTime(2007, 1, 1), "StringA")
table.Rows.Add(6, 1, New DateTime(2007, 2, 1), "StringA")
table.Rows.Add(8, 4, New DateTime(2008, 1, 1), "StringA")
table.Rows.Add(5, 7, New DateTime(2007, 1, 1), "StringB")
'The dynamic Where query
Dim query = table.AsEnumerable.AsQueryable.Where("Convert.ToInt32(get_Item(@0)) > @1", "A", 2)
'The dynamic GroupBy query, the keySelector is an anonymous type {.C, .D}
Dim newQuery = query.GroupBy("New(Convert.ToDateTime(get_Item(@0)).Year as C, get_Item(@1) as D)", "it", "C", "D")
'Get the type of the IGrouping<,>
Dim groupByType = newQuery.ElementType
'The dynamic Select query using Dynamic Lambda Invocation
newQuery = newQuery.Select("New(@0(it) as A, @1(it) as B, it.Key.C as C, it.Key.D as D)", GetGroupByLamdaExpression(groupByType, "A"), GetGroupByLamdaExpression(groupByType, "B"))
Dim newTable As New DataTable
'Use the extension method to create a new data table
newQuery.ToDataTable(newTable)
DataGridView1.DataSource = newTable
=====================================================================================
Function to create the Lambda Expression. The return expression should be similar with this one:
Dim sumA As Expression(Of Func(Of IGrouping(Of Integer, DataRow), Integer)) = Function(g) g.Sum(Function(r As DataRow) r.Field(Of Integer)("A"))
=====================================================================================
Private Function GetGroupByLamdaExpression(ByVal type As Type, ByVal columnName As String) As LambdaExpression
Dim colParam As ConstantExpression = Expression.Constant(columnName, GetType(String))
Dim fieldMethod As MethodInfo = GetType(DataRowExtensions).GetMethod("Field", New Type() {GetType(DataRow), GetType(String)})
fieldMethod = fieldMethod.MakeGenericMethod(GetType(Integer))
Dim rowParam As ParameterExpression = Expression.Parameter(GetType(DataRow), "r")
Dim fieldMethodCall As MethodCallExpression = Expression.Call(fieldMethod, rowParam, colParam)
Dim columnExpression As Expression(Of Func(Of DataRow, Integer)) = Expression.Lambda(fieldMethodCall, rowParam)
Dim sumMethod As MethodInfo = GetType(Enumerable).GetMethods().Single(Function(m) m.Name = "Sum" And m.ReturnType.Equals(GetType(Integer)) And m.IsGenericMethod)
sumMethod = sumMethod.MakeGenericMethod(GetType(DataRow))
Dim groupParam As ParameterExpression = Expression.Parameter(type, "g")
Dim sumMethodCall As MethodCallExpression = Expression.Call(sumMethod, groupParam, columnExpression)
Dim sumALambda = Expression.Lambda(sumMethodCall, groupParam)
Return sumALambda
End Function
=====================================================================================
VB.NET Extension Method to create the data table via Reflection
=====================================================================================
Module MyExtensions
<Extension()> _
Public Sub ToDataTable(ByVal list As IQueryable, ByRef table As DataTable)
Dim properties = list.ElementType.GetProperties(Reflection.BindingFlags.Instance Or Reflection.BindingFlags.Public)
If table Is Nothing Then
table = New DataTable
End If
For Each p In properties
table.Columns.Add(p.Name, p.PropertyType)
Next
For Each l In list
Dim temp = l
table.Rows.Add(properties.Select(Function(p As PropertyInfo) p.GetValue(temp, Nothing)).ToArray())
Next
End Sub
End Module
=====================================================================================