当在联合表上使用Where子句时，对Linq查询进行左连接

本文关键字：Linq 查询 连接 子句 Where | 更新日期: 2023-09-27 18:14:04

我知道这个问题已经被问过几次了，但我对如何为自己实现感到困惑。

我正在使用SQL Lite，我试图连接2个表，但有时'链接'行在第二表不存在。

这是我的代码:

return from address in DB.Connector.Table<InformedWorkerModel.Tables.Address>()
       join addressHistory in DB.Connector.Table<InformedWorkerModel.Tables.AddressHistory>() 
       on address.AddressRef equals addressHistory.AddressRef 
       where addressHistory.CustomerRef == customerRef 
       select new InformedWorkerModel.Tables.Address
       {
           Address1 = address.Address1,
           Address2 = address.Address2,
           Address3 = address.Address3,
           Town = address.Town,
           County = address.County,
           Country = address.Country,
           PostCode = address.PostCode,
           AddressRef = address.AddressRef,
           AddressId = address.AddressId
       };

在谷歌我可以看到一个解决方案，如果我不使用'where'子句。

当在联合表上使用Where子句时，对Linq查询进行左连接

您错过了DefaultIfEmpty()部分的使用，它将其转换为左连接(查看文档-它非常清楚地显示了如何执行所有不同的连接):

   from address in DB.Connector.Table<InformedWorkerModel.Tables.Address>()
   join addressHistory in (from x in DB.Connector.Table<InformedWorkerModel.Tables.AddressHistory>() 
                           where x.CustomerRef == customerRef
                           select x) 
   on address.AddressRef equals addressHistory.AddressRef into j
   from addressHistory in j.DefaultIfEmpty()
   select new InformedWorkerModel.Tables.Address
   {
       Address1 = address.Address1,
       Address2 = address.Address2,
       Address3 = address.Address3,
       Town = address.Town,
       County = address.County,
       Country = address.Country,
       PostCode = address.PostCode,
       AddressRef = address.AddressRef,
       AddressId = address.AddressId
   };

还可以看到我将addressHistory.CustomerRef == customerRef移动到一个嵌套的选择。如果你不这样做，那么你只会得到有值的行-其中有效地将其转化为"正常"inner join 。另一种方法是像前面的but:

where addressHistory == null || addressHistory.CustomerRef == customerRef

所以它看起来像:

   from address in DB.Connector.Table<InformedWorkerModel.Tables.Address>()
   join addressHistory in DB.Connector.Table<InformedWorkerModel.Tables.AddressHistory>() 
   on address.AddressRef equals addressHistory.AddressRef into j
   from addressHistory in j.DefaultIfEmpty()
   where addressHistory == null || addressHistory.CustomerRef == customerRef
   select new InformedWorkerModel.Tables.Address
   {
       Address1 = address.Address1,
       Address2 = address.Address2,
       Address3 = address.Address3,
       Town = address.Town,
       County = address.County,
       Country = address.Country,
       PostCode = address.PostCode,
       AddressRef = address.AddressRef,
       AddressId = address.AddressId
   };

如果您想返回数据，即使在addressHistory表中没有此地址的行，那么您需要执行相当于SQL "左连接"的操作:

join addressHistory in DB.Connector.Table<InformedWorkerModel.Tables.AddressHistory>()
     on address.AddressRef equals addressHistory.AddressRef
     into joinedAddressHistories
     from joinedHistory in joinedAddressHistories.DefaultIfEmpty()

但是，您还需要修改您的where子句，因为如果您尝试:

where joinedHistory.CustomerRef = customerRef

as joinedHistory将为空。在不知道你到底想要什么之前，我无法提供解决方案。

相关文章：