Entity Framework Core Linq Where NULL does not work

entity-framework entity-framework-core linq mysql

Question

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:

"MySql.Data.Core": "7.0.4-IR-191",
"MySql.Data.EntityFrameworkCore": "7.0.4-IR-191",

Here is my query:

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();

Here is my EndDate property declaration:

public DateTime? EndDate { get; set; }

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:

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

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

EDIT

Here is the generated SQL. It looks to be doing a couple queries:

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

EDIT #2

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

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

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:

 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<EmployeeRate> EmployeeRates { get; set; }

 }

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

1
2
5/23/2018 1:50:43 PM

Accepted Answer

Your first query contains, ToList() after each DbSet that would bring all the data from all those tables to in-memory. EF executes the query whenever ToList() is called. So even if you have written whole linq query together, there are 3 small queries for EF to process there (loading each DbSet) therefore you get 3 different queries being sent to database. EF will generate collection of entities from those queries and everything else will be evaluated on client side therefore you do not see the where clause being translated to server because it is not part of the query provided to EF at all. In this solution you are able to see the navigation properties loaded since all the data is loaded in-memory then EF will fix up the navigation properties to populate them. (so that all data in-memory is in consistent state.)

When you remove ToList() calls from all DbSets, it becomes one query being passed to EF. (the last ToList() calls for execution) So EF will process the query and translate the where clause but since you are only projecting out Employee object, EF will fetch only properties of it and will not retrieve any related data. If you want to have navigation property populated i.e. load related data then you have to tell EF explicitly by using Include synatax.

The query you are looking for is

var result = (from e in db.Employees.Include(e => e.HomeLocation).Include(e => e.EmployeeRates)
    join er in db.EmployeeRates on e.Id equals er.EmployeeId
    where er.EndDate != null
    select e).ToList();

The query is having Include for each navigation which you want populated in final result. Since filtered include are still not supported in EF, (see https://github.com/aspnet/EntityFramework/issues/1833) you cannot specify the where clause on rate.EndDate directly. Therefore you need to manually join with that table and apply the where condition on EndDate. Since HomeLocation is one-to-one navigation, EF will retrieve related data for it (to populate navigation) in main query only. EmployeeRates is collection navigation so EF will sent a separate query to load related data for relevant employees. Important point, you don't need to manually join for HomeLocation. Include will do that for you. You need manual join with EmployeeRate just for the filtering only.

Following is the main query generated in SQL

    SELECT [e].[Id], [e].[HomeLocationId], [l].[Id]
    FROM [Employees] AS [e]
    INNER JOIN [EmployeeRates] AS [er] ON [e].[Id] = [er].[EmployeeId]
    INNER JOIN [Locations] AS [l] ON [e].[HomeLocationId] = [l].[Id]
    WHERE [er].[EndDate] IS NOT NULL
    ORDER BY [e].[Id]
2
12/14/2016 12:01:43 AM

Popular Answer

Are you using Code First?

Check if u doesn't have this in your Map class

this.Property(t => t.EndDate).IsRequired();



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow