Entity Framework Core Linq Where NULL does not work
<p>I am using Entity Framework Core and Linq to write a query to get all entries where the EndDate property on my object is NULL.  However, EF does not convert the query to the proper SQL to filter out the objects with an EndDate that is not NULL.  I am using a MySQL database with these packages:</p>

<pre><code>"MySql.Data.Core": "7.0.4-IR-191",
"MySql.Data.EntityFrameworkCore": "7.0.4-IR-191",
</code></pre>

<p>Here is my query:</p>

<pre><code>var employees = (from emp in _context.Employees.ToList()
                             join loc in _context.Locations.ToList()
                                on emp.HomeLocationId equals loc.LocationId
                             join rate in _context.EmployeeRates.ToList()
                                on emp.EmployeeId equals rate.EmployeeId
                             where rate.EndDate == null
                             select emp).ToList();
</code></pre>

<p>Here is my EndDate property declaration:</p>

<pre><code>public DateTime? EndDate { get; set; }
</code></pre>

<p>The SQL that is generated does not include a WHERE clause at all.  I've converted this query to MySQL manually and it works perfectly:</p>

<pre><code>SELECT e.FirstName, e.LastName, er.Rate, er.StartDate, er.EndDate
FROM qasdb.employees as e
JOIN qasdb.employeerates as er on er.EmployeeId = e.EmployeeId
JOIN qasdb.locations as l on l.LocationId = e.HomeLocationId
WHERE e.FirstName='Todd' AND er.EndDate is null
</code></pre>

<p>Is this a problem with EF Core?  Is there a known work around to get Null comparisons to work?</p>

<p><strong>EDIT</strong></p>

<p>Here is the generated SQL.  It looks to be doing a couple queries:</p>

<pre><code>SELECT e.EmployeeRateId, e.EmployeeId, e.EndDate,
       e.LastModifiedBy, e.Rate, e.StartDate FROM employeerates
AS e

SELECT emp.EmployeeId, emp.ActiveFlag, emp.City,
       emp.CreateStamp, emp.Email, emp.EmergencyContactName,
       emp.EmergencyContactPhone, emp.FirstName,
       emp.HomeLocationId, emp.JobClass, emp.LastModifiedBy,
       emp.LastName, emp.PhoneNumber, emp.Ssn, emp.State,
       emp.Street, emp.UpdateStamp, emp.Zip FROM employees 
AS emp
INNER JOIN locations AS loc ON emp.HomeLocationId = loc.LocationId
</code></pre>

<p><strong>EDIT #2</strong></p>

<p>When I remove the ToList() calls from each line, the SQL is generated as expected:</p>

<pre><code>SELECT emp.EmployeeId, emp.ActiveFlag, emp.City,
       emp.CreateStamp, emp.Email, emp.EmergencyContactName,
       emp.EmergencyContactPhone, emp.FirstName,
       emp.HomeLocationId, emp.JobClass, emp.LastModifiedBy,
       emp.LastName, emp.PhoneNumber, emp.Ssn, emp.State,
       emp.Street, emp.UpdateStamp, emp.Zip FROM employees 
AS emp  
INNER JOIN locations AS loc ON emp.HomeLocationId = loc.LocationId 
INNER JOIN employeerates AS rate ON emp.EmployeeId = rate.EmployeeId 
WHERE rate.EndDate IS NULL
</code></pre>

<p>However, I am losing my navigation property from the Employee Object to the EmployeeRates list when I remove the .ToList() calls.  Here is how my Employee entity is setup:</p>

<pre><code> public class Employee : BaseEntity
 {
     public int EmployeeId { get; set; }

     ......


     public string JobClass { get; set; }

     public int HomeLocationId { get; set; }




     //Navigation Properties
     public virtual Location HomeLocation { get; set; }

     public virtual List&lt;EmployeeRate&gt; EmployeeRates { get; set; }

 }
</code></pre>

<p>Both the HomeLocation and EmployeeRates objects come back "null" after removing the ToList() calls.</p>