none
LINQ query: left outer join issue RRS feed

  • Question

  • Hi,

    my left outer join query doesn't work with a In clause (Contains).

     

    var query = from cust in context.Customers
       join custSalesOrg in context.CUSTOMERSALESORG on cust.ID equals custSalesOrg.CUSTOMER_ID into custSalesOrgGroups
                from custSalesOrg in custSalesOrgGroups.DefaultIfEmpty()
       where custSalesOrg.ACTIV.ToUpper() == "Y"

    This query works !

    var
     query = from cust in context.Customers join custSalesOrg in context.CUSTOMERSALESORG on cust.ID equals custSalesOrg.CUSTOMER_ID into custSalesOrgGroups             from custSalesOrg in custSalesOrgGroups.DefaultIfEmpty() where brandId_.Contains(custSalesOrg.BRAND_ID)
    This query generate an inner join instead of a left outer join

     

    Any idea ?

    Thanks in advance

    Max


    Monday, July 18, 2011 8:22 AM

Answers

  • Thank you for posting.

    Regarding left join and right join, I suggest you can check the following code snippets.

    var LeftJoin = from emp in ListOfEmployees
    join dept in ListOfDepartment
    on emp.DeptID equals dept.ID into JoinedEmpDept 
    from dept in JoinedEmpDept.DefaultIfEmpty()
    select new             
    {
    EmployeeName = emp.Name,
    DepartmentName = dept != null ? dept.Name : null          
    };
    
    var RightJoin = from dept in ListOfDepartment
    join employee in ListOfEmployees
    on dept.ID equals employee.DeptID into joinDeptEmp
    from employee in joinDeptEmp.DefaultIfEmpty()
    select new              
    {
    EmployeeName = employee != null ? employee.Name : null,
    DepartmentName = dept.Name
    };
    

    In addition, if it doesn't work, could you please provide more detailed error information in your post to help us analyze your question better?

    Best Regards,


    Larcolais Gong[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.

    Wednesday, July 20, 2011 4:02 AM
  • Hello again,

    Did the above code snippet not work on SQL provider? I recommend you can utilize SQL Server Profiler to check how it worked internally. Please check here Viewing and Analyzing Traces with SQL Server Profiler for more information. http://msdn.microsoft.com/en-us/library/ms175848.aspx

    Please feel free to let us know if you have any finding.

    Best Regards,


    Larcolais Gong[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.

    Thursday, July 21, 2011 6:26 AM

All replies

  • Any one ?
    Tuesday, July 19, 2011 11:58 AM
  • Thank you for posting.

    Regarding left join and right join, I suggest you can check the following code snippets.

    var LeftJoin = from emp in ListOfEmployees
    join dept in ListOfDepartment
    on emp.DeptID equals dept.ID into JoinedEmpDept 
    from dept in JoinedEmpDept.DefaultIfEmpty()
    select new             
    {
    EmployeeName = emp.Name,
    DepartmentName = dept != null ? dept.Name : null          
    };
    
    var RightJoin = from dept in ListOfDepartment
    join employee in ListOfEmployees
    on dept.ID equals employee.DeptID into joinDeptEmp
    from employee in joinDeptEmp.DefaultIfEmpty()
    select new              
    {
    EmployeeName = employee != null ? employee.Name : null,
    DepartmentName = dept.Name
    };
    

    In addition, if it doesn't work, could you please provide more detailed error information in your post to help us analyze your question better?

    Best Regards,


    Larcolais Gong[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.

    Wednesday, July 20, 2011 4:02 AM
  • Hi,

    Thank you too for you reply ! :)

    When I put a where clause on employee or dept (in your examples), the query generate a simple inner join and not a left/right outer join

    I'm working wîth the Oracle provider !

     

    Thanks for your help

    Max

     

     

     

    Wednesday, July 20, 2011 4:43 PM
  • Hello again,

    Did the above code snippet not work on SQL provider? I recommend you can utilize SQL Server Profiler to check how it worked internally. Please check here Viewing and Analyzing Traces with SQL Server Profiler for more information. http://msdn.microsoft.com/en-us/library/ms175848.aspx

    Please feel free to let us know if you have any finding.

    Best Regards,


    Larcolais Gong[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.

    Thursday, July 21, 2011 6:26 AM