Null checks in a Linq projection [EF 7 RC1]

Question

When I run this projection, the count is equal to the amount of People in my database:

int peopleProjectionCount  = _db.People.Count();

However when I run this projection and get the count, what I get is a count of only the records where TitleType and SuffixType are not null.

int peopleProjectionCount  = _db.People
            .Select(p => 
                new PersonViewModel()
                {
                    AgeInMonths = p.AgeInMonths,
                    Title = p.TitleType == null ? null : p.TitleType.Title,
                    FirstName = p.FirstName,
                    NickName = p.NickName,
                    MiddleName = p.MiddleName,
                    LastName = p.LastName,
                    Suffix = p.SuffixType == null ? null : p.SuffixType.Suffix,
                    DateOfBirth = p.DateOfBirth,
                    DateOfDeath = p.DateOfDeath
                }).Count();

The projection just returns the Count here for purposes of this question. What I really want to do is return a list of PersonViewModel's. If a person doesn't have a Title or Suffix, I still want to include that record; I just want that property on the view model to be null. Here it seems like those records are instead simply ignored.

Is this the expected behavior? If so, how can I use null checks in a projection like this without EF ignoring records where the checked properties are null?

What happens if you just remove the `null` checks altogether `Title = p.TitleType.Title`? — juharr, Apr 13 '16 at 17:12
Add this line of code into your Data Context constructor and check the queries executed in the Output window - this.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); — Stilgar, Apr 13 '16 at 17:19
@juharr If I just do `Title = p.TitleType.Title` and same for Suffix, I get the same result. It's as if EF does the null check for me and then ignores records that fail the check. My thought would be it fails with a NullReferenceException. — paz, Apr 13 '16 at 17:20
EF probably notices that you need the records in the result and does a join but for some reason it seems to be an inner join. — Stilgar, Apr 13 '16 at 17:22
@Stilgar I'm on EF 7 and that doesn't seem to be an option. I'll have to find another way to check the queries. — paz, Apr 13 '16 at 17:26
In EF7 you need to inject a logger. Maybe this will help - http://stackoverflow.com/questions/26747837/how-to-log-queries-using-entity-framework-7 — Stilgar, Apr 13 '16 at 17:37
EF**7** is the key - no more explanations needed. [Query: (Nav Prop Translation) Optional navigation translates to INNER JOIN instead of LEFT OUTER JOIN #3186](https://github.com/aspnet/EntityFramework/issues/3186). Friendly advice - don't use unreleased software. — Ivan Stoev, Apr 13 '16 at 17:52

paz · Answer 1 · 2016-04-13T20:11:24.940

As Ivan mentioned, this is a bug in EF 7 currently scheduled to be fixed in RC2.

I was able to hook up a profiler and saw the same problem that was reported and what Stilgar suspected: an inner join.

SELECT [p].[AgeInMonths], CASE
WHEN [p].[TitleTypeId] IS NULL
THEN NULL ELSE [p.TitleType].[Title]
END, [p].[FirstName], [p].[NickName], [p].[MiddleName], [p].[LastName], CASE
WHEN [p].[SuffixTypeId] IS NULL
THEN NULL ELSE [p.SuffixType].[Suffix]
END, [p].[DateOfBirth], [p].[DateOfDeath]
FROM [dbo].[People] AS [p]
INNER JOIN [dbo].[SuffixTypes] AS [p.SuffixType] ON [p].[SuffixTypeId] = [p.SuffixType].[Id]
INNER JOIN [dbo].[TitleTypes] AS [p.TitleType] ON [p].[TitleTypeId] = [p.TitleType].[Id]`

Null checks in a Linq projection [EF 7 RC1]

1 Answers1