Timespan not supported for query select linq

[ruben-rasmussen]

Follow my repo for reproduction there are multiple cases:

https://github.com/ruben-rasmussen/timespanissue

the issue is when c# data type TimeSpan is 1 day or more, then it can't serialize the response from postgresql. It only happens if using a .Select as in my example

While creating the issue I also encountered trouble with the /fails3 endpoint where it can't serialize the timespan due to leading 0.

one of the issues is likely cause by postgresql returning a weird format for intervals. where everything is cool until you have 1+ days:

stacktrace:

Npgsql.Command: Information: Command execution completed (duration=1ms): select jsonb_build_object('TimeSpan', CAST(d.data ->> 'TimeSpan' as interval)) as data from public.mt_doc_datawithtimespan as d
info: Npgsql.Command[2001]
Command execution completed (duration=1ms): select jsonb_build_object('TimeSpan', CAST(d.data ->> 'TimeSpan' as interval)) as data from public.mt_doc_datawithtimespan as d
Exception thrown: 'System.Text.Json.JsonException' in System.Private.CoreLib.dll
fail: Microsoft.AspNetCore.Server.Kestrel[13]
Connection id "0HN4V95PGUA1K", Request id "0HN4V95PGUA1K:00000002": An unhandled exception was thrown by the application.
System.Text.Json.JsonException: The JSON value could not be converted to System.TimeSpan. Path: $.TimeSpan | LineNumber: 0 | BytePositionInLine: 33.
---> System.FormatException: The JSON value is not in a supported TimeSpan format.
at System.Text.Json.ThrowHelper.ThrowFormatException(DataType dataType)
at System.Text.Json.Serialization.Converters.TimeSpanConverter.ReadCore(Utf8JsonReader& reader)
at System.Text.Json.Serialization.Metadata.JsonPropertyInfo1.ReadJsonAndSetMember(Object obj, ReadStack& state, Utf8JsonReader& reader) at System.Text.Json.Serialization.Converters.ObjectDefaultConverter1.OnTryRead(Utf8JsonReader& reader, Type typeToConvert, JsonSerializerOptions options, ReadStack& state, T& value)
at System.Text.Json.Serialization.JsonConverter1.TryRead(Utf8JsonReader& reader, Type typeToConvert, JsonSerializerOptions options, ReadStack& state, T& value, Boolean& isPopulatedValue) at System.Text.Json.Serialization.JsonConverter1.ReadCore(Utf8JsonReader& reader, JsonSerializerOptions options, ReadStack& state)
--- End of inner exception stack trace ---
at System.Text.Json.ThrowHelper.ReThrowWithPath(ReadStack& state, Utf8JsonReader& reader, Exception ex)
at System.Text.Json.Serialization.JsonConverter1.ReadCore(Utf8JsonReader& reader, JsonSerializerOptions options, ReadStack& state) at System.Text.Json.JsonSerializer.ReadFromSpan[TValue](ReadOnlySpan1 utf8Json, JsonTypeInfo1 jsonTypeInfo, Nullable1 actualByteCount)
at System.Text.Json.JsonSerializer.Deserialize[TValue](ReadOnlySpan1 utf8Json, JsonSerializerOptions options) at Marten.Services.SystemTextJsonSerializer.FromJson[T](Stream stream) at Marten.Services.SystemTextJsonSerializer.FromJson[T](DbDataReader reader, Int32 index) at Marten.Linq.Selectors.SerializationSelector1.Resolve(DbDataReader reader)
at Marten.Linq.QueryHandlers.ListQueryHandler1.Handle(DbDataReader reader, IMartenSession session) at Marten.Linq.QueryHandlers.ListQueryHandler1.Marten.Linq.QueryHandlers.IQueryHandler<System.Collections.Generic.IEnumerable>.Handle(DbDataReader reader, IMartenSession session)
at Marten.Linq.MartenLinqQueryProvider.ExecuteHandler[T](IQueryHandler1 handler) --- End of stack trace from previous location --- at JasperFx.Core.Exceptions.ExceptionTransformExtensions.TransformAndThrow(IEnumerable1 transforms, Exception ex)
at JasperFx.Core.Exceptions.ExceptionTransforms.TransformAndThrow(Exception ex)
at Marten.Exceptions.MartenExceptionTransformer.WrapAndThrow(Exception exception)
at Marten.Linq.MartenLinqQueryProvider.ExecuteHandler[T](IQueryHandler1 handler) at Marten.Linq.MartenLinqQueryProvider.Execute[TResult](Expression expression) at Marten.Linq.MartenLinqQueryable1.GetEnumerator()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Program.<>c.<<Main>$>b__0_5(IQuerySession session) in /Users/ruben/Documents/repos/martentimespanissue/webapp/Program.cs:line 35 at lambda_method8(Closure, Object, HttpContext) at Microsoft.AspNetCore.HttpsPolicy.HttpsRedirectionMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.HostFiltering.HostFilteringMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication1 application)
Microsoft.AspNetCore.Server.Kestrel: Error: Connection id "0HN4V95PGUA1K", Request id "0HN4V95PGUA1K:00000002": An unhandled exception was thrown by the application.

