LINQ Join between datatables, two untyped fields

Question

I'm querying two databases and trying to join the result sets and query out of them using LINQ. It seems it would be an easy task, but without doing an explicit join I'm having major performance issues. When I do the explicit join, I am having trouble with VB syntax for making things explicit types. Working Code, Cleaned:

        For Each CurrRow In ResultsA.Tables(15).Rows
            CurrDate = CurrRow("Date")
            CurrID = CurrRow("ID")
            CurrVal = CurrRow("Val")
            Dim ResultsB = From SMW In DataSetA.Tables(0).AsEnumerable() _
                           Where SMW("ID") = CurrScheduleID And SMW("Time") = CurrProfileDate _
                           Select UTC_TS = SMW("Time"), Value = (SMW("VALUE") / 1000), Time_Zone = SMW("Time_Zone"), ID = SMW("ID")

            Dim CurrentResult As Object
            Dim boolSchedDateFound As Boolean = False
            For Each CurrentResult In ResultsB
                If CurrentResult.Value <> CurrVal Then
                    'LogIntegrityCheckErrorRow()
                End If
                boolSchedDateFound = True
            Next
        Next

This takes FOREVER to run with 100,000 rows.

I've been trying to rewrite this as:

        Dim MismatchRows = From TableAData In DataSetA.Tables(0).AsEnumerable() Join TableBData In DataSetB.Tables(15).AsEnumerable() _
                On New With {.TableAID = Convert.ToInt32(TableAData("ID")), .TableATime = Convert.ToDateTime(TableAData("Date"))} _
                Equals New With {.TableBDID = Convert.ToInt32(TableBData("ID")), .TableBTime = Convert.ToDateTime(TableBData("Time"))} _
            Select ..................  (Hard to clean up, but this isn't the part that's failing)

And I'm having a bear of a time with it. The fundamental problem is the lack of strong typing. I've looked, but there seems to be little advice because most people doing this build EF on the data. Which isn't a terrible idea, but would require a bunch of re-engineering. So. Problem in front of me, how do I remove the error:

'Equals' cannot compare a value of type '<anonymous type> (line 2641)' with a value of type '<anonymous type> (line 2642)'.

Thank you so much for your help.

score 0 · Answer 1 · answered Mar 02 '14 at 01:48

0

Have you tried this?

Dim MismatchRows = From TableAData In ei _
    Join TableBData In e2 On _
        TableAData("ID") Equals TableBData("ID") And TableAData("Data") Equals TableBData("Time")
    Select .......

answered Mar 02 '14 at 01:48

Paulo Morgado

14,111
3
31
59

score 0 · Answer 2 · answered Mar 03 '14 at 13:48

    db.tb_DeviceGeFenceDetail.Join(db.tb_InventoryLog, Function(gfd) gfd.DeviceID, Function(il) il.deviceId, Function(gfd, il) New From { _
gfd.GeFenceLat1, _
gfd.GeFenceLng1, _
gfd.GeFenceLat2, _
gfd.GeFenceLng2, _
il.deviceName, _
il.DeviceIcon, _
gfd.DeviceID, _
il.id _
}).ToList().Where(Function(y) intValues.Contains(y.id))

you can try joining tables something like this.

LINQ Join between datatables, two untyped fields

2 Answers2