[RESOLVED] LINQ Grouping

[stin]

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:

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.

[Craig Gemmill]

Sample xml file please.

[dglienna]

Here's a GROUPing sample:


Create a directory of customers grouped by country and city

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

[stin]

Here is a sample XML file

<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>

[stin]

This is the SQL I'm trying to recreate:

SELECT     USRDEFND5
FROM         int_gp_employee
GROUP BY USRDEFND5

[Cimperiali]

You sure distinct is not working?
Try the following:

 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...

[stin]

I did end up getting it working; here's the LINQ I used:

branch_ids = From row In xmlFile...<row> Select row.<USRDEFND5>.Value Distinct

Part of the problem was that things are apparently case-sensitive.