c# - Linq to Sql 联合语句

Question

我有两个要合并的结果集(LINQ to SQL/Entity)

var ldrSet = (from ldr in Leader
              join emp in employee
              on ldr.ID equals emp.ID
              where ldr.ID.Contains("123")
     select new {ID = ldr.ID, Name = emp.firstName + " " + emp.lastName, flag = "Edit"});

var allEmpSet = (from emp in employee
                 where emp.ID.Contains("123")
     select new {ID = ldr.ID, Name = emp.firstName + " " + emp.lastName, flag = "New"});

var results = ldrSet.Union(allEmpSet);

当我运行这个查询时，我得到这样的结果:

123 Joe Blow Edit

123 Joe Blow New

234 Jane Smith New

345 John Doe New

我明白为什么会发生这种情况，但有没有办法仅通过使用 ID 进一步过滤此结果集？

我想返回一条记录，Edit 记录胜过 New 记录。所以我只想要这个:

123 Joe Blow Edit

234 Jane Smith New

345 John Doe New

Answer 1

在这种情况下处理事情的正确方法不是联合。它是使用左外连接而不是 LINQ 默认的内连接。这将允许您一次选择所有行，并在数据匹配适当时设置 flag:

var results = from e in employee
              join l in leaders on l.ID equals e.ID into ls
              from l in ls.DefaultIfEmpty()
              select new { 
                  e.ID, 
                  Name = e.firstName + e.lastName,
                  flag = (p == null ? "New" : "Edit")
              };