使用扩展方法linq创建左连接查询和内连接查询

Question

    select *
From Item i inner join Order o on i.OrderId = o.Id
        left join OrderDealersByDistance d on o.Id = d.OrderId
        left join SubOrder s on i.SubOrderId = s.Id
where d.[Status] = 1

我想用实体框架写上面的查询。我更喜欢DataContext和扩展方法。我怎么写上面的查询?

Answer 1

首先，我会使用查询语法，因为它更具可读性。

它看起来像这样

var db = new MyDbContext(conStr);
var query = from i in db.Items
     from o in db.Orders.Where(o => o.Id == i.OrderId)
     from d in db.OrderDealersByDistance.Where(d => d.OrderId == o.Id).DefaultIfEmpty()
     from s in db.SubOrders.Where(s => i.SubOrderId == s.Id).DefaultIfEmpty()
     where d.Status == 1
     select new { Item = i, Order = o, c.ContractName, Distance = d, SubOrder = s };
var result = query.ToList();

DefaultIfEmpty()方法给你一个左连接。您可能需要更具体地说明选择列表(命名列而不是'*')。