VB.NET LINQ to DataSet (SQL 'LEFT OUTER JOIN' alternative)

Question

I would like to join two DataTables and create the third one from the result. The result DataTable should have three columns:

ID
Name
YearOfBirth

My Compile options:

Option explicit: On
Option strict: On
Option compare: Binary

Option infer: Off

Dim dr As DataRow

REM Dt1
Dim Dt1 As New DataTable
Dt1.Columns.Add("ID", GetType(Integer))
Dt1.Columns.Add("Name", GetType(String))

dr = Dt1.NewRow
dr("ID") = 1
dr("Name") = "Peter"
Dt1.Rows.Add(dr)

dr = Dt1.NewRow
dr("ID") = 2
dr("Name") = "Anna"
Dt1.Rows.Add(dr)

dr = Dt1.NewRow
dr("ID") = 3
dr("Name") = "John"
Dt1.Rows.Add(dr)
REM End Dt1

REM Dt2
Dim Dt2 As New DataTable
Dt2.Columns.Add("ID", GetType(Integer))
Dt2.Columns.Add("YearOfBirth", GetType(Integer))

dr = Dt2.NewRow
dr("ID") = 1
dr("YearOfBirth") = 1970
Dt2.Rows.Add(dr)

dr = Dt2.NewRow
dr("ID") = 2
dr("YearOfBirth") = 1980
Dt2.Rows.Add(dr)
REM End Dt2

Dim Dt3 As New DataTable

Dim query As IEnumerable(Of DataRow) = From dr1 In Dt1.AsEnumerable()
                                       Group Join dr2 In Dt2.AsEnumerable()
                                       On dr1.Field(Of Integer)("ID") Equals dr2.Field(Of Integer)("ID")
                                       Into joined = Group
                                       From j In joined.DefaultIfEmpty()
                                       Select New With
                                           {
                                           .ID = dr1.Field(Of Integer)("ID"),
                                           .Name = dr1.Field(Of String)("Name"),
                                           .YearOfBirth = j.Field(Of Integer)("YearOfBirth")
                                           }


Dt3 = query.CopyToDataTable

But I get the error message in editor (VS 2017):

"Error BC36754: 'IEnumerable(Of anonymous type: ID As Integer, Name As String, YearOfBirth As Integer)' cannot be converted to 'IEnumerable(Of DataRow)' because 'anonymous type: ID As Integer, Name As String, YearOfBirth As Integer' is not derived from 'DataRow', as required for the 'Out' generic parameter 'T' in 'Interface IEnumerable(Of Out T)'."

You can't call `CopyToDataTable` unless your list contains `DataRows` which yours doesn't. You need to create the `DataTable` yourself and then loop through the results of your query, adding a `DataRow` for each item. — jmcilhinney, May 12 '18 at 08:27
If somebody could write correct part of code (Dim query...) for me, it will be very helpful for me. — skdan, May 12 '18 at 08:46

Tetsuya Yamamoto · Accepted Answer · 2018-05-15T05:42:05.327

Select New without specifying class name as query result will return anonymous type (such like IEnumerable(Of AnonymousType)), and CopyToDataTable() throwing exception because IEnumerable(Of AnonymousType) cannot be converted directly to IEnumerable(Of DataRow).

Hence, you need to convert anonymous type into DataRow using additional Select method that iterates IEnumerable(Of AnonymousType) contents and returns DataRow with DataTable.NewRow() (using prepared DataTable which includes column names as result set):

' joined DataTable columns
Dim JoinedDT As New DataTable
JoinedDT.Columns.Add("ID", GetType(Integer))
JoinedDT.Columns.Add("Name", GetType(String))
JoinedDT.Columns.Add("YearOfBirth", GetType(Integer))

' other stuff

Dim query As IEnumerable(Of DataRow) = (From dr1 In Dt1.AsEnumerable() _
                                       Group Join dr2 In Dt2.AsEnumerable() _
                                       On dr1.Field(Of Integer)("ID") Equals dr2.Field(Of Integer)("ID") _
                                       Into joined = Group _
                                       From j In joined.DefaultIfEmpty() _
                                       Select New With
                                       {
                                           .ID = dr1.Field(Of Integer)("ID"),
                                           .Name = dr1.Field(Of String)("Name"),
                                           .YearOfBirth = If(j IsNot Nothing, j.Field(Of Integer)("YearOfBirth"), 0)
                                       }).Select(Function(r)
                                           ' use `DataTable.NewRow` here
                                           Dim row As DataRow = JoinedDT.NewRow()
                                           row("ID") = r.ID
                                           row("Name") = r.Name
                                           row("YearOfBirth") = r.YearOfBirth
                                           Return row
                                       End Function)

Dt3 = query.CopyToDataTable()

Reference:

Get linq to return IEnumerable<DataRow> result (C# version)

Your code is OK, but only under condition, that both DataTables Dt1 and Dt2 have 3 rows with ID 1,2 and 3. If the DataTable Dt2 has only two rows with ID 1 and 2 (without ID 3), I get the error: "System.ArgumentNullException: 'Value cannot be null. Parameter name: row'". — skdan, May 14 '18 at 17:11
I just found that `YearOfBirth` only contains 2 rows (for ID 3 it's set to `Nothing`). Use [`If` operator](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/if-operator) with 2 arguments to check against `Nothing` and return default integer value if `DataRow` has `Nothing` value in it. — Tetsuya Yamamoto, May 15 '18 at 01:19
If I use `row("YearOfBirth") = If(r.YearOfBirth, 0)`, the query is not correct. I see this error in Error List: Error BC33107: First operand in a binary 'If' expression must be nullable or a reference type. If I use in the part of code `.YearOfBirth = j.Field(Of Integer)("YearOfBirth")` `Object`, instead of `Integer`, the query is formally OK, but I get the error after launch: "System.ArgumentNullException: 'Value cannot be null. Parameter name: row". If I use `Try...Catch`, I see that the error is on the line `Select New With`. — skdan, May 15 '18 at 05:25
Well, I had put check against `Nothing` in wrong place. It should be placed in `Select New With` as you're said, also confirmed by [this issue](https://stackoverflow.com/questions/17201168/left-outer-join-linq-to-datatable). Sorry for any misunderstanding. — Tetsuya Yamamoto, May 15 '18 at 05:43
The query is excellent now. You helped me a lot. Many thanks. — skdan, May 15 '18 at 06:27

VB.NET LINQ to DataSet (SQL 'LEFT OUTER JOIN' alternative)

1 Answers1