none
Trouble getting results I want with Linq to Dataset Join statement RRS feed

  • Question

  • I have a dataset that includes 3 datatables that I've gotten from various sources.  I need to take the data from these three tables and create another datatable that I can attach to a dataview.  Table 2 is the crosswalk between the other two tables.

    Table 1 (SREthnicity)  Table 2 (PSSTEthnicity)  Table 3 (MUEthnicity)
    Field Value   Field Value   Field  Value
    ----- -----   ----- -----   -----  -----
    ID 821   SRValue WH   prms_code W
    Desc White   MUValue W   prms_short WHITE
    Value WH

     

    ID 823   SRValue AF   prms_code A
    Desc AfAmer   MUValue A   prms_short AFRICAN
    Value AF


    ID 824   SRValue    prms_code
    Desc Asian   MUValue    prms_short
    Value AS


    I'm attempting to return a result set that joins the Value field from Table 1 to SRValue field on Table 2 and joins MUValue field in table 2 to prms_code in Table 3.  As you can see with the above data above the third record would return a NULL for SRValue, MUValue, prms_code, prms_short.  Example below:


    ID Desc Value SRValue  MUValue  prms_code prms_short
    -- ---- ----- -------  -------  --------- ----------
    821 White WH WH  W  W  WHITE
    823 AfAmer AF AF  A  A  AFRICAN
    824 Asian AS Null  Null  Null  Null

     

    I've attempted the code below with no success.  Suggestions would be appreciated.

    'Query data from datasets
    Dim results = From s In ds.SREthnicity.AsEnumerable() _
                  Join p In ds.PSSTEthnicity.AsEnumerable() On s.Field(Of String)("Value") Equals p.Field(Of String)("SRValue") _
                  Join m In ds.MUEthnicity.AsEnumerable().DefaultIfEmpty On p.Field(Of String)("MUValue") Equals m.Field(Of String)("prms_code") _
                  Select SRID = s.Field(Of String)("ID"), SRDesc = s.Field(Of String)("Description"), SRValue = s.Field(Of String)("Value"), _
                  MUCode = m.Field(Of String)("prms_code"), MUShort = m.Field(Of String)("prms_short")

    \\THIS ONLY GIVES ME A RESULT WHERE THE RECORDS MATCH,  NOT WHAT I'M LOOKING FOR

    Tuesday, December 22, 2009 10:48 PM

Answers

  • Hello Curtis,

    Welcome to MDSN Forums!

    For the LINQ to DataSet left outer join, we can use the If function to check whether a certain row is Nothing.  If so we need to set some default value to certain properties.  For detail, please see the following sample codes:

    ====================================================================
           Dim results = From l In _

                         (From s In SREthnicity.AsEnumerable() _

                          Group Join p In PSSTEthnicity.AsEnumerable() On s.Field(Of String)("Value") Equals p.Field(Of String)("SRValue") Into list1 = Group _

                          From l1 In list1.DefaultIfEmpty() _

                          Select SRID = s.Field(Of String)("ID"), SRDesc = s.Field(Of String)("Desc"), SRValue = s.Field(Of String)("Value"), _

                            MUValue = If(l1 Is Nothing, Nothing, l1.Field(Of String)("MUValue"))) _

                        Group Join m In MUEthnicity.AsEnumerable() On l.MUValue Equals m.Field(Of String)("prms_code") Into list2 = Group _

                        From l2 In list2.DefaultIfEmpty() _

                        Select SRID = l.SRID, SRDesc = l.SRDesc, SRValue = l.SRValue, _

                        MUCode = If(l2 Is Nothing, String.Empty, l2.Field(Of String)("prms_code")), _

                        MUShort = If(l2 Is Nothing, String.Empty, l2.Field(Of String)("prms_short"))
    ====================================================================

    Happy New Year!

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 28, 2009 1:51 PM
    Moderator