外连接在linq [英] outer join in linq

问题描述

我有3个表在sqlServer如下：

i have 3 table in sqlServer As follows:

Cost_Types

Cost_Types

Id   COST_NAME
-------------
1      A
2      B
3      C
4      D
5      E
6      F
7      Z

和请求表

Id   No
----------
1     100
2     200
3     300

和Cost_Request（= clearance_cost）

and Cost_Request (= clearance_cost)

RequestId     CostId   Amount
-------------------------------
1               2       200
1               3       400

p>

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

我写这个代码

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

：

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();

返回此数据

 RequestId     CostId   Amount
    -------------------------------
    2               1      0
    2               2      0
    2               3      0
    2               4      0
    2               5      0
    2               6      0

请帮助我。感谢所有

推荐答案

我知道LEFT JOIN'ing的GroupJoin-> SelectMany-> DefaultIfEmpty技术是流行的...但我认为SelectMany-> NavigationPropertyJoin-> DefaultIfEmpty技术更清楚。

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
  }

这篇关于外连接在linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！