How to filter IQueryable for null navigation property

Question

Say I have 2 simple models (below), where 'User' contains a navigation property to 'UserDetail' (one-to-one relationship):

Database

CREATE TABLE [dbo].[UserDetail]
(
    [UserId] [int] NOT NULL PRIMARY KEY,
    [Name] varchar(100) NULL
)
CREATE TABLE [dbo].[User]
(
    [UserId] [int] NOT NULL PRIMARY KEY
)

C#.NET

[Table("UserDetail"), Schema="dbo"]
public class UserDetail
{
    [Key, Required, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int UserId { get; set; }
    public string Name { get; set; }
    // Additional Properties Not Listed
}

[Table("User"), Schema="dbo"]
public class User
{
    [Key, Required]
    public int UserId { get; set; }
    // Additional Properties Not Listed

    [ForeignKey("UserId")]
    public virtual UserDetail UserDetail { get; set; }
}

Now, the .NET application is using a repository architecture, so I'm initially setting up a query for the 'User' table (default Entity Framework behavior; lazy-loading):

IQueryable<User> users = DataRepository.UserRepository.List(); // 'List()' returns an IQueryable

My ultimate goal from here is to simply add a filter that will only pull 'User'(s) that DO NOT have an associated 'UserDetail' record (i.e. navigation property would be NULL):

users = users.Where(m => m.UserDetail == null); // Also tried (m => m.UserDetail.Equals(null))
var testing = users.ToList();

But when inspecting the 'testing' variable, query ALWAYS returns empty (in the database, there are 'User' records with and without associated 'UserDetails'). Upon some initial research, it seemed like this was potentially due to the lazy-loading. To verify, I tried the following example:

users = users.Where(m => m.UserDetail.Name == "UserName");
var testing = users.ToList();

To my surprise, this worked as originally expected; the 'testing' variable contained 'User' records, which had an associated 'UserDetail' record with 'Name' property equaling 'UserName'. It now seems like the lazy-loading isn't a issue but rather a potential problem with IQueryable comparing NULL navigation properties (during the SQL query translation?).

In a final test, decided to try using the Include method to see if that made a difference but it produced the same result as the first test (i.e. empty):

users = users.Include(m => m.UserDetail).Where(m => m.UserDetail == null);
var testing = users.ToList();

So I'm now at a loss and obviously don't understand well enough to produce the results I'm looking for. Would love any advice/help from anyone who knows where I'm getting tripped up on this problem.

My best guess is that the IQueryable query translates navigation properties as 'JOIN'(s) rather than 'LEFT JOIN'(s) and therefore, cannot be used to compare empty/null associated records (the records are simply not there).

To recap questions:

1. How can I write a query in .NET/Entity Framework that will be executed on the database to only pull 'User'(s) which have no associated 'UserDetail' record?
2. Why does IQueryable not pull the records I'm expecting in my first test?
3. Clarify any general points I'm misstating around IQueryable functionality

Thanks in advance for any help/advice!

Answer 1

Ultimately, the problem came down to an incorrect relationship configuration (data annotations) for Entity Framework. Was attempting to configure a one-to-(one or none) relationship that Entity Framework was interpreting as a one-to-one (both being required). Hence, IQueryable/Include functions were performing JOIN instead of the needed LEFT JOIN.

There were two articles I referenced that lead me down this line of thinking (this and this)

The required code changes were to the C#.NET models:

[Table("UserDetail"), Schema="dbo"]
public class UserDetail
{
    // Other articles solution was to remove the 'Required' data annotation, but I found
    // it not to be necessary (especially if it's used for validation)
    [Key, Required, DatabaseGenerated(DatabaseGeneratedOption.None)] 
    public int UserId { get; set; }
    public string Name { get; set; }
    // Additional Properties Not Listed

    // Required so that 'UserId' can be considered 'nullable';
    // not just a primary key (non-nullable)
    [ForeignKey("UserId")]
    // Needed to determine 'principle end' between 'User' and 'UserDetail' relationship
    [Required]
    public virtual UserDetail UserDetail { get; set; }
}

[Table("User"), Schema="dbo"]
public class User
{
    [Key, Required]
    public int UserId { get; set; }
    // Additional Properties Not Listed

    //[ForeignKey("UserId")] // Optional: not needed in my case but won't break anything
    public virtual UserDetail UserDetail { get; set; }
}

And now the IQueryable query works as expected/intended:

IQueryable<User> users = DataRepository.UserRepository.List().Where(m => m.UserDetail == null);
// Now, it only displays 'User' records that don't have an associated 'UserDetail' record
// FYI, no 'Include()' method required
var testing = users.ToList();