Null coalescing operator not working in Entity Framework 6

c# entity-framework-6

Question

I'm currently migrating an application from EF5 to EF6 but ran into an issue with a unit test that runs this query:

return (from employeeWorkLocation in Retrieve()
                    where employeeWorkLocation.ClientId == clientId
                     && employeeWorkLocation.EmpUid == empUid
                     && employeeWorkLocation.EffectiveDate <= effectiveDate
                     && (!employeeWorkLocation.EffectiveEndDate.HasValue || employeeWorkLocation.EffectiveEndDate > effectiveDate)
                    join locationEntity in Context.WorkLocationEntities on employeeWorkLocation.WorkLocationUid equals locationEntity.WorkLocationUid into workLocations
                    from workLocation in workLocations.Where(wl => wl.Inactive == GenericYesNo.NO).DefaultIfEmpty()
                    select new EmployeeWorkLocation()
                    {
                        ClientId = employeeWorkLocation.ClientId,
                        EffectiveDate = employeeWorkLocation.EffectiveDate,
                        EffectiveEndDate = employeeWorkLocation.EffectiveEndDate,
                        EmployeeWorkLocationUid = employeeWorkLocation.EmployeeWorkLocationUid,
                        EmpUid = employeeWorkLocation.EmpUid,
                        MetaApplication = employeeWorkLocation.MetaApplication,
                        //MetaDateCreated = employeeWorkLocation.MetaDateCreated ?? DateTimeHelper.NowUnspecified,
                        MetaCreatedBy = employeeWorkLocation.MetaCreatedBy,
                        //MetaDateUpdated = employeeWorkLocation.MetaDateUpdated ?? DateTimeHelper.NowUnspecified,
                        MetaUpdatedBy = employeeWorkLocation.MetaUpdatedBy,
                        WorkLocationUid = employeeWorkLocation.WorkLocationUid,
                        HrLocationUid = workLocation.HRPLocationUid
                    }).OrderByDescending(e => e.EffectiveDate).FirstOrDefault();

For some reason if I remove the comments above I get this error:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.ArgumentException: Argument types do not match

I tried changing those lines to the long version (ternary operator) but still no luck. I get same error:

MetaDateCreated = employeeWorkLocation.MetaDateCreated != null ? employeeWorkLocation.MetaDateCreated.Value : DateTimeHelper.NowUnspecified,

employeeWorkLocation.MetaDateCreated and employeeWorkLocation.MetaDateUpdated are both of nullable Datetime? type

DateTimeHelper.NowUnspecified is of Datetime non-nullable type. Same as MetaDateCreated and MetaDateUpdated

Any ideas? This was working fine with Entity Framework 5

Update: Here is the definition for DateTimeHelper.NowUnspecified:

public static DateTime NowUnspecified
{
    get
    {
        return DateTime.SpecifyKind(DateTime.Now, DateTimeKind.Unspecified);
    }
}

If I replace DateTimeHelper.NowUnspecified with DateTimeHelper.Now as suggested in the comments my test passes...

Update2: After isolating the issue using LinqPad I realized that Entity Framework 6 was handling the query correctly. The problem is with Effort.EF6 library which is throwing the exception

Thanks,

1
3
10/18/2016 2:00:46 PM

Popular Answer

The issue is that EF does not know how to translate your property to sql. If you really need to use that property (or run into situations like this in the future), you can do so by forcing EF to execute the query prior to that portion of the logic and apply it locally:

return (from employeeWorkLocation in Retrieve()
                    where employeeWorkLocation.ClientId == clientId
                     && employeeWorkLocation.EmpUid == empUid
                     && employeeWorkLocation.EffectiveDate <= effectiveDate
                     && (!employeeWorkLocation.EffectiveEndDate.HasValue || employeeWorkLocation.EffectiveEndDate > effectiveDate)
                    join locationEntity in Context.WorkLocationEntities on employeeWorkLocation.WorkLocationUid equals locationEntity.WorkLocationUid into workLocations
                    from workLocation in workLocations.Where(wl => wl.Inactive == GenericYesNo.NO).DefaultIfEmpty()
                    select new{employeeWorkLocation, workLocation})
                    .ToArray() //this will cause EF to run the query
                    //Everything below this runs in the .NET code 
                    //rather than on sql server
                    .Select(wl => new EmployeeWorkLocation()
                    {
                        ClientId = wl.employeeWorkLocation.ClientId,
                        EffectiveDate = wl.employeeWorkLocation.EffectiveDate,
                        EffectiveEndDate = wl.employeeWorkLocation.EffectiveEndDate,
                        EmployeeWorkLocationUid = wl.employeeWorkLocation.EmployeeWorkLocationUid,
                        EmpUid = wl.employeeWorkLocation.EmpUid,
                        MetaApplication = wl.employeeWorkLocation.MetaApplication,
                        MetaDateCreated = wl.employeeWorkLocation.MetaDateCreated ?? DateTimeHelper.NowUnspecified,
                        MetaCreatedBy = wl.employeeWorkLocation.MetaCreatedBy,
                        MetaDateUpdated = wl.employeeWorkLocation.MetaDateUpdated ?? DateTimeHelper.NowUnspecified,
                        MetaUpdatedBy = employeeWorkLocation.MetaUpdatedBy,
                        WorkLocationUid = wl.employeeWorkLocation.WorkLocationUid,
                        HrLocationUid = wl.workLocation?.HRPLocationUid
                    }).OrderByDescending(e => e.EffectiveDate).FirstOrDefault();
2
8/13/2016 4:43:33 PM


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