CODEWITHSUNDEEP
vb.netlinqdatatable
Ross Degrand
Ross Degrand

Reputation: 101

LINQ Compare two datatables with .except vb

Here is my situation, and I could use some help.

I have three datatables. 1 and 2 are from querying a database, and the the third is a table that will hold a final formatted table.

dt1 has 8 columns, dt2 has 5 columns, and a final formatted dtJoined. I have already added all the rows from dt1 to dtJoined.

Each table has a key ID field, I need to find the rows from dt2 that are not in dt1 and add them to the formatted dtJoined.

This is what I have so far:

Dim query1 = (From a In dt1.AsEnumerable()
              Where Not a.IsNull("CustZ")
              Select a.Field(Of String)("id").Trim)
Dim query2 = (From a In dt2.AsEnumerable()
             Select a.Field(Of String)("id").Trim)

This works fine (as in no errors), I pull out the key field of each table so I can compare the datarow

Next I want to compare query2 to query1 and only return the id's not in dt1.

Dim notDt1 = query2.Except(query1).ToDataTable

This is where I get an error. The error is:

An unhandled exception of type 'System.Reflection.TargetParameterCountException' occurred in mscorlib.dll

Additional information: Parameter count mismatch.

I have searched this error, and haven't come across anything I could apply to my situation. I am lost because each of the row collections only has one column.

If I can get past this error, my next step would be to join dt2 on the keys that were not in dt1 so I can get the columns that were removed from when I compared the tables.

Dim queryGetAllColumns = (From a In dt2.AsEnumerable()
                          Join b In notDt1.AsEnumerable()
                          On a("id") Equals b("id")
                          Select a).CopyToDataTable()

Then loop through queryGetAllColumns and add the rows to the formatted dtJoined table.

Upvotes: 1

Views: 3938

Answers (1)

NetMage
NetMage

Reputation: 26907

I believe your issue is you are trying to call .ToDataTable on a variable notDt1 that is of type IEnumerable(Of String) which results from your queries. Just leave off the .ToDataTable as unnecessary.

Upvotes: 0

Related Questions