none
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 t2.id 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.

     

    Thanks,

     

     

    Friday, December 5, 2008 6:34 PM

Answers

  • 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.

     

    Thanks,

     

    Friday, December 5, 2008 7:07 PM