Using a DateTimeOffset created in a GroupBy fails to convert string to TimeSpan

[Biotronic]

Code to reproduce issue:

using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace ScratchDb;

internal class Program
{
    public static void Main(string[] args)
    {
        using var db = new ScratchContext();
        db.Widgets.Add(new Widget { Date = DateTimeOffset.UtcNow });
        
        // Exception here
        var v = db.Widgets
            .GroupBy(a => new DateTimeOffset(a.Date.Year, a.Date.Month, 1, 0, 0, 0, TimeSpan.Zero))
            .Select(a => a.Key)
            .ToList();
    }
}

internal class Widget
{
    [Key]
    public DateTimeOffset Date { get; set; }
}

internal class ScratchContext : DbContext
{
    public DbSet<Widget> Widgets { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseSqlServer("YourConnectionStringHere");
    }
}

Exception thrown

System.InvalidCastException: 'Unable to cast object of type 'System.String' to type 'System.TimeSpan'.'

   at Microsoft.Data.SqlClient.SqlDataReader.GetFieldValueFromSqlBufferInternal[T](SqlBuffer data, _SqlMetaData metaData, Boolean isAsync)
   at Microsoft.Data.SqlClient.SqlDataReader.GetFieldValue[T](Int32 i)
   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)
   at ScratchDb.Program.Main(String[] args) in Program.cs:line 19

Version information

EF Core version: 3.1.9 and 7.0.1 tested
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0, .NET Core 3.1
Operating system: Windows 10
IDE: Visual Studio 2022 17.3.6

Generated query

SELECT [t].[Key], [t].[Key0], [t].[Key1], [t].[Key2], [t].[Key3]
FROM (
    SELECT DATEPART(year, [f].[Date]) AS [Key], DATEPART(month, [f].[Date]) AS [Key0], 1 AS [Key1], 0 AS [Key2], '00:00:00' AS [Key3]
    FROM [Widgets] AS [f]
) AS [t]
GROUP BY [t].[Key], [t].[Key0], [t].[Key1], [t].[Key2], [t].[Key3]

As we can see in the generated query, [Key3] is '00:00:00', which fails to be correctly converted to a TimeSpan.

Note that without the GroupBy, the generated query is different, and '00:00:00' is not in there, thus not triggering this issue:

            var v2 = db.Widgets
                .Select(a => new DateTimeOffset(a.Date.Year, a.Date.Month, 1, 0, 0, 0, TimeSpan.Zero))
                .ToList();

Generates this query:

SELECT DATEPART(year, [f].[Date]), DATEPART(month, [f].[Date])
FROM [Widgets] AS [f]

Workaround

By using an anonymous class instead of a DateTimeOffset in the GroupBy, the issue disappears:

            var v2 = db.Widgets
                .GroupBy(a => new { a.Date.Year, a.Date.Month })
                .Select(a => new DateTimeOffset(a.Key.Year, a.Key.Month, 1, 0, 0, 0, TimeSpan.Zero))
                .ToList();

Generates this query:

SELECT [t].[Year], [t].[Month]
FROM (
    SELECT DATEPART(year, [f].[Date]) AS [Year], DATEPART(month, [f].[Date]) AS [Month]
    FROM [Widgets] AS [f]
) AS [t]
GROUP BY [t].[Year], [t].[Month]

Which, again, does not include a '00:00:00' string, and thus works fine.

[roji]

Duplicate of #24075

[roji]

The above basically projects an (untyped) time literal to be read back to a .NET TimeSpan, similar to this query from #24075:

_ = ctx.Set<Blog>().Select(x => new
{
    DateTime = default(TimeSpan),
    x.Name
}).ToList();

We need to add a Convert node to explicitly determine the data type in SQL, but there are some complications (see #24075).