是否有()返回预期结果的替代方案？
提问于 2016-01-07 07:53:59
Edit2与一些团队成员一起找到了解决方案。请看我对决议的评论。
看了看，似乎找不到我问题的真正答案。
我试图找到所有拥有组类型为空/空/空的组的用户。代码：
expression = (x => x.PersonGroups.Any(y => y.Group.GroupType == null || y.Group.GroupType == ""));
return expression我们的数据库中有许多用户应该返回这个表达式。相反，它不返回任何。一位比我更聪明、更有经验的同事昨天和我一起看了生成的SQL，他得出的结论是，这是LINQ和实体没有很好地合作的一个很好的例子。正在查询的实体被正确映射到数据库。我已经检查过了，这段代码肯定是执行此操作的代码(无论是在之前还是之后)。最让人困惑的是，我们的代码中还有其他类似于返回预期结果的linq查询。
所以有两个问题：
你看到我做错了什么吗？
( 2)不编写原始SQL，可能的替代方案是什么？我一直在尽可能多地阅读LINQ，但我确信我遗漏了一些东西。
谢谢!
(注意:我还没有找到这些帖子来回答我的问题：1，2，3，4)
Edit1:生成的sql代码。
SELECT
[UnionAll4].[C2] AS [C1],
[UnionAll4].[C218] AS [C218]
FROM (SELECT
[UnionAll3].[C1] AS [C1],
[UnionAll3].[C2] AS [C2],
[UnionAll3].[C193] AS [C218]
FROM (SELECT
CASE WHEN ([UnionAll1].[Person1Id1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Project6].[C1] AS [C2],
CAST(NULL AS bit) AS [C193]
FROM (SELECT
[Project4].[Id] AS [Id],
[Project4].[ProfilePicturePath] AS [ProfilePicturePath],
1 AS [C1]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[ProfilePicturePath] AS [ProfilePicturePath],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[pPeople_Disciplines] AS [Extent4]
WHERE [Extent1].[Id] = [Extent4].[People_ID]) AS [C1]
FROM [dbo].[Person] AS [Extent1]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent2].[Id] AS [Id],
[Extent2].[Discipline] AS [Discipline]
FROM [dbo].[pPeople_Disciplines] AS [Extent2]
WHERE [Extent1].[Id] = [Extent2].[People_ID]
) AS [Project1]
WHERE (LTRIM(RTRIM([Project1].[Discipline])) IN (N'RR', N'IAR', N'INS', N'TAX', N'TRST', N'MRTG', N'BD', N'RIA')) AND (LTRIM(RTRIM([Project1].[Discipline])) IS NOT NULL) AND ((@p__linq__0 = 1) OR (@p__linq__1 = 1) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[pPeople_Discipline_Agents] AS [Extent3]
WHERE ([Project1].[Id] = [Extent3].[People_Discipline_ID]) AND ([Extent3].[Agent_ID] IN (cast(5307 as bigint), cast(241 as bigint), cast(242 as bigint), cast(271 as bigint), cast(274 as bigint), cast(275 as bigint), cast(276 as bigint), cast(373 as bigint), cast(543 as bigint), cast(754 as bigint), cast(760 as bigint), cast(761 as bigint), cast(793 as bigint), cast(794 as bigint), cast(795 as bigint), cast(796 as bigint), cast(832 as bigint), cast(833 as bigint), cast(834 as bigint), cast(938 as bigint), cast(1370 as bigint), cast(1580 as bigint), cast(1733 as bigint), cast(1809 as bigint), cast(1857 as bigint), cast(1903 as bigint), cast(1951 as bigint), cast(2265 as bigint), cast(2391 as bigint), cast(2397 as bigint), cast(2423 as bigint), cast(2556 as bigint), cast(2567 as bigint), cast(2581 as bigint), cast(2743 as bigint), cast(2744 as bigint), cast(2745 as bigint), cast(2819 as bigint), cast(3153 as bigint), cast(3162 as bigint), cast(3166 as bigint), cast(3212 as bigint), cast(3227 as bigint), cast(3398 as bigint), cast(3634 as bigint), cast(3722 as bigint), cast(3757 as bigint), cast(3820 as bigint), cast(3862 as bigint), cast(3871 as bigint), cast(4393 as bigint), cast(4461 as bigint), cast(4565 as bigint), cast(4592 as bigint), cast(4742 as bigint), cast(4955 as bigint), cast(5002 as bigint), cast(5104 as bigint), cast(5234 as bigint), cast(5254 as bigint), cast(5306 as bigint), cast(5321 as bigint), cast(5322 as bigint)))
)))
)) AND ([Extent1].[HoldingCompanyId] = @p__linq__2) AND ([Extent1].[ConfirmationNeededTypeId] IS NULL)
) AS [Project4]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[pPeople_Groups] AS [Extent5]
INNER JOIN [dbo].[Groups] AS [Extent6] ON [Extent5].[Group_ID] = [Extent6].[Id]
WHERE ([Project4].[Id] = [Extent5].[People_ID]) AND ((LEN([Extent6].[Group_Type])) = 0)
)) AND (1 = CAST( [Project4].[PersonStatusTypeId] AS int)) AND ([Project4].[IsDeleted] <> cast(1 as bit)) AND ([Project4].[C1] > 0) ) AS [Project6]
OUTER APPLY (SELECT
CASE WHEN ([Extent11].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Extent8].[Person1Id] AS [Person1Id],
CAST(NULL AS varchar(1)) AS [C11]
FROM [dbo].[PersonRelationship] AS [Extent7]
LEFT OUTER JOIN [dbo].[PersonRelationshipMarriageDetail] AS [Extent8] ON ([Extent7].[RelationshipTypeId] = [Extent8].[RelationshipTypeId]) AND ([Extent7].[Person2Id] = [Extent8].[Person2Id]) AND ([Extent7].[Person1Id] = [Extent8].[Person1Id])
INNER JOIN [dbo].[Person] AS [Extent9] ON [Extent7].[Person1Id] = [Extent9].[Id]
INNER JOIN [dbo].[Person] AS [Extent10] ON [Extent7].[Person2Id] = [Extent10].[Id]
LEFT OUTER JOIN [dbo].[pPeople_Disciplines] AS [Extent11] ON [Extent11].[People_ID] = [Extent7].[Person1Id]
WHERE [Project6].[Id] = [Extent7].[Person1Id]
UNION ALL
SELECT
2 AS [C1],
[Extent13].[Person1Id] AS [Person1Id],
[Extent16].[Level] AS [Level]
FROM [dbo].[PersonRelationship] AS [Extent12]
LEFT OUTER JOIN [dbo].[PersonRelationshipMarriageDetail] AS [Extent13] ON ([Extent12].[RelationshipTypeId] = [Extent13].[RelationshipTypeId]) AND ([Extent12].[Person2Id] = [Extent13].[Person2Id]) AND ([Extent12].[Person1Id] = [Extent13].[Person1Id])
INNER JOIN [dbo].[Person] AS [Extent14] ON [Extent12].[Person1Id] = [Extent14].[Id]
INNER JOIN [dbo].[Person] AS [Extent15] ON [Extent12].[Person2Id] = [Extent15].[Id]
INNER JOIN [dbo].[pPeople_Disciplines] AS [Extent16] ON ([Extent12].[Person2Id] = [Extent15].[Id]) AND ([Extent16].[People_ID] = [Extent15].[Id])
WHERE [Project6].[Id] = [Extent12].[Person1Id]) AS [UnionAll1]
UNION ALL
SELECT
2 AS [C1],
[Project15].[C1] AS [C2],
CAST(NULL AS varchar(1)) AS [C192],
CAST(NULL AS bit) AS [C193]
FROM (SELECT
[Project13].[Id] AS [Id],
[Project13].[ProfilePicturePath] AS [ProfilePicturePath],
1 AS [C1]
FROM ( SELECT
[Extent17].[Id] AS [Id],
[Extent17].[ProfilePicturePath] AS [ProfilePicturePath],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[pPeople_Disciplines] AS [Extent20]
WHERE [Extent17].[Id] = [Extent20].[People_ID]) AS [C1]
FROM [dbo].[Person] AS [Extent17]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent18].[Id] AS [Id],
[Extent18].[Discipline] AS [Discipline]
FROM [dbo].[pPeople_Disciplines] AS [Extent18]
WHERE [Extent17].[Id] = [Extent18].[People_ID]
) AS [Project10]
WHERE (LTRIM(RTRIM([Project10].[Discipline])) IN (N'RR', N'IAR', N'INS', N'TAX', N'TRST', N'MRTG', N'BD', N'RIA')) AND (LTRIM(RTRIM([Project10].[Discipline])) IS NOT NULL) AND ((@p__linq__0 = 1) OR (@p__linq__1 = 1) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[pPeople_Discipline_Agents] AS [Extent19]
WHERE ([Project10].[Id] = [Extent19].[People_Discipline_ID]) AND ([Extent19].[Agent_ID] IN (cast(5307 as bigint), cast(241 as bigint), cast(242 as bigint), cast(271 as bigint), cast(274 as bigint), cast(275 as bigint), cast(276 as bigint), cast(373 as bigint), cast(543 as bigint), cast(754 as bigint), cast(760 as bigint), cast(761 as bigint), cast(793 as bigint), cast(794 as bigint), cast(795 as bigint), cast(796 as bigint), cast(832 as bigint), cast(833 as bigint), cast(834 as bigint), cast(938 as bigint), cast(1370 as bigint), cast(1580 as bigint), cast(1733 as bigint), cast(1809 as bigint), cast(1857 as bigint), cast(1903 as bigint), cast(1951 as bigint), cast(2265 as bigint), cast(2391 as bigint), cast(2397 as bigint), cast(2423 as bigint), cast(2556 as bigint), cast(2567 as bigint), cast(2581 as bigint), cast(2743 as bigint), cast(2744 as bigint), cast(2745 as bigint), cast(2819 as bigint), cast(3153 as bigint), cast(3162 as bigint), cast(3166 as bigint), cast(3212 as bigint), cast(3227 as bigint), cast(3398 as bigint), cast(3634 as bigint), cast(3722 as bigint), cast(3757 as bigint), cast(3820 as bigint), cast(3862 as bigint), cast(3871 as bigint), cast(4393 as bigint), cast(4461 as bigint), cast(4565 as bigint), cast(4592 as bigint), cast(4742 as bigint), cast(4955 as bigint), cast(5002 as bigint), cast(5104 as bigint), cast(5234 as bigint), cast(5254 as bigint), cast(5306 as bigint), cast(5321 as bigint), cast(5322 as bigint)))
)))
)) AND ([Extent17].[HoldingCompanyId] = @p__linq__2) AND ([Extent17].[ConfirmationNeededTypeId] IS NULL)
) AS [Project13]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[pPeople_Groups] AS [Extent21]
INNER JOIN [dbo].[Groups] AS [Extent22] ON [Extent21].[Group_ID] = [Extent22].[Id]
WHERE ([Project13].[Id] = [Extent21].[People_ID]) AND ((LEN([Extent22].[Group_Type])) = 0)
)) AND (1 = CAST( [Project13].[PersonStatusTypeId] AS int)) AND ([Project13].[IsDeleted] <> cast(1 as bit)) AND ([Project13].[C1] > 0) ) AS [Project15]
CROSS APPLY (SELECT
CASE WHEN ([Extent27].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Extent24].[Person1Id] AS [Person1Id],
--many 'extents' edited out from here
FROM [dbo].[PersonRelationship] AS [Extent23]
LEFT OUTER JOIN [dbo].[PersonRelationshipMarriageDetail] AS [Extent24] ON ([Extent23].[RelationshipTypeId] = [Extent24].[RelationshipTypeId]) AND ([Extent23].[Person2Id] = [Extent24].[Person2Id]) AND ([Extent23].[Person1Id] = [Extent24].[Person1Id])
INNER JOIN [dbo].[Person] AS [Extent25] ON [Extent23].[Person1Id] = [Extent25].[Id]
INNER JOIN [dbo].[Person] AS [Extent26] ON [Extent23].[Person2Id] = [Extent26].[Id]
LEFT OUTER JOIN [dbo].[pPeople_Disciplines] AS [Extent27] ON [Extent27].[People_ID] = [Extent23].[Person1Id]
WHERE [Project15].[Id] = [Extent23].[Person2Id]
UNION ALL
SELECT
2 AS [C1],
[Extent29].[Person1Id] AS [Person1Id],
--many 'extents' edited out from here
[Extent32].[Level] AS [Level]
FROM [dbo].[PersonRelationship] AS [Extent28]
LEFT OUTER JOIN [dbo].[PersonRelationshipMarriageDetail] AS [Extent29] ON ([Extent28].[RelationshipTypeId] = [Extent29].[RelationshipTypeId]) AND ([Extent28].[Person2Id] = [Extent29].[Person2Id]) AND ([Extent28].[Person1Id] = [Extent29].[Person1Id])
INNER JOIN [dbo].[Person] AS [Extent30] ON [Extent28].[Person1Id] = [Extent30].[Id]
INNER JOIN [dbo].[Person] AS [Extent31] ON [Extent28].[Person2Id] = [Extent31].[Id]
INNER JOIN [dbo].[pPeople_Disciplines] AS [Extent32] ON ([Extent28].[Person2Id] = [Extent31].[Id]) AND ([Extent32].[People_ID] = [Extent31].[Id])
WHERE [Project15].[Id] = [Extent28].[Person2Id]) AS [UnionAll2]) AS [UnionAll3]
UNION ALL
SELECT
3 AS [C1],
3 AS [C2],
[Project23].[Id] AS [Id],
[Join19].[Global] AS [Global]
FROM (SELECT
[Extent33].[Id] AS [Id],
[Extent33].[ProfilePicturePath] AS [ProfilePicturePath],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[pPeople_Disciplines] AS [Extent36]
WHERE [Extent33].[Id] = [Extent36].[People_ID]) AS [C1]
FROM [dbo].[Person] AS [Extent33]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent34].[Id] AS [Id],
[Extent34].[Discipline] AS [Discipline]
FROM [dbo].[pPeople_Disciplines] AS [Extent34]
WHERE [Extent33].[Id] = [Extent34].[People_ID]
) AS [Project20]
WHERE (LTRIM(RTRIM([Project20].[Discipline])) IN (N'RR', N'IAR', N'INS', N'TAX', N'TRST', N'MRTG', N'BD', N'RIA')) AND (LTRIM(RTRIM([Project20].[Discipline])) IS NOT NULL) AND ((@p__linq__0 = 1) OR (@p__linq__1 = 1) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[pPeople_Discipline_Agents] AS [Extent35]
WHERE ([Project20].[Id] = [Extent35].[People_Discipline_ID]) AND ([Extent35].[Agent_ID] IN (cast(5307 as bigint), cast(241 as bigint), cast(242 as bigint), cast(271 as bigint), cast(274 as bigint), cast(275 as bigint), cast(276 as bigint), cast(373 as bigint), cast(543 as bigint), cast(754 as bigint), cast(760 as bigint), cast(761 as bigint), cast(793 as bigint), cast(794 as bigint), cast(795 as bigint), cast(796 as bigint), cast(832 as bigint), cast(833 as bigint), cast(834 as bigint), cast(938 as bigint), cast(1370 as bigint), cast(1580 as bigint), cast(1733 as bigint), cast(1809 as bigint), cast(1857 as bigint), cast(1903 as bigint), cast(1951 as bigint), cast(2265 as bigint), cast(2391 as bigint), cast(2397 as bigint), cast(2423 as bigint), cast(2556 as bigint), cast(2567 as bigint), cast(2581 as bigint), cast(2743 as bigint), cast(2744 as bigint), cast(2745 as bigint), cast(2819 as bigint), cast(3153 as bigint), cast(3162 as bigint), cast(3166 as bigint), cast(3212 as bigint), cast(3227 as bigint), cast(3398 as bigint), cast(3634 as bigint), cast(3722 as bigint), cast(3757 as bigint), cast(3820 as bigint), cast(3862 as bigint), cast(3871 as bigint), cast(4393 as bigint), cast(4461 as bigint), cast(4565 as bigint), cast(4592 as bigint), cast(4742 as bigint), cast(4955 as bigint), cast(5002 as bigint), cast(5104 as bigint), cast(5234 as bigint), cast(5254 as bigint), cast(5306 as bigint), cast(5321 as bigint), cast(5322 as bigint)))
)))
)) AND ([Extent33].[HoldingCompanyId] = @p__linq__2) AND ([Extent33].[ConfirmationNeededTypeId] IS NULL) ) AS [Project23]
INNER JOIN (SELECT [Extent37].[Id] AS [Id1], [Extent37].[People_ID] AS [People_ID], [Extent37].[Group_ID] AS [Group_ID], [Extent37].[Remarks] AS [Remarks], [Extent37].[Start_Date] AS [Start_Date], [Extent37].[End_Date] AS [End_Date], [Extent37].[Status] AS [Status], [Extent38].[Id] AS [Id2], [Extent38].[Agency_ID] AS [Agency_ID], [Extent38].[Agent_ID] AS [Agent_ID], [Extent38].[Name] AS [Name], [Extent38].[Description] AS [Description], [Extent38].[Deleted] AS [Deleted], [Extent38].[User_Created] AS [User_Created], [Extent38].[Group_Type] AS [Group_Type], [Extent38].[Global] AS [Global]
FROM [dbo].[pPeople_Groups] AS [Extent37]
INNER JOIN [dbo].[Groups] AS [Extent38] ON [Extent37].[Group_ID] = [Extent38].[Id] ) AS [Join19] ON [Project23].[Id] = [Join19].[People_ID]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[pPeople_Groups] AS [Extent39]
INNER JOIN [dbo].[Groups] AS [Extent40] ON [Extent39].[Group_ID] = [Extent40].[Id]
WHERE ([Project23].[Id] = [Extent39].[People_ID]) AND ((LEN([Extent40].[Group_Type])) = 0)
)) AND (1 = CAST( [Project23].[PersonStatusTypeId] AS int)) AND ([Project23].[IsDeleted] <> cast(1 as bit)) AND ([Project23].[C1] > 0)) AS [UnionAll4]
ORDER BY [UnionAll4].[C3] ASC, [UnionAll4].[C1] ASC, [UnionAll4].[C28] ASC, [UnionAll4].[C29] ASC, [UnionAll4].[C30] ASC, [UnionAll4].[C34] ASC, [UnionAll4].[C37] ASC, [UnionAll4].[C38] ASC, [UnionAll4].[C45] ASC, [UnionAll4].[C46] ASC, [UnionAll4].[C47] ASC, [UnionAll4].[C49] ASC, [UnionAll4].[C75] ASC, [UnionAll4].[C100] ASC, [UnionAll4].[C115] ASC, [UnionAll4].[C116] ASC, [UnionAll4].[C117] ASC, [UnionAll4].[C121] ASC, [UnionAll4].[C124] ASC, [UnionAll4].[C125] ASC, [UnionAll4].[C132] ASC, [UnionAll4].[C133] ASC, [UnionAll4].[C134] ASC, [UnionAll4].[C136] ASC, [UnionAll4].[C162] ASC, [UnionAll4].[C187] ASC
回答 3
Stack Overflow用户
回答已采纳
发布于 2016-01-08 07:18:50
毕竟，问题确实存在于我们的地图中。
默认情况下，实体框架将所有字段标记为可选字段。但是在我们的示例中，我们的团队自定义了设置实体，在此期间，他们将所有字段设置为我们上下文中所需的字段。这是问题的症结所在；当数据映射中的字段被标记为必需时，实体在执行LINQ查询时跳过检查表中的空值。从我的问题中可以看出，空字段是我正在寻找的一些东西--在我的例子中有很多。因此，要解决这个问题，我只需在数据映射中将该字段标记为可选字段。因为实体知道要检查它是否为空字段，所以它按预期的方式运行。
Tl;dr:如果您有类似的问题，请检查您的conext和数据映射，以确保数据库中的可空字段在数据映射中也被标记为可选字段。否则，实体将不会检查它是否为空。
Stack Overflow用户
发布于 2016-01-07 08:06:23
要获取PersonGroup对象的列表，请使用Where而不是Any
expression = (x => x.PersonGroups.Where(y => y.Group.GroupType == null || y.Group.GroupType == ""));
return expressionStack Overflow用户
发布于 2016-01-07 08:04:21
PersonGroups集合，任何将只返回真假基于您的条件。如果集合包含PersonGroup -> group.GroupType == null或空中的任何一项，则它将返回true else false。
你的期望结果是对还是错？
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34659508
复制相关文章
腾讯云开发者
