Q:

outer join in linq

i have 3 table in sqlServer As follows:
Cost_Types
Id   COST_NAME
-------------
1      A
2      B
3      C
4      D
5      E
6      F
7      Z

and Request Table
Id   No
----------
1     100
2     200
3     300

and Cost_Request (= clearance_cost)
RequestId     CostId   Amount
-------------------------------
1               2       200
1               3       400

i want Get this Result
RequestId     CostId   Amount
    -------------------------------
    1               2       200
    1               3       400
    1               1        0
    1               4        0
    1               5        0
    1               6        0

i write this code
var context = new CLEARANCEEntities();
        var items = (from c in context.COST_TYPES
                     join t in context.CLEARANCE_COST
                         on c.COST_ID equals t.COST_ID into outer
                     from t in outer.DefaultIfEmpty()
                     where t. RequestId==1
                     select new
                          {
                            c.COST_ID,
                            c.COST_NAME, I = ((t == null) ? 0 : t.COST_AMOUNT)
                          }).ToList();

this code just return this
    RequestId     CostId   Amount
    -------------------------------
    1               2       200
    1               3       400

and i want if change Query As follows:
var context = new CLEARANCEEntities();
            var items = (from c in context.COST_TYPES
                         join t in context.CLEARANCE_COST
                             on c.COST_ID equals t.COST_ID into outer
                         from t in outer.DefaultIfEmpty()
                         where t.RequestId==2               
                         select new
                                    {
                                        c.COST_ID,
                                        c.COST_NAME, I = ((t == null) ? 0 : t.COST_AMOUNT)
                                    }).ToList();

return this data
 RequestId     CostId   Amount
    -------------------------------
    2               1      0
    2               2      0
    2               3      0
    2               4      0
    2               5      0
    2               6      0

please help me. thanks all

A:

I know the GroupJoin->SelectMany->DefaultIfEmpty technique of LEFT JOIN'ing is popular... but I think that the SelectMany->NavigationPropertyJoin->DefaultIfEmpty technique is much clearer.
var query =
  from c in context.CostTypes
  from xr in c.CostRequests
    .Where(xrRecord => xrRecord.RequestId == 1)
    .DefaultIfEmpty()
  select new {
    CostType = c,
    Amount = xr == null ? 0 : xr.Amount
  }

