join problem in linq

Question

i have following tables

T1    
    ====   ======
     ID     Desc
    ====   ======
     1       t1
     2       t2
     3       t3
     4       t4


T2
=====   =======  ========
 ID      T1ID     PT1ID
=====    ======  =========
 1        2         1
 2        3         2

In T2 both T1ID and PT1ID are foreign key for T1. In my output i want row with ID=4 as it does not have any matching row in both foreign key columns

Answer 1

I'm not 100% clear what you are trying to do.

I assume that you want to return all rows from T1 and any matching rows from T2 where either the column T1ID or PT1ID values equal the T1 ID column.

If this is the case you need a outer join. You can get a good example of outer joins from the MSDN microsoft Forums

Answer 2

If your problem is to do an outer join : http://msdn.microsoft.com/en-us/library/bb399397.aspx

If your problem is to return differences : msdn.microsoft.com/en-us/library/bb386962.aspx

Answer 3

This should do want you want:

        var notInT2 = T1.Where(i => T2.All(e => e.T1ID != i.Id) &&
                                    T2.All(e => e.PT1ID != i.Id));

To test it:

class RowT1 { public Int32 Id;}
class RowT2 { public Int32 T1ID; public Int32 PT1ID;    }

class Programm
{
    static void Main()
    {
        var T1 = new List<RowT1>(){new RowT1(){Id=1},
                                   new RowT1(){Id=2},
                                   new RowT1(){Id=3},
                                   new RowT1(){Id=4}};

        var T2 = new List<RowT2>(){new RowT2(){T1ID=2, PT1ID=1}, 
                                   new RowT2(){T1ID=3, PT1ID=2}};

        var notInT2 = T1.Where(i => T2.All(e => e.T1ID != i.Id) &&
                                    T2.All(e => e.PT1ID != i.Id));

        Console.ReadLine();
    }
}