如何在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; }
这是一般形式(已经在其他答案中提供了)
var c =
from a in alpha
join b in beta on b.field1 equals a.field1 into b_temp
from b_value in b_temp.DefaultIfEmpty()
select new { Alpha = a, Beta = b_value };
然而，这里有一个解释，我希望能澄清这实际上是什么意思!
join b in beta on b.field1 equals a.field1 into b_temp
本质上创建了一个单独的结果集b_temp，其中有效地包含了右边条目的空“rows”('b'中的条目)。
然后是下一行:
from b_value in b_temp.DefaultIfEmpty()
．．遍历该结果集，为右侧的'row'设置默认空值，并将右侧的行连接的结果设置为'b_value'的值(即，如果有匹配的记录，则为右侧的值，如果没有则为'null')。
现在，如果右边是一个单独的LINQ查询的结果，它将由匿名类型组成，它只能是'something'或'null'。如果它是一个可枚举的对象(例如，一个List -其中MyObjectB是一个有2个字段的类)，那么它可以具体说明它的属性使用什么默认的“null”值:
var c =
from a in alpha
join b in beta on b.field1 equals a.field1 into b_temp
from b_value in b_temp.DefaultIfEmpty( new MyObjectB { Field1 = String.Empty, Field2 = (DateTime?) null })
select new { Alpha = a, Beta_field1 = b_value.Field1, Beta_field2 = b_value.Field2 };
这确保了'b'本身不是空的(但它的属性可以是空的，使用您指定的默认空值)，这允许您检查b_value的属性，而不会得到b_value的空引用异常。注意，对于可空的DateTime， (DateTime?)类型，即。'nullable DateTime'必须在'DefaultIfEmpty'的规范中指定为null的'Type'(这也适用于'原生'不为空的类型，例如double, float)。
您可以通过简单地链接上述语法来执行多个左外连接。
下面是一个版本的扩展方法解决方案，使用IQueryable代替IEnumerable
public class OuterJoinResult<TLeft, TRight>
{
public TLeft LeftValue { get; set; }
public TRight RightValue { get; set; }
}
public static IQueryable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(this IQueryable<TLeft> left, IQueryable<TRight> right, Expression<Func<TLeft, TKey>> leftKey, Expression<Func<TRight, TKey>> rightKey, Expression<Func<OuterJoinResult<TLeft, TRight>, TResult>> result)
{
return left.GroupJoin(right, leftKey, rightKey, (l, r) => new { l, r })
.SelectMany(o => o.r.DefaultIfEmpty(), (l, r) => new OuterJoinResult<TLeft, TRight> { LeftValue = l.l, RightValue = r })
.Select(result);
}
如果需要连接和筛选某些东西，可以在连接之外完成。可以在创建集合之后进行筛选。
在这种情况下，如果我在连接条件中这样做，我减少了返回的行。
使用三元条件(= 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);
class Program
{
List<Employee> listOfEmp = new List<Employee>();
List<Department> listOfDepart = new List<Department>();
public Program()
{
listOfDepart = new List<Department>(){
new Department { Id = 1, DeptName = "DEV" },
new Department { Id = 2, DeptName = "QA" },
new Department { Id = 3, DeptName = "BUILD" },
new Department { Id = 4, DeptName = "SIT" }
};
listOfEmp = new List<Employee>(){
new Employee { Empid = 1, Name = "Manikandan",DepartmentId=1 },
new Employee { Empid = 2, Name = "Manoj" ,DepartmentId=1},
new Employee { Empid = 3, Name = "Yokesh" ,DepartmentId=0},
new Employee { Empid = 3, Name = "Purusotham",DepartmentId=0}
};
}
static void Main(string[] args)
{
Program ob = new Program();
ob.LeftJoin();
Console.ReadLine();
}
private void LeftJoin()
{
listOfEmp.GroupJoin(listOfDepart.DefaultIfEmpty(), x => x.DepartmentId, y => y.Id, (x, y) => new { EmpId = x.Empid, EmpName = x.Name, Dpt = y.FirstOrDefault() != null ? y.FirstOrDefault().DeptName : null }).ToList().ForEach
(z =>
{
Console.WriteLine("Empid:{0} EmpName:{1} Dept:{2}", z.EmpId, z.EmpName, z.Dpt);
});
}
}
class Employee
{
public int Empid { get; set; }
public string Name { get; set; }
public int DepartmentId { get; set; }
}
class Department
{
public int Id { get; set; }
public string DeptName { get; set; }
}
输出
