title: Why does this LINQ query fail to work?
body: <p>I am running into a problem that I like to understand why it is happening. Some help would be appreciated.</p>
  
  <p>I am using entity framework core and this is the query I have in my code:</p>
  
  <pre><code>var user = await _db.User
        .Where(x =&gt; x.Email == username &amp; x.Suspended == null)
        .Select(x =&gt; new
        {
             x.UserId,
             x.Password,
             x.Salt,
             Role = x.Role.Name
        })
        .SingleOrDefaultAsync();
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/7pp5r.png" rel="nofollow"><img src="https://i.stack.imgur.com/7pp5r.png" alt="enter image description here"></a>
  <br/>
  This query does not select the user but it doesn't throw any error ether. If I remove "x.Suspended" from Where clause than it will return the user.
  The "Suspended" is a table that is related to the user table by the user ID. The user ID is PKs in both tables, so the relation is 1 to 1.
  I think this query does not return the user because the x.Suspended is null. Can some one help me understand why this is happening and what I can do to make it work.</p>
  
  <p>UPDATE:
  Ok, I used SQL server profiler and this is the converted sql by entity framework:</p>
  
  <pre><code>exec sp_executesql N'SELECT [x.Suspended].[UserId], [x.Suspended].[RCD], [x.Suspended].[Reason], [x].[UserId], [x].[Password], [x].[Salt], [x.Role].[Name]
  FROM [User] AS [x]
  INNER JOIN [Role] AS [x.Role] ON [x].[RoleId] = [x.Role].[RoleId]
  INNER JOIN [Suspended] AS [x.Suspended] ON [x].[UserId] = [x.Suspended].[UserId]
  WHERE [x].[Email] = @__username_0',N'@__username_0 varchar(80)',@__username_0='some-username'
  </code></pre>
  
  <p>the problem here is that all one-to-one relations are converted to INNER JOINs. How can I handle this problem? Thanks in advance!</p>
  
  <p>UPDATE 2:
  I am starting to think that this is a bug in EF Core. For example when I have this:</p>
  
  <pre><code>var user = await _db.User
        .Include(x =&gt; x.Role)
        .Include(x =&gt; x.Suspended)
        .Where(x =&gt; x.Email == username)
        .SingleOrDefaultAsync();
  </code></pre>
  
  <p>Under profiler the sql output is this:</p>
  
  <pre><code>exec sp_executesql N'SELECT TOP(2) [x].[UserId], [x].[Email], [x].[FirstName], [x].[LastName], [x].[Password], [x].[Phone], [x].[RCD], [x].[RoleId], [x].[Salt], [s].[UserId], [s].[RCD], [s].[Reason], [r].[RoleId], [r].[Name]
  FROM [User] AS [x]
  LEFT JOIN [Suspended] AS [s] ON [s].[UserId] = [x].[UserId]
  INNER JOIN [Role] AS [r] ON [x].[RoleId] = [r].[RoleId]
  WHERE [x].[Email] = @__username_0',N'@__username_0 varchar(80)',@__username_0='user-email'
  </code></pre>
  
  <p>Clearly the "Suspended" table is left join not inner. But when I have this:</p>
  
  <pre><code> var user1 = await _db.User
         .Include(x =&gt; x.Role)
         .Include(x =&gt; x.Suspended)
         .Where(x =&gt; x.Email == username &amp; x.Suspended == null)
         .SingleOrDefaultAsync();
  </code></pre>
  
  <p>The sql under the sql profiler is:</p>
  
  <pre><code>SELECT [x].[UserId], [x].[Email], [x].[FirstName], [x].[LastName], [x].[Password], [x].[Phone], [x].[RCD], [x].[RoleId], [x].[Salt], [x.Suspended].[UserId], [x.Suspended].[RCD], [x.Suspended].[Reason], [s].[UserId], [s].[RCD], [s].[Reason], [r].[RoleId], [r].[Name]
  FROM [User] AS [x]
  INNER JOIN [Suspended] AS [x.Suspended] ON [x].[UserId] = [x.Suspended].[UserId]
  LEFT JOIN [Suspended] AS [s] ON [s].[UserId] = [x].[UserId]
  INNER JOIN [Role] AS [r] ON [x].[RoleId] = [r].[RoleId]
  </code></pre>
  
  <p>what a mess :-D how the hell do we have "Suspended" table joined twice, INNER and LEFT? And where is the WHERE clause?</p>
  
  <h2>All I want is this in LINQ to work:</h2>
  
  <pre><code>SELECT U.[UserId], U.[Password], U.[Salt], R.[Name] AS [Role]
  FROM [ag].[dbo].[User] AS U
  INNER JOIN [ag].[dbo].[Role] AS R ON U.[RoleId] = R.[RoleId]
  LEFT JOIN [ag].[dbo].[Suspended] AS S ON U.[UserId] = S.[UserId]
  WHERE U.[Email] = 'john.smith@gmail.com' AND S.[UserId] IS NULL
  </code></pre>