如何在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，以防您以后需要对它进行进一步的逻辑操作。
概述:在这个代码片段中，我演示了如何按ID分组，其中Table1和Table2具有一对多关系。我集中在
Id, Field1和Field2。如果需要第三次查找表，并且需要左连接关系，那么子查询是有用的。
我展示了一个左连接分组和一个子查询linq。结果是等价的。
class MyView
{
public integer Id {get,set};
public String Field1 {get;set;}
public String Field2 {get;set;}
public String SubQueryName {get;set;}
}
IList<MyView> list = await (from ci in _dbContext.Table1
join cii in _dbContext.Table2
on ci.Id equals cii.Id
where ci.Field1 == criterion
group new
{
ci.Id
} by new { ci.Id, cii.Field1, ci.Field2}
into pg
select new MyView
{
Id = pg.Key.Id,
Field1 = pg.Key.Field1,
Field2 = pg.Key.Field2,
SubQueryName=
(from chv in _dbContext.Table3 where chv.Id==pg.Key.Id select chv.Field1).FirstOrDefault()
}).ToListAsync<MyView>();
Compared to using a Left Join and Group new
IList<MyView> list = await (from ci in _dbContext.Table1
join cii in _dbContext.Table2
on ci.Id equals cii.Id
join chv in _dbContext.Table3
on cii.Id equals chv.Id into lf_chv
from chv in lf_chv.DefaultIfEmpty()
where ci.Field1 == criterion
group new
{
ci.Id
} by new { ci.Id, cii.Field1, ci.Field2, chv.FieldValue}
into pg
select new MyView
{
Id = pg.Key.Id,
Field1 = pg.Key.Field1,
Field2 = pg.Key.Field2,
SubQueryName=pg.Key.FieldValue
}).ToListAsync<MyView>();
我想补充的是，如果您获得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，以防您以后需要对它进行进一步的逻辑操作。
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; }
}
输出
使用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
});
根据我对类似问题的回答，在这里:
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,
});
