做一个LEFT OUTER JOIN在LINQ查询

Question

我有下面的查询中sqllite工作与左外连接

 select * from customer cu 
     inner join contract cnt on cu.customerId = cnt.customerid 
     inner join address addy on cu.addressid = addy.addressId 
     inner join csrAssoc cassc on cu.customerid = cassc.customerId 
     left outer join CustomerServiceRepresentative csrr on cassc.csrid = csrr.customerservicerepresentativeId 
     inner join customerServiceManager csmm on cassc.csmid = csmm.customerservicemanagerId 
     where cu.customernumber = '22222234' 

我希望能够在LINQ申请一个左外连接在这条线查询低于

 join csrr in objCsrCustServRep.AsEnumerable() on cassc.CsrId equals 
            csrr.CustomerServiceRepresentativeId 

     VisitRepData = (from cu in objCustomer.AsEnumerable() 
           join cnt in objContract.AsEnumerable() on cu.customerId equals cnt.customerId 
           join addy in objAddress.AsEnumerable() on cu.addressId equals addy.addressId 
           join cassc in objCsrAssoc.AsEnumerable() on cu.customerId equals cassc.CustomerId 
           join csrr in objCsrCustServRep.AsEnumerable() on cassc.CsrId equals 
            csrr.CustomerServiceRepresentativeId 
           join csmm in objCustServMan on cassc.CsmId.ToString() equals csmm.customerServiceManagerId 
           where cu.CustomerNumber == (customernbr) 

如何在linq查询中执行左外连接？

这是我的意见，调整和运行代码后。另一部分也被添加。我所得到的是对象没有设置为对象的实例。

  var VisitRepData = from cu in objCustomer.AsEnumerable() 
           join cnt in objContract.AsEnumerable() on cu.customerId equals cnt.customerId 
           join addy in objAddress.AsEnumerable() on cu.addressId equals addy.addressId 
           join cassc in objCsrAssoc.AsEnumerable() on cu.customerId equals cassc.CustomerId 
           join csrr in objCsrCustServRep.AsEnumerable() on cassc.CsrId equals 
            csrr.CustomerServiceRepresentativeId into temp 
           from tempItem in temp.DefaultIfEmpty() 
           join csmm in objCustServMan on cassc.CsmId.ToString() equals csmm.customerServiceManagerId 
           where cu.CustomerNumber == (customernbr) 
           select new 
           { 
            cu.customerId, 
            cu.CustomerNumber, 
            cu.customerName, 
            cu.dateActive, 
            cnt.contractExpirationDate, 
            addy.street, 
            addy.street2, 
            addy.city, 
            addy.state, 
            addy.zipcode, 
            cu.EMail, 
            cu.phoneNo, 
            cu.faxNumber, 
            csmm.customerServiceManagerName, 
            tempItem.CustomerServiceRepresentativeName, 
           }; 

          foreach (var item in VisitRepData) 
          { 
           var one = item.customerId; 
           var two = item.CustomerNumber; 
          } 

Answer 1

每documentation：

左外连接是一个连接，其中第一集合中的每个元素被返回，而不管其是否具有所述第二集合中的任何相关的元件。 可以使用LINQ通过调用上的一组结果的DefaultIfEmpty方法执行左外连接连接

（重点煤矿）基于MSDN链接

以上，如果我理解您的要求正确，查询应该是这样的：

VisitRepData = from cu in objCustomer.AsEnumerable() 
          join cnt in objContract.AsEnumerable() on cu.customerId equals cnt.customerId 
          join addy in objAddress.AsEnumerable() on cu.addressId equals addy.addressId 
          join cassc in objCsrAssoc.AsEnumerable() on cu.customerId equals cassc.CustomerId 
          join csrr in objCsrCustServRep.AsEnumerable() on cassc.CsrId equals 
           csrr.CustomerServiceRepresentativeId into temp 
          from tempItem in temp.DefaultIfEmpty() 
          join csmm in objCustServMan on cassc.CsmId.ToString() equals csmm.customerServiceManagerId 
          where cu.CustomerNumber == (customernbr) 

具体来说，左外连接与该代码执行：

join csrr in objCsrCustServRep.AsEnumerable() 
    on cassc.CsrId equals csrr.CustomerServiceRepresentativeId 
    into temp 
from tempItem in temp.DefaultIfEmpty()