none
Full outer join with LINQ to Dataset example (work around) RRS feed

  • Question

  • Hi,

    Base from what I am reading about LINQ Full outher join is not supported, and I could not really find a good example of a workaround for outer join in LINQ to Dataset.

     

    Here is an example I would like to see working with LINQ to Dataset

     

    Let's start with SQL server side with two tables

     

    Table 1

    CREATE TABLE [dbo].[ttsls](

    [supp_inv_id] [int] NULL,

    [family_id] [int] NULL,

    [amount] [int] NULL

    ) ON [PRIMARY]

     

     

     

    Table 2

     

    CREATE TABLE [dbo].[imaging](

    [family_id] [int] NOT NULL,

    [image_name] [varchar](50) NULL

    ) ON [PRIMARY]

     

    Here is the data in both tables

     

    Table 1:

    supp_inv_id            family_id            amount

    ----------- --------------------------------------------------
    1                            11                         10
    2                            12                         5
    3                            13                         5
    4                            NULL                   7
    5                            NULL                   9

     

    Table 2:

     

    family_id         image_name

    ----------- --------------------------------------------------

    11                     image1

    12                     image2

    13                    image3

    16                    image3

    17                    image4

     

    Now I want to make a Full Join between these two tables with LINQ, let's start with SQL code

     

    This SQL code would give me the results I would like to have:

     

    Select

    ttsls.supp_inv_id,

    isnull(ttsls.family_id,imaging.family_id) as family_id,

    ttsls.amount,

    imaging.image_name

    from

    ttsls full outer join imaging on imaging.family_id = ttsls.family_id

     

    Data:

    supp_inv_id family_id family_id amount image_name

    ----------- ----------- ----------- ----------- --------------------------------------------------

    1                11           11        10 image1

    2               12            12        15 image2

    3               13           13          5 image3

    4              NULL       NULL      7 NULL

    5              NULL      NULL       9 NULL

    NULL       16          16            NULL image3

    NULL       17         17            NULL image4

     

    How would I get the same dataset using LINQ, let's say I have these two table in DataTable objects

    How would I get this Outer join to work?

     

    Thanks in advance

     

     

     

     

     

     

     

    Wednesday, July 16, 2008 6:45 PM

Answers