如何在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; }
使用lambda表达式
db.Categories
.GroupJoin(db.Products,
Category => Category.CategoryId,
Product => Product.CategoryId,
(x, y) => new { Category = x, Products = y })
.SelectMany(
xy => xy.Products.DefaultIfEmpty(),
(x, y) => new { Category = x.Category, Product = y })
.Select(s => new
{
CategoryName = s.Category.Name,
ProductName = s.Product.Name
});
现在作为一个扩展方法:
public static class LinqExt
{
public static IEnumerable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(this IEnumerable<TLeft> left, IEnumerable<TRight> right, Func<TLeft, TKey> leftKey, Func<TRight, TKey> rightKey,
Func<TLeft, TRight, TResult> result)
{
return left.GroupJoin(right, leftKey, rightKey, (l, r) => new { l, r })
.SelectMany(
o => o.r.DefaultIfEmpty(),
(l, r) => new { lft= l.l, rght = r })
.Select(o => result.Invoke(o.lft, o.rght));
}
}
像平常使用join一样使用:
var contents = list.LeftOuterJoin(list2,
l => l.country,
r => r.name,
(l, r) => new { count = l.Count(), l.country, l.reason, r.people })
希望这能为您节省一些时间。
看看这个例子
class Person
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Phone { get; set; }
}
class Pet
{
public string Name { get; set; }
public Person Owner { get; set; }
}
public static void LeftOuterJoinExample()
{
Person magnus = new Person {ID = 1, FirstName = "Magnus", LastName = "Hedlund"};
Person terry = new Person {ID = 2, FirstName = "Terry", LastName = "Adams"};
Person charlotte = new Person {ID = 3, FirstName = "Charlotte", LastName = "Weiss"};
Person arlene = new Person {ID = 4, FirstName = "Arlene", LastName = "Huff"};
Pet barley = new Pet {Name = "Barley", Owner = terry};
Pet boots = new Pet {Name = "Boots", Owner = terry};
Pet whiskers = new Pet {Name = "Whiskers", Owner = charlotte};
Pet bluemoon = new Pet {Name = "Blue Moon", Owner = terry};
Pet daisy = new Pet {Name = "Daisy", Owner = magnus};
// Create two lists.
List<Person> people = new List<Person> {magnus, terry, charlotte, arlene};
List<Pet> pets = new List<Pet> {barley, boots, whiskers, bluemoon, daisy};
var query = from person in people
where person.ID == 4
join pet in pets on person equals pet.Owner into personpets
from petOrNull in personpets.DefaultIfEmpty()
select new { Person=person, Pet = petOrNull};
foreach (var v in query )
{
Console.WriteLine("{0,-15}{1}", v.Person.FirstName + ":", (v.Pet == null ? "Does not Exist" : v.Pet.Name));
}
}
// This code produces the following output:
//
// Magnus: Daisy
// Terry: Barley
// Terry: Boots
// Terry: Blue Moon
// Charlotte: Whiskers
// Arlene:
现在你可以从左边包含元素,即使那个元素在右边没有匹配,在我们的例子中,我们检索了Arlene,即使他在右边没有匹配
这是参考资料
如何:执行左外连接(c#编程指南)
如果需要连接和筛选某些东西,可以在连接之外完成。可以在创建集合之后进行筛选。
在这种情况下,如果我在连接条件中这样做,我减少了返回的行。
使用三元条件(= 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);