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

[anranruye]

How to reproduce:

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

Expected result:
One of:

1. Can not translate linq query to sql command
2. Parameterize default(DateTime) like a variable
3. Client evaluate default(DateTime)
4. 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

[smitpatel]

var connection = db.Database.GetDbConnection();
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT '0001-01-01T00:00:00.0000000'";
                    using (var reader = command.ExecuteReader())
                    {
                        reader.Read();
                        Console.WriteLine(reader.GetDataTypeName(0));
                        Console.WriteLine(reader.GetDateTime(0));
                    }
                }
                connection.Close();

The issue is in SqlClient which doesn't return correct type. The datatype is varchar

[roji]

@smitpatel are you sure there's a SqlClient issue here? It seems pretty reasonable to me for this untyped literal to return a varchar (anything else would require sniffing and heuristically inferring the type based on contents). Shouldn't we be typing the literal in this case, i.e. CAST('...' AS timestamp2)?

[smitpatel]

Other providers are working fine with the query. The untyped literal is how datetimes are printed in SQL for SqlClient so if a literal can be interpreted in multiple different types then there should be resiliency.
I removed from milestone if we want to add custom convert node to account for the mismatch.

[roji]

FWIW the code above also fails on Npgsql (Can't cast database type text to DateTime) as well as on MySqlConnector (Couldn't interpret '0001-01-01T00:00:00.0000000' as a valid DateTime), anything else would mean the driver parses a string to a DateTime, with all the locale complexities etc...

So yeah, I think that if we want to make this work, we're going to need that convert node. It's also a bit odd to roundtrip an uncorrelated value like this, I wonder if there's an actual use case...

[anranruye]

@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]

It's also a bit odd to roundtrip an uncorrelated value like this

I agree. It's better to handle 'default(DateTime)' at the client side. But making a convert node is also ok.

[roji]

@anranruye thanks for the use-case scenario, that makes sense.

On an unrelated note, for your example, it could be argued that the type mapping of x.Birthday should be used to generate the literal for default(DateTime) (though that's probably not going to be easy as the whole conditional expression is elided early on). But in any case, the resulting literal in the SQL likely needs to be explicitly typed with a convert node.