none
Left Outer Join in Linq to DataSet RRS feed

  • Question

  • I want to do left outer join between employee table and sanc table.

    Below is my code:

    var query = from e in Employee
                        join s
    in Sanc
                        on e
    .Field<long>("eCol1") equals s.Field<long>("sCol1") into empSan
                       
    select new
                       
    {
                           
    EName = e["eCol2"],
                           
    SancName = empSan
                       
    };

    I am getting the output as:-

          EName                                                   SancName                                              

    <Steve Smith> <System.Linq.Lookup`2+Grouping[System.Int64,System.Data.DataRow]>

    How to get the correct column value(i.e.,sancname) from Sanc table instead of this?



    • Edited by SwastikMS Thursday, April 19, 2012 4:37 AM
    Thursday, April 19, 2012 4:36 AM

All replies

  • var query = from e in Employee
                        join s in Sanc
                        on e.Field<long>("eCol1") equals s.Field<long>("sCol1") into empSan
                        select new
                        {
                            EName = e["eCol2"],
                            SancName = s["sancName"]
                        };

    Modify the code like above, what's the sancname's column name in your table, use s[""] to assign it to SancName;

    Fighting like Allen Iverson. Neve give up!

    Sunday, April 22, 2012 8:40 AM
  • Hi SwastikMS,

    Please try the code below.

    var query = from e in Employee join s in Sanc on e["eCol1"] equals s["sCol1"] 
    select new {EName = e["eCol2"], SanName = s["sCol2"]};

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, April 24, 2012 9:10 AM
    Moderator
  • Hi Allen Li [MSFT]

    The code you have provided is for inner join.I want to do outer join with the two table(both left outer join and right outer join).


    Best Regards
    SwastikMS

    Tuesday, April 24, 2012 11:08 AM
  • Hi SwastikMS,

    OK, please refer to the code below, it's outer join.

    var query = from e in Employee join s in Sanc on e["eCol1"] equals s["sCol1"] into gp from g in gp.DefaultIfEmpty() select new {EName = e["eCol2"], SancName = g["sCol2"]};

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, April 25, 2012 7:59 AM
    Moderator
  • Hi Allen Li [MSFT]
       Thanks for your reply.

    I had tried using the below query.It's returning 39 records.
    var query = (from e in Employee
                         join s in Sanc on e.Field<long>("esancid") equals s.Field<long>("ssancid") into empSan
                         from es in empSan.DefaultIfEmpty()
                         select new
                         {
                             EName = e.Field<string>("ename"),
                             SancName = es == null ? string.Empty : es.Field<string>("ssancname")
                         });

    I tried your query too.It's returning only 10 records.
    But none of them giving me the accurate no. of records(i.e.,58).

    I am providing the pseudo query for the two returned tables:

    {

    select eid,eename,isnull(esancid,'') esancid from Employee where eOfficeId=officeid

    select ssancid,ISNULL(ssancname,'') ssancname from Sanc

    }

            Here I had to handle null otherwise I was getting exception.So I did this way.while in Sql Server we don't need to handle for null.

    Can you make me clear of these concepts? Actually I am completely new to Linq and Linq to DataSets too.


    Best Regards
    SwastikMS


    • Edited by SwastikMS Wednesday, April 25, 2012 11:35 AM
    Wednesday, April 25, 2012 11:33 AM
  • Hi SwastikMS,

    In your last post, you have already handle the null scenario, could you please clarify which instance may be null in the query? And, the query statement seems not different with mine, I want to make sure again what the exact result you need to query out. I'd appreciate if you can upload a demo to SkyDrive and post the link here. This is, I will help you to test the code and do my best to provide a solution.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, April 26, 2012 5:41 AM
    Moderator
  • Hi Allen Li [MSFT]

         Yes you are right.Both yours and my query are same.But they are literally giving different results.

        I have two tables named Sanction Master(parent table) and Employee(child table).Here employees may or may not have been assigned with any sanction id.In case any employees has not been assigned to any sancid then the corresponding column will have null value.

    Now my objective is to show

    1)Which employees have any Sanction and who have not.

    2)Which Sanction orders have been assigned to any employees and which are free.

    And I want to use outer join for this.


    Best Regards
    SwastikMS


    • Edited by SwastikMS Thursday, April 26, 2012 9:20 AM
    Thursday, April 26, 2012 9:19 AM