efcore: Wrong translation for default(DateTime)/default(Guid)/default(TimeSpan) in linq projection

How to reproduce:

    dbContext.Set<AnyEntityType>().Select(x => new
    { 
        DateTime = default(DateTime), 
        x.SomeProperty 
    }).ToList();

Expected result: One of:

Can not translate linq query to sql command
Parameterize default(DateTime) like a variable
Client evaluate default(DateTime)
Convert returned string value to DateTime type

Actual result:

System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.DateTime'.
   at Microsoft.Data.SqlClient.SqlBuffer.get_DateTime()
   at Microsoft.Data.SqlClient.SqlDataReader.GetDateTime(Int32 i)
   at lambda_method5(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Generated sql:

      SELECT '0001-01-01T00:00:00.0000000' AS [DateTime], [x].[SomeProperty]
      FROM [TableName] AS [x]

EF Core version: 5.0.2 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 5.0 Operating system: Windows 10 IDE: Visual Studio 2019 16.8

About this issue

Original URL
State: open
Created 3 years ago
Reactions: 3
Comments: 16 (12 by maintainers)

Most upvoted comments

@roji

Consider we have an entity type like this:

public class Person
{
    public int Id { get; set; }

    public DateTime Birthday { get; set; }
   //Omit other properties 
}

Only the users who have permisson can access Birthday property and other sensitive information.

The query is like this:

    bool currentUserCanAccessBirthdayProperty = true; // or false
    var persons = db.People.Select(x => new Person
    {
        Id = x.Id,
        Birthday = currentUserCanAccessBirthdayProperty ? x.Birthday : default(DateTime)
    })

If currentUserCanAccessBirthdayProperty is false, then the query equals to

    var persons = db.People.Select(x => new Person
    {
        Id = x.Id,
        Birthday = default(DateTime)
    })

We use OData to shape the returned entities, so it’s not possible to set Birthday to default(DateTime) after the query.

I know that changing the type of Birthday from DateTime to DateTime? may be better. But this is how I met this issue half year ago.

anranruye on Aug 26, 2021

I’ve also just run into this. For anyone else looking for a workaround, adding (DateTime)(object) yields the desired SQL, e.g.:

    dbContext.Set<AnyEntityType>().Select(x => new
    { 
        DateTime = (DateTime)(object)DateTime.MinValue, 
        x.SomeProperty 
    }).ToList();

SQL

      SELECT CAST('0001-01-01T00:00:00.0000000' AS datetime2) AS [DateTime], [x].[SomeProperty]
      FROM [TableName] AS [x]

Suchiman on Mar 24, 2023