none
Linq to Dataset - Left Outer Join NullException RRS feed

  • Question

  • Hello

    I'm trying to do a Left Outer Join with linq

    But in don't know why when i don't have values in the right table i get a nullexception...

    Tests that i have done

    Dim query = From regE In (From registoE In _dtRegistosAEnviar.AsEnumerable
             Group registoE By colaborador = registoE.Field(Of String)("Colaborador") Into grupoE = Group
             Select New With {
               .Colaborador = colaborador,
               .Seg = grupoE.Sum(Function(registoE) registoE.Field(Of Decimal)("Seg"))
             }).AsEnumerable
             Group Join regS In (From registoS In _dtRegistosEnviados.AsEnumerable
             Group registoS By colaborador = registoS.Field(Of String)("Colaborador") Into grupoS = Group
             Select New With {
               .ColaboradorS = colaborador,
               .SegS = grupoS.Sum(Function(registoS) registoS.Field(Of Decimal?)("Seg"))
             }).AsEnumerable
             On regE.Colaborador Equals regS.ColaboradorS Into juncao = Group
             From ele In juncao.DefaultIfEmpty
             Select New With {
               .Colaborador = regE.Colaborador,
               .HorasPorLancar = regE.Seg,
               .TheErrorColum= ele.SegS <<<<<<<<< ERROR >>>>>>>
             } 

    The tests that i made:

    IIf(ele.SegS Is Nothing, 0, ele.SegS)
    IIf(IsDBNull(ele.SegS), 0, ele.SegS)
    
    If(ele.SegS Is Nothing, 0, ele.SegS)
    If(IsDBNull(ele.SegS), 0, ele.SegS)
    
    Thanks

    • Edited by Mickey PT Friday, February 11, 2011 2:18 PM Bad code format
    Friday, February 11, 2011 2:05 PM

Answers

  • Hello Mickey PT,

     

    Welcome to the ADO.NET DataSet Forum.

    I'm not familiar with VB, so I give the examples using C#. Thank you for your understanding.

    We can use the GroupJoin operator as the SQL left outer join.   The compiler differentiate the GroupJoin and the Join operator by the into keyword in the next clause of the join clause. 

    If we need to enumerate each element of the inner sequence project a null value when no course exists for a given department, the job is left for the SelectMany and DefaultIfEmpty extension methods.  

     

    Using GroupJoin operator:

    ==========================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmetnID equals c.DepartmentID into courseList

                         select new

                         {

                             DepartmentName = d.Name,

                             Courses = courseList

                         };

    ==========================================================

    Using GroupJoin extension method:

    ==========================================================

    var query = db.Departments.GroupJoin(db.Courses,

                                                                             d => d.DepartmentID,

                                                                             c => c.DepartmentID,

                                                                             (d, courseList) => new

                                                                                                            {

                                                                                                                 DepartmentName = d.Name,

                                                                                                                 Courses = courseList

                                                                                                            });

    ========================================================= 

    Using SelectMany and DefaultIfEmpty:

    ==========================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmentID equals c.DepartmentID into courseList

                         from cl in courseList.DefaultIfEmpty()

                         select new

                         {

                              DepartmentName = d.Name,

                              CourseName = cl == null ? string.Empty : cl.Title

                         }

    ==========================================================

     

    I hope this will help you.

     

    Have a nice day,

     


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 15, 2011 6:35 AM
    Moderator

All replies

  • Anyone?

    :(

    Saturday, February 12, 2011 11:20 PM
  • Hello Mickey PT,

     

    Welcome to the ADO.NET DataSet Forum.

    I'm not familiar with VB, so I give the examples using C#. Thank you for your understanding.

    We can use the GroupJoin operator as the SQL left outer join.   The compiler differentiate the GroupJoin and the Join operator by the into keyword in the next clause of the join clause. 

    If we need to enumerate each element of the inner sequence project a null value when no course exists for a given department, the job is left for the SelectMany and DefaultIfEmpty extension methods.  

     

    Using GroupJoin operator:

    ==========================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmetnID equals c.DepartmentID into courseList

                         select new

                         {

                             DepartmentName = d.Name,

                             Courses = courseList

                         };

    ==========================================================

    Using GroupJoin extension method:

    ==========================================================

    var query = db.Departments.GroupJoin(db.Courses,

                                                                             d => d.DepartmentID,

                                                                             c => c.DepartmentID,

                                                                             (d, courseList) => new

                                                                                                            {

                                                                                                                 DepartmentName = d.Name,

                                                                                                                 Courses = courseList

                                                                                                            });

    ========================================================= 

    Using SelectMany and DefaultIfEmpty:

    ==========================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmentID equals c.DepartmentID into courseList

                         from cl in courseList.DefaultIfEmpty()

                         select new

                         {

                              DepartmentName = d.Name,

                              CourseName = cl == null ? string.Empty : cl.Title

                         }

    ==========================================================

     

    I hope this will help you.

     

    Have a nice day,

     


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 15, 2011 6:35 AM
    Moderator