Linq to SQL Join Allowing Nulls When I don't want that

Question

I've written the below query to join a couple of tables. The la.UserProfileId is nullable for some ungodly reason.

When I write the equivalent SQL statement, I get 0 records as I should at this point.

var result = (from e in _ctx.Employees
                     join la in _ctx.LoginAudits on e.UserProfile.Id equals la.UserProfileId.Value
                     where la.LoginDate >= fromDate
                     && e.Client.Id == clientID
                     select new
                     {
                         la.Id,
                         employeeID = e.Id,
                         e.Client.DisplayName,
                         la.UserProfileId
                     }).ToList();

The above LINQ code generates the below SQL.

exec sp_executesql N'SELECT 
1 AS [C1], 
[Extent2].[Id] AS [Id], 
[Extent1].[Id] AS [Id1], 
[Extent3].[DisplayName] AS [DisplayName], 
[Extent2].[UserProfileId] AS [UserProfileId]
FROM   [dbo].[Employees] AS [Extent1]
INNER JOIN [dbo].[LoginAudits] AS [Extent2] ON ([Extent1].[UserProfile_Id] = [Extent2].[UserProfileId]) OR (([Extent1].[UserProfile_Id] IS NULL) AND ([Extent2].[UserProfileId] IS NULL))
INNER JOIN [dbo].[Clients] AS [Extent3] ON [Extent1].[Client_Id] = [Extent3].[Id]
WHERE ([Extent2].[LoginDate] >= @p__linq__0) AND ([Extent1].[Client_Id] = @p__linq__1)',N'@p__linq__0 datetime2(7),@p__linq__1 bigint',@p__linq__0='2018-02-09 11:11:29.1047249',@p__linq__1=37

As you can see, it includes "OR (([Extent1].[UserProfile_Id] IS NULL) AND ([Extent2].[UserProfileId] IS NULL))"

That is the exact opposite of what I want. How do I make it do a normal inner join and not try to allow for null values?

I was able to work around this by adding && la.UserProfileId != null in my WHERE clause, but ideally I'd rather make the JOIN behave like normal INNER JOIN and not try to anticipate stuff I don't ask for.

Answer 1

That is the exact opposite of what I want. How do I make it do a normal inner join and not try to allow for null values?

The reasoning behind is that in C# null == null evaluates to true, while in SQL it evaluates to NULL (and basically is handled like FALSE). So EF is trying to emulate the C# behavior in order to get the same result as if you were running the same query in LINQ to Objects.

This is the default EF6 behavior. It is controlled by the UseDatabaseNullSemantics property, so if you want to use the SQL behavior, you should set it to true in your DbContext derived class constructor or from outside:

[dbContext.]Configuration.UseDatabaseNullSemantics = true;

But that's not enough though. It affects all the comparison operators, but they forgot to apply it to joins. The solution is to not use LINQ join operator, but correlated where (EF is smart enough to turn it into SQL JOIN).

So additionally to setting UseDatabaseNullSemantics to true, replace

join la in _ctx.LoginAudits on e.UserProfile.Id equals la.UserProfileId

with

from la in _ctx.LoginAudits where e.UserProfile.Id == la.UserProfileId

and you'll get the desired INNER JOIN.