如何在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; }
如果需要连接和筛选某些东西，可以在连接之外完成。可以在创建集合之后进行筛选。
在这种情况下，如果我在连接条件中这样做，我减少了返回的行。
使用三元条件(= 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);
使用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
});
(from a in db.Assignments
join b in db.Deliveryboys on a.AssignTo equals b.EmployeeId
//from d in eGroup.DefaultIfEmpty()
join c in db.Deliveryboys on a.DeliverTo equals c.EmployeeId into eGroup2
from e in eGroup2.DefaultIfEmpty()
where (a.Collected == false)
select new
{
OrderId = a.OrderId,
DeliveryBoyID = a.AssignTo,
AssignedBoyName = b.Name,
Assigndate = a.Assigndate,
Collected = a.Collected,
CollectedDate = a.CollectedDate,
CollectionBagNo = a.CollectionBagNo,
DeliverTo = e == null ? "Null" : e.Name,
DeliverDate = a.DeliverDate,
DeliverBagNo = a.DeliverBagNo,
Delivered = a.Delivered
});
根据我对类似问题的回答，在这里:
Linq到SQL左外连接使用Lambda语法和连接2列(复合连接键)
获得代码在这里，或克隆我的github回购，并发挥!
查询:
var petOwners =
from person in People
join pet in Pets
on new
{
person.Id,
person.Age,
}
equals new
{
pet.Id,
Age = pet.Age * 2, // owner is twice age of pet
}
into pets
from pet in pets.DefaultIfEmpty()
select new PetOwner
{
Person = person,
Pet = pet,
};
λ:
var petOwners = People.GroupJoin(
Pets,
person => new { person.Id, person.Age },
pet => new { pet.Id, Age = pet.Age * 2 },
(person, pet) => new
{
Person = person,
Pets = pet,
}).SelectMany(
pet => pet.Pets.DefaultIfEmpty(),
(people, pet) => new
{
people.Person,
Pet = pet,
});
在linqc#中执行左外连接
//执行左外连接
class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
}
class Child
{
public string Name { get; set; }
public Person Owner { get; set; }
}
public class JoinTest
{
public static void LeftOuterJoinExample()
{
Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };
Child barley = new Child { Name = "Barley", Owner = terry };
Child boots = new Child { Name = "Boots", Owner = terry };
Child whiskers = new Child { Name = "Whiskers", Owner = charlotte };
Child bluemoon = new Child { Name = "Blue Moon", Owner = terry };
Child daisy = new Child { Name = "Daisy", Owner = magnus };
// Create two lists.
List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
List<Child> childs = new List<Child> { barley, boots, whiskers, bluemoon, daisy };
var query = from person in people
join child in childs
on person equals child.Owner into gj
from subpet in gj.DefaultIfEmpty()
select new
{
person.FirstName,
ChildName = subpet!=null? subpet.Name:"No Child"
};
// PetName = subpet?.Name ?? String.Empty };
foreach (var v in query)
{
Console.WriteLine($"{v.FirstName + ":",-25}{v.ChildName}");
}
}
// This code produces the following output:
//
// Magnus: Daisy
// Terry: Barley
// Terry: Boots
// Terry: Blue Moon
// Charlotte: Whiskers
// Arlene: No Child
https://dotnetwithhamid.blogspot.in/
