none
Linq to Dataset sourced from XML RRS feed

  • Question

  • My project is needs to lookup US Cities to obtain a State.   Example is Media, A city(town) in both IL and PA comprising a total of 4 different zipcodes.  The goal is to have the user select the correct City/State and zip listing.  My XML source file is defined as follows:

    <?xml version="1.0" standalone="yes"?>
    <NewDataSet>
      <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="CityStZip" msdata:UseCurrentLocale="true">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
              <xs:element name="CityStZip">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="ZIPCODE" type="xs:string" minOccurs="0" />
                    <xs:element name="ZIPCLASS" type="xs:string" minOccurs="0" />
                    <xs:element name="CITY" type="xs:string" minOccurs="0" />
                    <xs:element name="STATE" type="xs:string" minOccurs="0" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:choice>
          </xs:complexType>
        </xs:element>
      </xs:schema>
      <CityStZip>
        <ZIPCODE>00501</ZIPCODE>
        <ZIPCLASS>U</ZIPCLASS>
        <CITY>HOLTSVILLE</CITY>
        <STATE>NY</STATE>
      </CityStZip>
    ...remainder of data removed (504333 line total i table.
    </NewDataSet>

    The table is loaded into a shared dataset during program startup. As follows:

      Private Function LoadDataFile(ByVal sPathFileName As String,
                                 ByVal sTableName As String) As Boolean
    
        If clsConfig.dsConfig.Tables.Contains(sTableName) Then 'Already loaded?
          Return True
        End If
    
        Dim ds As DataSet = New DataSet
        If Not (sPathFileName Is Nothing) Then
          Try 'load of datafile
            ds.ReadXml((sPathFileName), XmlReadMode.ReadSchema)
            ds.Tables(0).TableName = sTableName
            clsConfig.dsConfig.Tables.Add(ds.Tables(0).Copy)
            Return True
          Catch ex As Exception
            Return False
          End Try
        Else
          Return False
        End If
    
      End Function
    

    The table is loaded and I can show it's content via a datagridview.  My problem is that I can not select just the relevant rows for a specific city.  I can successfully load the rows when I use the SQL version, via LinqPad, of the table with the following LinQ query: 

    (from cty in CITYSTZiP where cty.city like "media*" order by cty.City select cty.city).firstordefault

    When I translate to VB.Net against the same SQL table all is good.

    Its when I go against the XML dataset I get enumeration returned no results.

    So I've tried several variations of the query with the same result.

    When I use the following:

        Dim Zips As DataTable = clsConfig.dsConfig.Tables("CityStZip")
        Dim query = From citys In Zips.AsEnumerable
        For Each c In query
          Console.WriteLine(c("City"))
        Next
    

    I correctly get the cities displayed so I'm getting to the data successfully.

    The following queries always result in no results:

    Dim query = From citys In Zips.AsEnumerable Where citys("CITY") = "Media" Select citys

    Dim query = (From dr In Zips.AsEnumerable).Where(Function(c) c.Field(Of String)("City") = "Media")

    So clearly I'm missing something anyone with any ideas?

    Yes I must use XML source files, Yes this is VB.Net LinQ, No I cannot define a Typed Dataset(xsd) the data must be able to be maintained without complex builtin or external data management tools. We must be able to maintain via a simple tool like notepad.

    This whole project is a library(DLL) of various support processes for end solutions.

    Wednesday, May 25, 2016 8:21 PM

Answers

  • Hi Mudoch2505,

    >>So clearly I'm missing something anyone with any ideas? 

    I create a console app by using your code snippet. it seems that we could modify the LINQ statement as below:

    Dim xmlPath As String = AppDomain.CurrentDomain.BaseDirectory + "Test.xml"
            Dim dataSet As New DataSet()
            For Each dataTable As DataTable In dataSet.Tables
                dataTable.BeginLoadData()
            Next
            dataSet.ReadXml(xmlPath)
            Dim zips As DataTable = dataSet.Tables(0)
    
            Dim query = From z In zips.AsEnumerable().Where(Function(w) w.Field(Of String)("CITY") = "HOLTSVILLE")
                        Select z
    
            For Each item In query
                Console.WriteLine("{0} -- {1}", item.Field(Of String)("ZIPCODE"), item.Field(Of String)("ZIPCLASS"))
            Next

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mudoch2505 Thursday, May 26, 2016 2:12 PM
    Thursday, May 26, 2016 9:59 AM
    Moderator
  • Hi Larry,

    You need to use .StartsWith ... something like this should work for you:

    .Where(Function(c) c.Field(Of String)("City").StartsWith("Medi"))


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Thursday, May 26, 2016 7:55 PM
    • Marked as answer by Mudoch2505 Thursday, May 26, 2016 11:11 PM
    Thursday, May 26, 2016 7:54 PM

All replies

  • Hi Mudoch2505,

    >>So clearly I'm missing something anyone with any ideas? 

    I create a console app by using your code snippet. it seems that we could modify the LINQ statement as below:

    Dim xmlPath As String = AppDomain.CurrentDomain.BaseDirectory + "Test.xml"
            Dim dataSet As New DataSet()
            For Each dataTable As DataTable In dataSet.Tables
                dataTable.BeginLoadData()
            Next
            dataSet.ReadXml(xmlPath)
            Dim zips As DataTable = dataSet.Tables(0)
    
            Dim query = From z In zips.AsEnumerable().Where(Function(w) w.Field(Of String)("CITY") = "HOLTSVILLE")
                        Select z
    
            For Each item In query
                Console.WriteLine("{0} -- {1}", item.Field(Of String)("ZIPCODE"), item.Field(Of String)("ZIPCLASS"))
            Next

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mudoch2505 Thursday, May 26, 2016 2:12 PM
    Thursday, May 26, 2016 9:59 AM
    Moderator
  • Cole Wu;

    Thank you for the reply, as I had already tried the syntax you provided, I was at first uncertain the results. Indeed I was using the correct Syntax and the result was Enumeration yielded no results.   I realized your sample was successful so I continued to bang my head on this.

    It turns out the lookup is case sensitive, interesting...  So now with a little further research I've modified your answer as follows:

    Dim query = From z In Zips.AsEnumerable().Where(Function(W) String.Compare(W.Field(OfString)("City"), "Media"StringComparison.InvariantCultureIgnoreCase) = 0) Select z

    So the results are as expected my four rows now exist. Thanks again.

    On to the next step, getting the city name when the user enters the start of the city... IE entry of Medi returns list of all cities starting with medi.

    Would you have any suggestions for this?

    Larry

     

    Thursday, May 26, 2016 2:12 PM
  • Hi Larry,

    You need to use .StartsWith ... something like this should work for you:

    .Where(Function(c) c.Field(Of String)("City").StartsWith("Medi"))


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Thursday, May 26, 2016 7:55 PM
    • Marked as answer by Mudoch2505 Thursday, May 26, 2016 11:11 PM
    Thursday, May 26, 2016 7:54 PM
  • Thank you Bonnie DeWitt,   Yeah that was what I thought but referring to the question you'll note the case sensitivity problem I encountered which was at the heart of the failed enumeration in the first place.

    Further research turns up that unlike SQL, XML sourced datatables are case sensitive.

    With the syntax I used I was not sure if the startswith was viable in a case insensitive way (the String.Compare(.. aspect).

    So as it turns out here is the final result: 

     
       Dim Zips As DataTable = clsConfig.dsConfig.Tables("CityStZip")
        Dim strSearch As String = "Media"
        Dim query = From z In Zips.AsEnumerable().Where(Function(W) W.Field(Of String)("City").StartsWith(strSearch, StringComparison.InvariantCultureIgnoreCase)) Select z
    

    The results are 6 US Cities start with Media according to official USPS data, which verifies with the source data.

    ANOTHER ONE BITES THE DUST OH YEAH! Thanks all!

    Larry



    • Edited by Mudoch2505 Thursday, May 26, 2016 11:31 PM Code not in code block, sorry.
    Thursday, May 26, 2016 11:26 PM
  • Thank you Bonnie DeWitt,   Yeah that was what I thought but referring to the question you'll note the case sensitivity problem I encountered which was at the heart of the failed enumeration in the first place.


    Yep, I should have included that IgnoreCase stuff in my reply ... but, you got it figured out, and that is awesome!!  =0)

    ANOTHER ONE BITES THE DUST OH YEAH! Thanks all!

    Now you've got that song stuck in my head!!!  (Good thing that I like the song!)   ;0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, May 26, 2016 11:58 PM