LINQ join query w/ 2 datatables - not getting expected results

[stomas]

Hi...complete novice at LINQ here, so apologies for my ignorance...I'm trying to use the below query that will return all of the records in the 'dtNewAssets' datatable that have a corresponding value for the 'ACCOUNT_NAME' field in the 'dtExistingAssets' datatable. When I execute the query all I get back all the records from the 'dtNewAssets' datatable with a bunch of duplicate records wherever dtNewAssets.ACCOUNT_NAME = dtExistingAssets.ACCOUNT_NAME. What am I doing wrong here? Thanks in advance.


Dim Query1 = From asset In dtNewAssets _
Join exasset In dtExistingAssets _
On exasset("ACCOUNT_NAME") Equals asset("ACCOUNT_NAME") _
Select New With _
{ _
.ACCOUNT_NAME = asset.Field(Of String)("ACCOUNT_NAME"), _
.SUB_NO = asset.Field(Of String)("SUB_NO"), _
.CONTACT_FIRST_NAME = asset.Field(Of String)("CONTACT_FIRST_NAME"), _
.CONTACT_LAST_NAME = asset.Field(Of String)("CONTACT_LAST_NAME"), _
.CONTACT_EMAIL = asset.Field(Of String)("CONTACT_EMAIL"), _
.USER_ID = asset.Field(Of String)("USER_ID"), _
.INSTALL_TO_SUB_NUM = asset.Field(Of String)("INSTALL_TO_SUB_NUM"), _
.ORIG_ORDER_ID = asset.Field(Of String)("ORIG_ORDER_ID"), _
.AKR = asset.Field(Of String)("AKR"), _
.SPECIAL_PRICE = asset.Field(Of String)("SPECIAL_PRICE"), _
.SPECIAL_PRICE_CURRENCY = asset.Field(Of String)("SPECIAL_PRICE_CURRENCY"), _
.SPECIAL_PRICE_DATE = asset.Field(Of String)("SPECIAL_PRICE_DATE"), _
.LOCATION_ACCOUNT_NUMBER = asset.Field(Of String)("LOCATION_ACCOUNT_NUMBER"), _
.BILLING_ACCOUNT_NUMBER = asset.Field(Of String)("BILLING_ACCOUNT_NUMBER"), _
.PRODUCT_NAME = asset.Field(Of String)("PRODUCT_NAME"), _
.PLI = asset.Field(Of String)("PLI"), _
.ASSET_STATUS = asset.Field(Of String)("ASSET_STATUS"), _
.NET_UNIT_PRICE = asset.Field(Of String)("NET_UNIT_PRICE"), _
.NET_UNIT_PRICE_CURRENCY = asset.Field(Of String)("NET_UNIT_PRICE_CURRENCY"), _
.BILLING_START_DATE = asset.Field(Of String)("BILLING_START_DATE"), _
.BILLING_STOP_DATE = asset.Field(Of String)("BILLING_STOP_DATE"), _
.SITE = asset.Field(Of String)("SITE"), _
.STREET = asset.Field(Of String)("STREET"), _
.CITY = asset.Field(Of String)("CITY"), _
.COUNTRY = asset.Field(Of String)("COUNTRY"), _
.REGION = asset.Field(Of String)("REGION"), _
.ACCOUNT_CLASS = asset.Field(Of String)("ACCOUNT_CLASS"), _
.LAST_BILL_PRICE = asset.Field(Of String)("LAST_BILL_PRICE"), _
.ILA = asset.Field(Of String)("ILA"), _
.ASSET_NUMBER = asset.Field(Of String)("ASSET_NUMBER"), _
.SEA_USER = asset.Field(Of String)("SEA_USER") _
}

 

[jmcilhinney]

You are performing an inner join, which means creating a new record for each combination of records in the two original tables based on the specified criteria. If you have one record in table 1 and 10 records that are related in table 2 then you'll get 10 records in the output. If you just want the 1 record from table 1 then you don't want to do a join at all. All you want to do is perform a query on the first table with a WHERE clause based on the second. The specifics may vary but here's one way you could do that:

Dim accountNames = (From row In dtExistingAssets.AsEnumerable()
                    Select row.Field(Of String)("ACCOUNT_NAME")
                    Distinct).ToArray()
Dim matches = (From row In dtNewAssets.AsEnumerable()
               Where accountNames.Contains(row.Field(Of String)("ACCOUNT_NAME"))
               Select row).CopyToDataTable()

 

[stomas]

Hi...it turned out that part of my problem was that the datatables contained overlapping data due to me not clearing out a stringbuilder before reusing it. I have the join working fine but a different issue is now cropping up, I'll post to another thread. Thanks for the response!