LINQ左连接中的FirstOrDefault()

Question

我试图设置一个左连接查询，我只是从订单表中拉FirstOrDefault记录，但它不工作的方式，我有它，这甚至可能使用LINQ?

var customerOrder = (from customer in customers
join orderJoin in orders.FirstOrDefault(x => x.CustomerId == customer.CustomerId) on customer.CustomerId equals orderJoin.CustomerId
join shippingJoin in shipping on customer.CustomerId equals shippingJoin.CustomerId into shippingGroup
from shipping in shippingGroup.DefaultIfEmpty()
select new
{
Customer.CustomerId,
Order = orderJoin,
Shipping = shipping
}).ToList();

Answer 1

将所有连接设置为左连接并检查是否有可空值

var query = from company in companies
join product in products on company.CompanyId equals product.CompanyId into productleftjoin
from product in productleftjoin.DefaultIfEmpty()
join transaction in transactions on product.ProductId equals transaction.ProductId into transactionleftjoin
from transaction in transactionleftjoin.DefaultIfEmpty()
where transaction?.Cost * transaction?.Quantity>0
select new { company.CompanyName,productName=product.Name, extendPrice=transaction?.Cost* transaction?.Quantity };

Answer 2

您不能使用firstordefault作为查询的源。Take(1).DefaultIfEmpty()还有另一种技术。请注意，我添加了默认的OrderBy，因为数据库在这种情况下不保证记录顺序，可能应该使用DateCreated或类似的东西。

var customerOrder = (
from customer in customers
from orderJoin in orders
.Where(x => x.CustomerId == customer.CustomerId)
.OrderByDescending(x => x.Id)
.Take(1)
.DefaultIfEmpty()
join shippingJoin in shipping on customer.CustomerId equals shippingJoin.CustomerId into shippingGroup
from shipping in shippingGroup.DefaultIfEmpty()
select new
{
Customer.CustomerId,
Order = orderJoin,
Shipping = shipping
}).ToList();