代码之家 › 专栏 › 技术社区 › Kramlinger

Any()没有返回预期的结果--备选方案?

any linq-to-sql linq entity-framework .net

Kramlinger · 技术社区 · 9 年前

编辑2 与一些团队成员一起找到了解决方案。有关决议,请参阅我的评论。

看了之后,似乎找不到我问题的真正答案。

我正在尝试查找所有拥有组类型为blank/null/empty的组的用户。代码:

expression = (x => x.PersonGroups.Any(y => y.Group.GroupType == null || y.Group.GroupType == "")); 
return expression

我们的数据库中有许多用户需要此表达式返回。相反,它不会返回任何信息。一位比我聪明、经验丰富的同事昨天和我一起查看了生成的SQL,他得出的结论是,这是LINQ和Entity不能很好地协同工作的一个很好的例子。此查询所在的实体已正确映射到数据库。我已经检查过了,这段代码绝对是执行此操作的代码(不是之前或之后)。最令人困惑的是,我们的代码中还有其他linq查询,它们看起来与此查询非常相似,并返回预期结果。

所以有两个问题:

1) 你看到我做错了什么吗?

2) 如果不编写原始SQL,可能的替代方案是什么?我一直在尽可能多地阅读LINQ,但我肯定我错过了什么。

谢谢

(注意:我还没有找到这些帖子来回答我的问题: 1 , 2 , 3 , 4 )

编辑1: 生成的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 回复 | 直到 7 年前

user449689 9 年前

获取列表 PersonGroup 对象,使用 Where 而不是 Any :

expression = (x => x.PersonGroups.Where(y => y.Group.GroupType == null || y.Group.GroupType == "")); 
return expression

Kramlinger 9 年前

毕竟问题确实出在我们的地图上。

默认情况下,实体框架将所有字段标记为可选字段。但是在我们的例子中,我们的团队定制了Entity,在此期间,他们将所有字段设置为上下文中的必填字段。 这是问题的症结所在; 当数据映射中的某个字段标记为必需时,Entity在执行LINQ查询时只需跳过检查表中该字段的空值 正如你从我的问题中看到的,空字段是我正在搜索的一些确切的东西——在我的例子中有很多。因此,为了解决这个问题,我只在数据映射中将该字段标记为可选字段。因为Entity知道检查它的空字段,所以它按预期工作。

Tl;dr:如果您有类似的问题,请检查您的conext和数据映射,以确保数据库中可为空的字段在数据映射中也标记为可选。否则,Entity不会检查它是否为空。

Muthukrishnan R 9 年前

PersonGroups集合,Any将根据您的条件仅返回true或false。如果集合包含PersonGroup中的任何一项->组GroupType==null或空,则返回true或false。

你的预期结果是真是假?