Any() is not returning expected result -- alternatives?

Question

Edit2 Found the solution with some team members. See my comment for the resolution.

After looking, can't seem to find the real answer to my problem.

I'm trying to find all users that have any groups whose group type is blank/null/empty. Code:

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

There are many users in our database that this expression should return. Instead, it doesn't return any. A colleague that is much smarter and more experienced than me looked at the generated SQL with me yesterday, and all that he concluded was that this was a wonderful example of LINQ and Entity not working well together. The entity on which this is querying is mapped properly to the database. I've checked and checked, and this piece of code is definitely the code that is doing this (not before or after). What is most befuddling is that there are other linq queries in our code that look very similar to this one that return the expected result.

So two questions:

1) Is there anything you see that I am doing wrong?

2) Without writing raw SQL, what would possible alternatives be? I've been trying to read as much about LINQ as I can, but I'm sure I'm missing something.

Thanks!

(Note: I haven't found these posts to quite answer my questions: 1, 2, 3, 4)

Edit1: Generated sql code.

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

It would probably help if you could post the generated SQL, and a representative sample of some of the data in the database you feel should be getting picked up by this query. This way we can try to figure out what needs to change... — Stephen Byrne, Jan 07 '16 at 15:56
Ok, I will get it and try to get rid of some of the gunk - it's enormous. — Kramlinger, Jan 07 '16 at 16:00
Do you want to get a list of users or just a boolean that let you know that there some users that satisfy the condition expressed? — user449689, Jan 07 '16 at 16:04
I see `LEN([Extent6].[Group_Type]) = 0` a couple of times in the query. That seems to have been generated from a different expression: `y.Group.GroupType.Length == 0`. — Gert Arnold, Jan 07 '16 at 16:45
Thanks for the consideration and the input everyone! The problem after all was indeed in our mapping. Apparently we set up our context so that by default all fields were required. Knowing this, entity neglects to query for null fields in the database. Since that field is indeed nullable in the database, I changed the field in the map to say that it is optional, and the query is working as expect now. Sorry that I missed this before posting and thanks for all the input on my first question! — Kramlinger, Jan 07 '16 at 17:21

user449689 · Answer 1 · 2016-01-07T16:11:50.583

1

To get a list of PersonGroup objects, use Where instead of Any:

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

edited Jan 07 '16 at 16:11

answered Jan 07 '16 at 16:06

user449689

3,009
4
17
36

score 1 · Accepted Answer · answered Jan 08 '16 at 15:18

The problem after all was indeed in our mapping.

Entity Framework marks all fields as optional by default. But in our case, our team custom set up Entity, during which they set all fields to required in our context. This was the crux of the problem; when a field in the data map is marked as required, Entity simply skips checking that field in the table for null values when it executes LINQ queries. As you can see from my question, null fields are some of the exact things I'm searching for--there are many in my case. So, to fix this I simply marked the field as optional in our data maps. Because Entity knows to check it for null fields, it functions as expected.

Tl;dr: If you are having a similar problem, check your conext and data maps to be sure the field that is nullable in your database is also marked as optional in your data map. Otherwise, Entity won't check it for nulls.

score 0 · Answer 3 · answered Jan 07 '16 at 16:04

PersonGroups collection, Any will return only true or false based on your condition. If collection contain any one item in PersonGroup -> group.GroupType == null or empty, then it will return true else false.

Your expectation result is true or false only?

Any() is not returning expected result -- alternatives?

3 Answers3