Linq to SQL - 确切的关系划分
本文关键字：关系 划分 to SQL Linq | 更新日期: 2023-09-27 17:55:59
下面的Linq-to-SQL查询当前导致：
NotSupportedException：本地序列不能在查询运算符的 LINQ to SQL 实现中使用，但包含运算符除外。
看起来在使用 linq to SQL 时，在 linq 中执行精确关系划分筛选器的典型方法不可用。
在下面的查询中，我们看到两次尝试使用 Except 子句筛选出在提供范围内没有 ID 的任何记录。
有谁知道除了更改为使用存储过程或推迟 Expect 过滤器以进一步筛选返回的列表之外还需要做什么？
该部分具体是
&&
(additionalServiceIDs == null || additionalServiceIDs.Count == 0 ||
!(AdditionalServices.Where(ads => p.ReferenceNumber == ads.FK_Provider_ReferenceNumber).Select(ad => ad.FK_ServiceList_ID).Except(additionalServiceIDs).Any())
) // *ONLY* records that match the provided list of additionalServiceIDs, no more - no less (aka. Exact Relational Division)
&&
(additionalServiceIDs == null || additionalServiceIDs.Count == 0 ||
!(AdditionalNeeds.Where(adn => p.ReferenceNumber == adn.FK_Provider_ReferenceNumber).Select(an => an.FK_ServiceList_ID).Except(additionalNeedsIDs).Any())
) // *ONLY* records that match the provided list of additionalServiceIDs, no more - no less (aka. Exact Relational Division)
在：
List<int> additionalServiceIDs = new List<int>(){40,42};
List<int> establishmentIDs = new List<int>() {};
List<int> additionalNeedsIDs = new List<int>(){};
bool advertisedOnly = true;
bool Internal = true;
string providerTypeCode = "";
List<string> settingTowns = new List<string>() {};
var sqlResultsList = (from p in Providers
join po in ProviderOverviews on p.ReferenceNumber equals po.FK_Provider_ReferenceNumber into p_po
from p_po_LeftOuter in p_po.DefaultIfEmpty() // left outer join
join ad in AdditionalServices on p.ReferenceNumber equals ad.FK_Provider_ReferenceNumber into p_ad
from p_ad_LeftOuter in p_ad.DefaultIfEmpty() // left outer join
join sp in SchoolPickUps on p.ReferenceNumber equals sp.FK_Provider_ReferenceNumber into p_sp
from p_sp_LeftOuter in p_sp.DefaultIfEmpty() // left outer join
join an in AdditionalNeeds on p.ReferenceNumber equals an.FK_Provider_ReferenceNumber into p_an
from p_an_LeftOuter in p_an.DefaultIfEmpty() // left outer join
join il in Inspections on p.ReferenceNumber equals il.FK_Provider_ReferenceNumber into p_il
from p_il_LeftOuter in p_il.DefaultIfEmpty() // left outer join
join sl in SchoolLists on p_sp_LeftOuter.FK_SchoolList_SchoolID equals sl.SchoolID into sp_sl
from sp_sl_LeftOuter in sp_sl.DefaultIfEmpty() // left outer join
join nl in NeedLists on p_an_LeftOuter.FK_NeedsList_ID equals nl.ID into an_nl
from an_nl_LeftOuter in an_nl.DefaultIfEmpty() // left outer join
join svl in ServiceLists on p_ad_LeftOuter.FK_ServiceList_ID equals svl.ID into ad_svl
from ad_svl_LeftOuter in ad_svl.DefaultIfEmpty() // left outer join
join ptl in ProviderTypeLists on p.FK_ProviderTypeList_ID equals ptl.ID
where
(string.IsNullOrEmpty(providerTypeCode) || ptl.Code.ToLower() == providerTypeCode)
&&
p.Advertise == advertisedOnly
&&
(settingTowns == null || settingTowns.Count == 0 || settingTowns.Contains(p.SettingTown.ToLower())) // allow for no parameters being passed
&&
(establishmentIDs == null || establishmentIDs.Count == 0 || establishmentIDs.Contains(p_sp_LeftOuter.FK_SchoolList_SchoolID)) // allow for no parameters being passed
&&
(Internal == true || p.RegistrationStatus == "ACTV")// if internal = false then add filter .. RegistrationStatus = 'ACTV'
&&
(additionalServiceIDs == null || additionalServiceIDs.Count == 0 ||
!(AdditionalServices.Where(ads => p.ReferenceNumber == ads.FK_Provider_ReferenceNumber).Select(ad => ad.FK_ServiceList_ID).Except(additionalServiceIDs).Any())
) // *ONLY* records that match the provided list of additionalServiceIDs, no more - no less (aka. Exact Relational Division)
&&
(additionalNeedsIDs== null || additionalNeedsIDs.Count == 0 ||
!(AdditionalNeeds.Where(adn => p.ReferenceNumber == adn.FK_Provider_ReferenceNumber).Select(an => an.FK_NeedsList_ID).Except(additionalNeedsIDs).Any())
) // *ONLY* records that match the provided list of additionalServiceIDs, no more - no less (aka. Exact Relational Division)
select new // anonymous type
{
SettingTown = p.SettingTown,
ProviderTypeCode = ptl.Code,
ProviderName = p.ProviderName,
PublishedAddress = p_po_LeftOuter.PublishedAddressLocation,
Vacancies = p_po_LeftOuter.Vacancies,
VacancyMemo = p_po_LeftOuter.VacancyMemo,
PublishedPhone = p_po_LeftOuter.PublicPhone,
PublishedEmail = p_po_LeftOuter.PublicEmail,
Website = p_po_LeftOuter.Website,
AdditionalNeed = an_nl_LeftOuter.Description,
AdditionalNeedID = (int?)an_nl_LeftOuter.ID,
AdditionalService = ad_svl_LeftOuter.Description,
AdditionalServiceID = (int?)ad_svl_LeftOuter.ID,
CostPerDay = p_po_LeftOuter.CostPerDay,
CostPerHour = p_po_LeftOuter.CostPerHour,
CostPerSession = p_po_LeftOuter.CostPerSession,
Hours = p_po_LeftOuter.Hours,
InspectionOverallJudgement = p_il_LeftOuter.InspectionOVerallJudgement,
ReferenceNumber = p.ReferenceNumber,
ServiceDescription = p_po_LeftOuter.ServiceDescription,
SchoolPickUp = sp_sl_LeftOuter.SchoolName,
Under5 = p_po_LeftOuter.Under5,
Over5 = p_po_LeftOuter.Over5,
PublicTransport = p_po_LeftOuter.PublicTransport,
}).ToList();
错误消息表明，除了函数之外Contains()不能在 LINQ to SQL 中使用本地序列。基于该建议，尽量避免使用.Except()，改用.Contains()。您可以替换它：
.Except(localCollection)
有了这个：
.Where(!localCollection.Contains())
您的案例示例：
(additionalServiceIDs == null || additionalServiceIDs.Count == 0 ||
!(AdditionalNeeds.Where(adn => p.ReferenceNumber == adn.FK_Provider_ReferenceNumber)
.Select(an => an.FK_ServiceList_ID)
.Where(fk => !additionalNeedsIDs.Contains(fk))
.Any())
) // *ONLY* records that match the provided list of additionalServiceIDs, no more - no less (aka. Exact Relational Division)
或通过合并两个.Where() s 来简化：
(additionalServiceIDs == null || additionalServiceIDs.Count == 0 ||
!(AdditionalNeeds.Where(adn => p.ReferenceNumber == adn.FK_Provider_ReferenceNumber
&& !additionalNeedsIDs.Contains(adn.FK_ServiceList_ID))
.Select(an => an.FK_ServiceList_ID)
.Any())
) // *ONLY* records that match the provided list of additionalServiceIDs, no more - no less (aka. Exact Relational Division)
在@har07的帮助下，我完全弄清楚了，过滤器就是这样。
&&
(additionalServiceIDs == null || additionalServiceIDs.Count == 0 ||
!(dbo.AdditionalServices.Where(ads => p_ad_LeftOuter.FK_Provider_ReferenceNumber == ads.FK_Provider_ReferenceNumber
&& !additionalServiceIDs.Contains(ads.FK_ServiceList_ID)
)
.Select(an => an.FK_ServiceList_ID)
.Any())
&&
p_ad_LeftOuter.FK_ServiceList_ID != null
&&
dbo.AdditionalServices.Where(ads => p_ad_LeftOuter.FK_Provider_ReferenceNumber == ads.FK_Provider_ReferenceNumber
&& additionalServiceIDs.Contains(ads.FK_ServiceList_ID))
.Select(an => an.FK_ServiceList_ID)
.Distinct()
.Count() == additionalServiceIDs.Count
)
&&
(additionalNeedsIDs == null || additionalNeedsIDs.Count == 0 ||
!(dbo.AdditionalNeeds.Where(ads => p_an_LeftOuter.FK_Provider_ReferenceNumber == ads.FK_Provider_ReferenceNumber
&& !additionalNeedsIDs.Contains(ads.FK_NeedsList_ID)
)
.Select(an => an.FK_NeedsList_ID)
.Any())
&&
p_an_LeftOuter.FK_NeedsList_ID != null
&&
dbo.AdditionalNeeds.Where(ads => p_an_LeftOuter.FK_Provider_ReferenceNumber == ads.FK_Provider_ReferenceNumber
&& additionalNeedsIDs.Contains(ads.FK_NeedsList_ID))
.Select(an => an.FK_NeedsList_ID)
.Distinct()
.Count() == additionalNeedsIDs.Count
)