left outer join with linq RRS feed

  • Question


    I have two related tables and need to return values from table1 that meets a criteria from table1 or does not exist in table2. 


    in sql i would do the following


    select t1.*

    from table1 t1 left outer join

    table2 t2 on t2.typeid=  t1.typeid


    where t1.value1 = 'value' or is null



    In LINQ I am this far..


    table1 = From t1 in Table1 _

    Join t2 in Table2 on t2.typeid Equals t1.typeid _

    where t2.intValue = 55   _

    select t1


    This returns only records that have the intValue of 55, but I need the records from t1 that also don't have t2 records.


    Any help would be appreciated.





    Friday, December 5, 2008 6:34 PM


  • Ok of course all i needed to do was post this and I would immediately find my own answer.  Here's what I did.


    Table1 = From t1 In Table1 _

    Group Join r In Table2 On t2.UserId Equals t1.UserID Into x = Group _

    From t2 In x.DefaultIfEmpty _

    Where t2.CodeId = 55  Or x.Count = 0 _

    Select t1


    If someone has a better idea using better practices please let me know.




    Friday, December 5, 2008 7:07 PM