ASP.NET Core Controller外连接：无OrderItems时返回全部JobOrders记录

你的查询无法返回无关联OrderItems的JobOrders，核心原因是与OrderItems表使用了内连接，只有存在匹配条目时才会保留JobOrders记录。要实现需求，需要将这部分连接改为左外连接，同时处理空值情况：

1. 对OrderItems的连接添加into子句，并通过DefaultIfEmpty()实现左外连接
2. 求和Qty时，需判断OrderItems是否为null，避免空引用异常

修改后的完整代码如下：

var query = from order in DbContext.JobOrders.Where(i => i.Status == 1 && i.Facility == roleID.First().Facility)
            where order.OrderStat == apiID

            join users in DbContext.users on order.Uid equals users.Uid into onlineusers
            from onusers in onlineusers.DefaultIfEmpty()
            join customers in DbContext.customers on order.Uid equals customers.Uid into offlinecustomers
            from offusers in offlinecustomers.DefaultIfEmpty()
            // 修改为左外连接
            join oi in DbContext.OrderItems on order.Eid equals oi.OrderId into orderItemsGroup
            from oi in orderItemsGroup.DefaultIfEmpty()
            // 分组时处理oi为null的情况，确保分组键不受影响
            group oi by new { 
                order.Eid, 
                order.Uid, 
                order.OrderStat, 
                order.Date, 
                order.CollectionSlot, 
                Name = onusers.Name == null ? offusers.Name : onusers.Name, 
                Contact = onusers.Contact == null ? offusers.Contact : onusers.Contact 
            } into g
            select new
            {
                Eid = g.Key.Eid,
                Uid = g.Key.Uid,
                OrderStat = g.Key.OrderStat,
                Orderdate = g.Key.Date,
                CollectionSlot = g.Key.CollectionSlot,
                Name = g.Key.Name,
                Contact = g.Key.Contact,
                // 求和时判断oi是否为null，空值则按0计算
                Qty = g.Sum(x => x?.Qty ?? 0),
            };

var result = query.OrderByDescending(m => m.Eid).ToList();

return Ok(result);

内容的提问来源于stack exchange，提问作者Sulfy