System.Text.Json.JsonException: The JSON value could not be converted to System.TimeSpan. Path: $.TimeSpan | LineNumber: 0 | BytePositionInLine: 33.
---> System.FormatException: The JSON value is not in a supported TimeSpan format.
at System.Text.Json.ThrowHelper.ThrowFormatException(DataType dataType)
at System.Text.Json.Serialization.Converters.TimeSpanConverter.ReadCore(Utf8JsonReader& reader)
at System.Text.Json.Serialization.Metadata.JsonPropertyInfo1.ReadJsonAndSetMember(Object obj, ReadStack& state, Utf8JsonReader& reader) at System.Text.Json.Serialization.Converters.ObjectDefaultConverter1.OnTryRead(Utf8JsonReader& reader, Type typeToConvert, JsonSerializerOptions options, ReadStack& state, T& value)
at System.Text.Json.Serialization.JsonConverter1.TryRead(Utf8JsonReader& reader, Type typeToConvert, JsonSerializerOptions options, ReadStack& state, T& value, Boolean& isPopulatedValue) at System.Text.Json.Serialization.JsonConverter1.ReadCore(Utf8JsonReader& reader, JsonSerializerOptions options, ReadStack& state)
--- End of inner exception stack trace ---
at System.Text.Json.ThrowHelper.ReThrowWithPath(ReadStack& state, Utf8JsonReader& reader, Exception ex)
at System.Text.Json.Serialization.JsonConverter1.ReadCore(Utf8JsonReader& reader, JsonSerializerOptions options, ReadStack& state) at System.Text.Json.JsonSerializer.ReadFromSpan[TValue](ReadOnlySpan1 utf8Json, JsonTypeInfo1 jsonTypeInfo, Nullable1 actualByteCount)
at System.Text.Json.JsonSerializer.Deserialize[TValue](ReadOnlySpan1 utf8Json, JsonSerializerOptions options) at Marten.Services.SystemTextJsonSerializer.FromJson[T](Stream stream) at Marten.Services.SystemTextJsonSerializer.FromJson[T](DbDataReader reader, Int32 index) at Marten.Linq.Selectors.SerializationSelector1.Resolve(DbDataReader reader)
at Marten.Linq.QueryHandlers.ListQueryHandler1.Handle(DbDataReader reader, IMartenSession session) at Marten.Linq.QueryHandlers.ListQueryHandler1.Marten.Linq.QueryHandlers.IQueryHandler<System.Collections.Generic.IEnumerable>.Handle(DbDataReader reader, IMartenSession session)
at Marten.Linq.MartenLinqQueryProvider.ExecuteHandler[T](IQueryHandler1 handler) --- End of stack trace from previous location --- at JasperFx.Core.Exceptions.ExceptionTransformExtensions.TransformAndThrow(IEnumerable1 transforms, Exception ex)
at JasperFx.Core.Exceptions.ExceptionTransforms.TransformAndThrow(Exception ex)
at Marten.Exceptions.MartenExceptionTransformer.WrapAndThrow(Exception exception)
at Marten.Linq.MartenLinqQueryProvider.ExecuteHandler[T](IQueryHandler1 handler) at Marten.Linq.MartenLinqQueryProvider.Execute[TResult](Expression expression) at Marten.Linq.MartenLinqQueryable1.GetEnumerator()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Program.<>c.<<Main>$>b__0_5(IQuerySession session) in /Users/ruben/Documents/repos/martentimespanissue/webapp/Program.cs:line 35 at lambda_method8(Closure, Object, HttpContext) at Microsoft.AspNetCore.HttpsPolicy.HttpsRedirectionMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.HostFiltering.HostFilteringMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication1 application)

[ruben-rasmussen]

A fix would be adding SET intervalstyle = 'sql_standard'; to the query to prevent sql returning weird format :)

[jeremydmiller]

We can handle this on the Marten side of things. I'd argue that it's really an Npgsql issue, but Marten can cover it by having a specific TimeSpan specific IQueryableMember in its internals that fetches the raw data as a string, then does the Parse().

Not this week, but next, I could walk you through the PR if you were interested in trying that