如何在c# LINQ中执行左外连接到对象而不使用join-on-equal -into子句?有办法用where子句来实现吗? 正确的问题: 内连接很简单,我有一个这样的解决方案

List<JoinPair> innerFinal = (from l in lefts from r in rights where l.Key == r.Key
                             select new JoinPair { LeftId = l.Id, RightId = r.Id})

但是对于左外连接,我需要一个解决方案。我的是这样的,但它不工作

List< JoinPair> leftFinal = (from l in lefts from r in rights
                             select new JoinPair { 
                                            LeftId = l.Id, 
                                            RightId = ((l.Key==r.Key) ? r.Id : 0
                                        })

其中JoinPair是一个类:

public class JoinPair { long leftId; long rightId; }

当前回答

我想补充的是,如果您获得MoreLinq扩展,现在支持同质和异构左连接

http://morelinq.github.io/2.8/ref/api/html/Overload_MoreLinq_MoreEnumerable_LeftJoin.htm

例子:

//Pretend a ClientCompany object and an Employee object both have a ClientCompanyID key on them

return DataContext.ClientCompany
    .LeftJoin(DataContext.Employees,                         //Table being joined
        company => company.ClientCompanyID,                  //First key
        employee => employee.ClientCompanyID,                //Second Key
        company => new {company, employee = (Employee)null}, //Result selector when there isn't a match
        (company, employee) => new { company, employee });   //Result selector when there is a match

编辑:

回想起来,这可能会起作用,但它将IQueryable转换为IEnumerable,因为morelinq不会将查询转换为SQL。

您可以使用如下所述的GroupJoin: https://stackoverflow.com/a/24273804/4251433

这将确保它仍然是一个IQueryable,以防您以后需要对它进行进一步的逻辑操作。

其他回答

看一下这个例子。 这个查询应该工作:

var leftFinal = from left in lefts
                join right in rights on left equals right.Left into leftRights
                from leftRight in leftRights.DefaultIfEmpty()
                select new { LeftId = left.Id, RightId = left.Key==leftRight.Key ? leftRight.Id : 0 };

如“Perform left outer joins”中所述:

var q =
    from c in categories
    join pt in products on c.Category equals pt.Category into ps_jointable
    from p in ps_jointable.DefaultIfEmpty()
    select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };

如果需要连接和筛选某些东西,可以在连接之外完成。可以在创建集合之后进行筛选。

在这种情况下,如果我在连接条件中这样做,我减少了返回的行。

使用三元条件(= n == null ?"__": n.MonDayNote,)

如果对象为空(因此不匹配),则返回?后面的内容。__,在这种情况下。 否则,返回:,n.MonDayNote后面的内容。

感谢其他贡献者,这是我开始自己的问题。


        var schedLocations = (from f in db.RAMS_REVENUE_LOCATIONS
              join n in db.RAMS_LOCATION_PLANNED_MANNING on f.revenueCenterID equals

                  n.revenueCenterID into lm

              from n in lm.DefaultIfEmpty()

              join r in db.RAMS_LOCATION_SCHED_NOTE on f.revenueCenterID equals r.revenueCenterID
              into locnotes

              from r in locnotes.DefaultIfEmpty()
              where f.LocID == nLocID && f.In_Use == true && f.revenueCenterID > 1000

              orderby f.Areano ascending, f.Locname ascending
              select new
              {
                  Facname = f.Locname,
                  f.Areano,
                  f.revenueCenterID,
                  f.Locabbrev,

                  //  MonNote = n == null ? "__" : n.MonDayNote,
                  MonNote = n == null ? "__" : n.MonDayNote,
                  TueNote = n == null ? "__" : n.TueDayNote,
                  WedNote = n == null ? "__" : n.WedDayNote,
                  ThuNote = n == null ? "__" : n.ThuDayNote,

                  FriNote = n == null ? "__" : n.FriDayNote,
                  SatNote = n == null ? "__" : n.SatDayNote,
                  SunNote = n == null ? "__" : n.SunDayNote,
                  MonEmpNbr = n == null ? 0 : n.MonEmpNbr,
                  TueEmpNbr = n == null ? 0 : n.TueEmpNbr,
                  WedEmpNbr = n == null ? 0 : n.WedEmpNbr,
                  ThuEmpNbr = n == null ? 0 : n.ThuEmpNbr,
                  FriEmpNbr = n == null ? 0 : n.FriEmpNbr,
                  SatEmpNbr = n == null ? 0 : n.SatEmpNbr,
                  SunEmpNbr = n == null ? 0 : n.SunEmpNbr,
                  SchedMondayDate = n == null ? dMon : n.MondaySchedDate,
                  LocNotes = r == null ? "Notes: N/A" : r.LocationNote

              }).ToList();
                Func<int, string> LambdaManning = (x) => { return x == 0 ? "" : "Manning:" + x.ToString(); };
        DataTable dt_ScheduleMaster = PsuedoSchedule.Tables["ScheduleMasterWithNotes"];
        var schedLocations2 = schedLocations.Where(x => x.SchedMondayDate == dMon);

下面是使用方法语法的一个相当容易理解的版本:

IEnumerable<JoinPair> outerLeft =
    lefts.SelectMany(l => 
        rights.Where(r => l.Key == r.Key)
              .DefaultIfEmpty(new Item())
              .Select(r => new JoinPair { LeftId = l.Id, RightId = r.Id }));

这是一种SQL语法,与用于内连接和左外连接的LINQ语法相比。 左外连接:

http://www.ozkary.com/2011/07/linq-to-entity-inner-and-left-joins.html

下面的示例在产品和类别之间进行组连接。这本质上是左连接。即使类别表为空,into表达式也会返回数据。要访问类别表的属性,现在必须通过在catList.DefaultIfEmpty()语句中添加from cl从可枚举结果中进行选择。