-
[RESOLVED] LINQ Grouping
I'm having a heck of a time with this. I'm trying to get some distinct values from an XML table.
I've looked at using distinct but that wasn't working at all so thought I'd just group by the element I want distinct and only return that field. LINQ is busting my chops man, it just doesn't make any sense to me.
It seems like it is returning all records. As I debug and go through the loop, the branch.value isn't changing. There are 17000 "row"s in the xml but only 3 distinct "USRDEFND5".
Here's the code I'm currently using:
Code:
branch_ids = From b In xmlFile.Descendants.Elements("row") Group By bid = b.Element("USRDEFND5") Into Group Select bid
For Each branch As XElement In branch_ids
myConnection.Open()
myCommand = New SqlCommand("DELETE FROM " & files(i) & " WHERE USRDEFND5 = '" & branch.Value & "'", myConnection)
dr = myCommand.ExecuteReader()
dr.Close()
myConnection.Close()
Next
Thanks for any help.
-
Re: LINQ Grouping
-
Re: LINQ Grouping
Here's a GROUPing sample:
Create a directory of customers grouped by country and city
Code:
Public Sub XLinq54()
Dim customers = XDocument.Load(dataPath + "nw_customers.xml")
Dim dir = <directory>
<%= From customer In customers...<Customers> _
From country In customer.<FullAddress>.<Country> _
Group customer By Key = CStr(country) Into countryGroup = Group _
Let country = Key _
Select <Country name=<%= country %> numberOfCustomers=<%= countryGroup.Count %>>
<%= From customer In countryGroup _
From city In customer...<City> _
Group customer By CityKey = CStr(city) Into cityGroup = Group _
Let city = CityKey _
Select <City name=<%= city %> numberOfCustomers=<%= cityGroup.Count() %>>
<%= cityGroup.<ContactName> %>
</City> %>
</Country> %>
</directory>
Console.WriteLine(dir)
End Sub
-
Re: LINQ Grouping
Here is a sample XML file
Code:
<int_gp_customer>
<row>
<int_id>1AD00F79C8E44F7CBF115B0039F3B5F1</int_id>
<pk_id>14E7336CC7FE40A7A1495976206D1B</pk_id>
<external_id>0629ABC123456</external_id>
<transaction_id></transaction_id>
<date_updated>2009-10-20T09:31:05</date_updated>
<updated_by_id>4102F140-D2D2-47EA-9E88-971881EB</updated_by_id>
<error_num>0</error_num>
<error_msg></error_msg>
<date_created>2005-03-08T12:00:00</date_created>
<db_id>LAL</db_id>
<CUSTNMBR>0629ABC123456</CUSTNMBR>
<CUSTNAME>ABC Inc.</CUSTNAME>
<CUSTCLAS>DEFAULT</CUSTCLAS>
<TAXSCHID></TAXSCHID>
<SHIPMTHD></SHIPMTHD>
<ADDRESS1>123 Ave</ADDRESS1>
<ADDRESS2 />
<ADDRESS3 />
<CITY>NEW NEW YORK</CITY>
<STATE>NY</STATE>
<ZIPCODE>12345</ZIPCODE>
<COUNTRY>USA</COUNTRY>
<PHNUMBR1>1234567890</PHNUMBR1>
<PHNUMBR2></PHNUMBR2>
<PHNUMBR3></PHNUMBR3>
<FAX></FAX>
<SALSTERR></SALSTERR>
<SLPRSNID></SLPRSNID>
<SLPRSNFN></SLPRSNFN>
<SPRSNSLN></SPRSNSLN>
<UPSZONE></UPSZONE>
<CNTCPRSN></CNTCPRSN>
<CPRCSTNM>CC123456</CPRCSTNM>
<CHEKBKID></CHEKBKID>
<PYMTRMID></PYMTRMID>
<FNCHATYP>1</FNCHATYP>
<FNCHPCNT>0.05</FNCHPCNT>
<COMMENT1></COMMENT1>
<COMMENT2></COMMENT2>
<USERDEF1>30</USERDEF1>
<USERDEF2>0629</USERDEF2>
<TAXEXMT1></TAXEXMT1>
<TAXEXMT2></TAXEXMT2>
<TXRGNNUM></TXRGNNUM>
<PRBTADCD></PRBTADCD>
<PRSTADCD></PRSTADCD>
<ADRSCODE>01</ADRSCODE>
<STADDRCD>01</STADDRCD>
<UpdateIfExists>1</UpdateIfExists>
<CreateAddress>1</CreateAddress>
<INACTIVE>0</INACTIVE>
<HOLD>0</HOLD>
<CRCARDID></CRCARDID>
<CRCRDNUM></CRCRDNUM>
<CCRDXPDT>1900-01-01T00:00:00</CCRDXPDT>
<STMTNAME></STMTNAME>
<SHRTNAME></SHRTNAME>
<Revalue_Customer>0</Revalue_Customer>
<Post_Results_To>0</Post_Results_To>
<FINCHID></FINCHID>
<GOVCRPID></GOVCRPID>
<GOVINDID></GOVINDID>
<DISGRPER></DISGRPER>
<DUEGRPER></DUEGRPER>
<DOCFMTID></DOCFMTID>
<BALNCTYP>0</BALNCTYP>
<CRLMTTYP>1</CRLMTTYP>
<CRLMTAMT>0.00</CRLMTAMT>
<Send_Email_Statements>0</Send_Email_Statements>
<TO_Email_Recipient></TO_Email_Recipient>
<CC_Email_Recipient></CC_Email_Recipient>
<BC_Email_Recipient></BC_Email_Recipient>
<USERLANG></USERLANG>
<GPSFOINTEGRATONID></GPSFOINTEGRATONID>
<INTEGRATIONSOURCE></INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<ORDERFULFILLDEFAULT>1</ORDERFULFILLDEFAULT>
<CUSTPRIORITY>1</CUSTPRIORITY>
<CCode></CCode>
<SHIPCOMPLETE>0</SHIPCOMPLETE>
<CURNCYID></CURNCYID>
<PRCLEVEL></PRCLEVEL>
<STMTCYCL>5</STMTCYCL>
<RequesterTrx>0</RequesterTrx>
<USRDEFND1>06</USRDEFND1>
<USRDEFND2>29</USRDEFND2>
<USRDEFND3>LAL</USRDEFND3>
<USRDEFND4></USRDEFND4>
<USRDEFND5>92CB5F5F42884C439BA1A5EA3A3535E1</USRDEFND5>
</row>
<row>
<int_id>DAD00F79C8E44F7CBF115B0039F3B5F1</int_id>
<pk_id>14E7336CC7FE40A7A1495976206D1B</pk_id>
<external_id>0629ABC123457</external_id>
<transaction_id></transaction_id>
<date_updated>2009-10-20T09:31:05</date_updated>
<updated_by_id>4102F140-D2D2-47EA-9E88-971881EB</updated_by_id>
<error_num>0</error_num>
<error_msg></error_msg>
<date_created>2005-03-08T12:00:00</date_created>
<db_id>LAL</db_id>
<CUSTNMBR>0629ABC123457</CUSTNMBR>
<CUSTNAME>ABC Inc.</CUSTNAME>
<CUSTCLAS>DEFAULT</CUSTCLAS>
<TAXSCHID></TAXSCHID>
<SHIPMTHD></SHIPMTHD>
<ADDRESS1>123 Ave</ADDRESS1>
<ADDRESS2 />
<ADDRESS3 />
<CITY>NEW NEW YORK</CITY>
<STATE>NY</STATE>
<ZIPCODE>12345</ZIPCODE>
<COUNTRY>USA</COUNTRY>
<PHNUMBR1>1234567890</PHNUMBR1>
<PHNUMBR2></PHNUMBR2>
<PHNUMBR3></PHNUMBR3>
<FAX></FAX>
<SALSTERR></SALSTERR>
<SLPRSNID></SLPRSNID>
<SLPRSNFN></SLPRSNFN>
<SPRSNSLN></SPRSNSLN>
<UPSZONE></UPSZONE>
<CNTCPRSN></CNTCPRSN>
<CPRCSTNM>CC123456</CPRCSTNM>
<CHEKBKID></CHEKBKID>
<PYMTRMID></PYMTRMID>
<FNCHATYP>1</FNCHATYP>
<FNCHPCNT>0.05</FNCHPCNT>
<COMMENT1></COMMENT1>
<COMMENT2></COMMENT2>
<USERDEF1>30</USERDEF1>
<USERDEF2>0629</USERDEF2>
<TAXEXMT1></TAXEXMT1>
<TAXEXMT2></TAXEXMT2>
<TXRGNNUM></TXRGNNUM>
<PRBTADCD></PRBTADCD>
<PRSTADCD></PRSTADCD>
<ADRSCODE>01</ADRSCODE>
<STADDRCD>01</STADDRCD>
<UpdateIfExists>1</UpdateIfExists>
<CreateAddress>1</CreateAddress>
<INACTIVE>0</INACTIVE>
<HOLD>0</HOLD>
<CRCARDID></CRCARDID>
<CRCRDNUM></CRCRDNUM>
<CCRDXPDT>1900-01-01T00:00:00</CCRDXPDT>
<STMTNAME></STMTNAME>
<SHRTNAME></SHRTNAME>
<Revalue_Customer>0</Revalue_Customer>
<Post_Results_To>0</Post_Results_To>
<FINCHID></FINCHID>
<GOVCRPID></GOVCRPID>
<GOVINDID></GOVINDID>
<DISGRPER></DISGRPER>
<DUEGRPER></DUEGRPER>
<DOCFMTID></DOCFMTID>
<BALNCTYP>0</BALNCTYP>
<CRLMTTYP>1</CRLMTTYP>
<CRLMTAMT>0.00</CRLMTAMT>
<Send_Email_Statements>0</Send_Email_Statements>
<TO_Email_Recipient></TO_Email_Recipient>
<CC_Email_Recipient></CC_Email_Recipient>
<BC_Email_Recipient></BC_Email_Recipient>
<USERLANG></USERLANG>
<GPSFOINTEGRATONID></GPSFOINTEGRATONID>
<INTEGRATIONSOURCE></INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<ORDERFULFILLDEFAULT>1</ORDERFULFILLDEFAULT>
<CUSTPRIORITY>1</CUSTPRIORITY>
<CCode></CCode>
<SHIPCOMPLETE>0</SHIPCOMPLETE>
<CURNCYID></CURNCYID>
<PRCLEVEL></PRCLEVEL>
<STMTCYCL>5</STMTCYCL>
<RequesterTrx>0</RequesterTrx>
<USRDEFND1>06</USRDEFND1>
<USRDEFND2>29</USRDEFND2>
<USRDEFND3>LAL</USRDEFND3>
<USRDEFND4></USRDEFND4>
<USRDEFND5>051FA957AB4A4203B5BCC95D1E120B26</USRDEFND5>
</row>
</int_gp_customer>
-
Re: LINQ Grouping
This is the SQL I'm trying to recreate:
Code:
SELECT USRDEFND5
FROM int_gp_employee
GROUP BY USRDEFND5
-
Re: LINQ Grouping
You sure distinct is not working?
Try the following:
Code:
Dim query = (From emp In thefile.Descendants("row") _
Select emp.Element("USRDEFND5").Value).Distinct
For Each u In query
Debug.Print(u)
Next
based on your sample data I added a couple of rows varying one for a different data and one for USRDEFND5, and i correctly got 3 distinct rows...
-
Re: LINQ Grouping
I did end up getting it working; here's the LINQ I used:
Code:
branch_ids = From row In xmlFile...<row> Select row.<USRDEFND5>.Value Distinct
Part of the problem was that things are apparently case-sensitive.
