How can format datetime with web api

Question

I have build a simple web service using Web Api in C#.

I have this method:

[Route("secsocdata/{id:int?}")]
[HttpGet]
public HttpResponseMessage getSecSocData(int? id = null)
{
    try
    {
    return Request.CreateResponse(HttpStatusCode.OK, new RCamera((short)status_code.Success, "Ok", getSecSocImages(id)));
    }
    catch (Exception e)
    {
    return Request.CreateResponse(HttpStatusCode.InternalServerError, new RMessage((short)status_code.Exception, HttpStatusCode.InternalServerError.ToString()));
    }
}

[NonAction]
private IQueryable<WSOmniacare.Models.AAHome.ImmaginiSecSoc.ImmaginiSecSocDTO> getSecSocImages(int? id)
{
   var strItem = from u in db_data.CAMERA_SEC_SOC
          where u.ID == id
          select u.Image;

    String imageBas64 = GetString(strItem.First());

    if (id != null)
    {
    return from u in db_data.CAMERA_SEC_SOC 
           where u.ID == id
           select new WSOmniacare.Models.AAHome.ImmaginiSecSoc.ImmaginiSecSocDTO()
           {
           image = imageBas64,
           image_width = u.image_width,
           image_height= u.image_height,
           type = u.type,
           timestamp = u.timestamp.ToString("MM/dd/yyyy HH:mm:ss.fff",
            CultureInfo.InvariantCulture),
           rectangle = new WSOmniacare.Models.AAHome.ImmaginiSecSoc.ImmaginiSecSocDTO.Rectangle()
           {
             rects = from pi in db_data.CAMERA_SEC_SOC_Rectangles 
                where pi.ID_SecSoc  == id
                 select new WSOmniacare.Models.AAHome.ImmaginiSecSoc.ImmaginiSecSocDTO.Rectangle.Rect()
                {   
                height= pi.height,
                width = pi.width,
                x = pi.x,
                y=pi.y
                }
           } 
           };
    }
    return null;
}

So, if I try to call this web service, I have this error:

{ "Message": "An error has occurred.", "ExceptionMessage": "The 'ObjectContent1' type failed to serialize the response body for content type 'application/json; charset=utf-8'.", "ExceptionType": "System.InvalidOperationException", "StackTrace": null, "InnerException": { "Message": "An error has occurred.", "ExceptionMessage": "LINQ to Entities does not recognize the method 'System.String ToString(System.String, System.IFormatProvider)' method, and this method cannot be translated into a store expression.", "ExceptionType": "System.NotSupportedException", "StackTrace": " in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator1.Translate(ExpressionConverter parent, Expression linq)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MemberInitTranslator.TypedTranslate(ExpressionConverter parent, MemberInitExpression linq)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator1.Translate(ExpressionConverter parent, Expression linq)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator1.Translate(ExpressionConverter parent, Expression linq)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n in System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()\r\n in System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable1 forMergeOption)\r\n in System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass7.b__6()\r\n in System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)\r\n in System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass7.b__5()\r\n in System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)\r\n in System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption)\r\n in System.Data.Entity.Core.Objects.ObjectQuery1..GetEnumerator>b__0()\r\n in System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()\r\n in Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)\r\n in Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)\r\n in Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject(JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)\r\n in Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)\r\n in Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)\r\n in Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)\r\n in Newtonsoft.Json.JsonSerializer.Serialize(JsonWriter jsonWriter, Object value)\r\n in System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n in System.Net.Http.Formatting.JsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n in System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content)\r\n in System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)\r\n--- Fine traccia dello stack da posizione precedente dove è stata generata l'eccezione ---\r\n in System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n in System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n in System.Runtime.CompilerServices.TaskAwaiter.GetResult()\r\n in System.Web.Http.WebHost.HttpControllerHandler.d__1b.MoveNext()" } }

Now I think that the problem is when I'm insert this line of code:

timestamp = u.timestamp.ToString("MM/dd/yyyy HH:mm:ss.fff",
                                CultureInfo.InvariantCulture),

If I use this

timestamp = u.timestamp.ToString(),

instead of

timestamp = u.timestamp.ToString("MM/dd/yyyy HH:mm:ss.fff",
                                    CultureInfo.InvariantCulture),

My code works. How can I fixed it?

Ruslan Valitov Ruslan Valitov · Accepted Answer · 2016-01-28T16:56:34

Looks like db_data is DbContext and CAMERA_SEC_SOC_Rectangles is DbSet. So you want to execute Linq-to-SQL query. When you use Linq over DbSet your Linq query is translated to appropriate SQL query. Seems that your Linq-to-SQL driver cannot translate function ToString(format,CultureInfo) to sql function. So I see for you 2 options:

Remove unsupported ToString from Linq query: extract timestamp in DateTime format and then process it with C#.
Find C# function for date formatting that can be translated to sql function.

How can format datetime with web api

1 Answers