左外连接 LINQ To Entities

Question

我有以下实体：

客户
-- 客户端ID
-- 客户名称

承包商
-- 承包商ID
-- 承包商名称

首选承包商
-- 首选承包商 ID
-- 客户端ID
-- ContractorID

所以我有一份客户和承包商的列表。与其他承包商相比，客户更喜欢与某些承包商合作。我想构建一个 LINQ to Entity 查询，该查询使用布尔字段来提取所有承包商，指示该承包商是否是首选。

 public IQueryable<PreferredContractor> GetPreferredContractors(int clientID)
    {
        var preferredContractors = from c in db.Contractors
                  from pc in db.PreferredContractors.DefaultIfEmpty()
                  select new PreferredContractor
                             {
                                 ContractorID = c.ContractorID,
                                 ContractorName = c.ContractorName,
                                 IsPreferred = // This is where I need help
                             };

        return preferredContractors;
    }

我如何确定承包商是否是首选承包商？

Answer 1

    var preferredContractors =
              from c in db.Contractors
              join pc in db.PreferredContractors.Where(pc2 => pc2.ClientId == clientId) on c.ContractorId equals pc.ContractorId into j
              from pc in j.DefaultIfEmpty()
              select new PreferredContractor
                         {
                             ContractorID = c.ContractorID,
                             ContractorName = c.ContractorName,
                             IsPreferred = pc != null
                         };