首页 文章

使用左连接转换LINQ内连接

提问于
浏览 次
2

我正在尝试在我的查询中实现左连接，此时我正在获取'对象引用未设置为对象的实例' .

该查询完美地作为内部联接工作，但我想要包括左表中的所有行，即使找到了匹配项 . 我试图关注一些以前的帖子，大多数都引用了DefaultIfEmpty（），但我没有弄明白 .

INNER JOIN - SQL
      SELECT TOP (1000) 
      FROM table1 as edc
      inner join table2 as c on edc.Id = c.Id
      inner join table3 as p on p.Id = c.Id
      group by p.Description

INNER JOIN - SQL

SELECT TOP (1000) 
  FROM table1 as edc
  inner join table2 as c on edc.Id = c.Id
  left join table3 as p on p.Id = c.Id
  group by p.Description

INNER JOIN - LINQ

from edc in table1
   join q1 in table2 on __edc.Id equals q1__.Id
   join q2 in _table3 on q2.Id equals q1.Id 
   group q1 by q2.Description
   into grouped
   select new MyObj
   {
     Label = grouped.Key,
     Value = grouped.Count(),
    }

LEFT JOIN - LINQ

from edc in table1
       join q1 in table2 on __edc.Id equals q1__.Id
       join q2 in _table3 on q2.Id equals q1.Id  into leftJoin
       from p in leftJoin.DefaultIfEmpty()
       group q1 by p.Description
       into grouped
       select new MyObj
       {
         Label = grouped.Key,
         Value = grouped.Count(),
        }
c# linq

2 回答

  • 0

    请考虑以下示例 . 我们有三个表，在table1和table2之间有一个左连接，第二个连接到table3 . 您需要在两个连接上指定 DefaultIfEmpty() 以包含右表中没有匹配的行 .

    public class Item
{
    public int Id { get; set; }

    public string Description { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var table1 = new List<Item>
        {
            new Item {Id = 1, Description = "a"},
            new Item {Id = 2, Description = "b"},
            new Item {Id = 3, Description = "c"},
            new Item {Id = 4, Description = "d"}
        };

        var table2 = new List<Item>
        {
            new Item {Id = 1, Description = "e"},
            new Item {Id = 2, Description = "f"},
            new Item {Id = 4, Description = "g"}
        };

        var table3 = new List<Item>
        {
            new Item {Id = 1, Description = "h"},
            new Item {Id = 4, Description = "h"},
            new Item {Id = 5, Description = "i"},
            new Item {Id = 6, Description = "j"}
        };

        var leftJoin = from t1 in table1
            join t2 in table2 on t1.Id equals t2.Id into firstJoin
            from x in firstJoin.DefaultIfEmpty()
            join t3 in table3 on x?.Id equals t3.Id into secondJoin
            from y in secondJoin.DefaultIfEmpty()
            select new
            {
                Table1Id = t1?.Id,
                Table1Description = t1?.Description,
                Table2Id = x?.Id,
                Table2Description = x?.Description,
                Table3Id =  y?.Id,
                Table3Description = y?.Description
            };

        Console.WriteLine("Left Join:");
        foreach (var i in leftJoin)
        {
            Console.WriteLine($"T1Id: {i.Table1Id}, T1Desc: {i.Table1Description}, " +
                              $"T2Id: {i.Table2Id}, T2Desc: {i.Table2Description}, " +
                              $"T3Id: {i.Table3Id}, T3Desc: {i.Table3Description}");
        }
        Console.WriteLine(string.Empty);

        var grouped = from x in leftJoin
            group x by x.Table3Description
            into group1
            select new
            {
                Label = group1.Key,
                Count = group1.Count()
            };

        Console.WriteLine("Left Join Grouped:");
        foreach (var i in grouped)
        {
            Console.WriteLine($"Label: {i.Label}, Count: {i.Count}");
        }

        Console.ReadLine();
    }
}

    运行该程序会产生以下输出：

    Left Join:
T1Id: 1, T1Desc: a, T2Id: 1, T2Desc: e, T3Id: 1, T3Desc: h
T1Id: 2, T1Desc: b, T2Id: 2, T2Desc: f, T3Id: , T3Desc:
T1Id: 3, T1Desc: c, T2Id: , T2Desc: , T3Id: , T3Desc:
T1Id: 4, T1Desc: d, T2Id: 4, T2Desc: g, T3Id: 4, T3Desc: h

Left Join Grouped:
Label: h, Count: 2
Label: , Count: 2

    希望这可以帮助！

    回复于
  • 0

    它非常简单，只需将“从p”更改为“从q2”：

    from edc in table1
   join q1 in table2 on __edc.Id equals q1__.Id
   join q2 in _table3 on q2.Id equals q1.Id  into leftJoin
   from q2 in leftJoin.DefaultIfEmpty()
   group q1 by p.Description
   into grouped
   select new MyObj
   {
     Label = grouped.Key,
     Value = grouped.Count(),
    }
    回复于

相关问题