使用Linq和默认值的多重左联接

Question

我正试图在c#.netcore 2.2应用程序的linq中编写一个包含多个左联接的查询。我尝试过用linq编写查询，但它不能正确检索所有行。我试图转换的查询如下。

select   ISNULL(b.Id, '00000000-0000-0000-0000-000000000000') as 'Bat Id', p.Id as 'ProductId',  p.RP, u.UnitName as 'UnitName', ISNULL(b.QTY,0) as 'BusQty', p.[Name] as 'Product Name'
from  Products p left join Bat b
ON p.Id = b.ProductId
left join Units u on p.UOId = u.Id;

linq到目前为止我有

var allProducts = (from p in _db.Products
join s in _db.Bat on p.Id equals s.ProductId into ps
from s in ps.DefaultIfEmpty()
join u in _db.Units on p.UOId equals u.Id
select new
{
BatId = s.Id == null ? Guid.NewGuid() : s.Id,
RP = p.RP,
BusQty = s.QTY == null ? 0 : s.QTY,
ProductName = p.Name,
UnitName = u.UnitName,
ProductId = p.Id,
}).ToList();

Answer 1

您在Units上缺少DefaultIfEmpty()，从而将其变成内部联接

var allProducts = (
from p in _db.Products
join s in _db.Bat on p.Id equals s.ProductId into ps
from s in ps.DefaultIfEmpty()
join u in _db.Units on p.UOId equals u.Id into us
from u in us.DefaultIfEmpty()
select new
{
BatId = s.Id ?? Guid.NewGuid(),
RP = p.RP,
BusQty = s.QTY ?? 0,
ProductName = p.Name,
UnitName = u.UnitName,
ProductId = p.Id,
}).ToList();