Changing DateTime format in lambda Expression

Question

I have a DTO field in DateTime format

public DateTime date_joined { get; set; }

I use this to turn the data into a Json

public JsonResult Customer(int? id)
        {
            var user = User.Identity.Name;
            if (!AccountController.IsInRole(user, "admin"))
            {
                return null;
            }

            id = id == null ? 0 : id;

            var customer = db.PrmCustomers
                .Where(x => x.EmailAddress != null && x.CustomerID > id)
                .OrderBy(x => x.CustomerID)
                .Select(x => new CustomerDTO()
                {
                    email = x.EmailAddress,
                    phone = x.MobilePhoneNumber,
                    username = "",
                    first_name = x.Name,
                    last_name = x.Surname,
                    gender = x.GenderType,
                    customer_code = x.CustomerID,
                    is_active = "",
                    //date_joined = String.Format("{0:d/M/yyyy HH:mm:ss}", x.CreateDate.ToString()),
                    date_joined = x.CreateDate,
                    last_login = "",
                    date_of_birth = x.BirthDate,
                    //date_of_birth = String.Format("{0:d/M/yyyy HH:mm:ss}", x.BirthDate.ToString()),
                    password = x.Password,
                    email_allowed = x.IsNewsletter,
                    sms_allowed = x.IsSms,
                    verified = x.IsApprovedEmail,
                    social_account_facebook_uuid = "",
                    social_account_facebook_extra = ""
                });

            return Json(customer, JsonRequestBehavior.AllowGet);
        }

Problem is, it comes up as this

"date_joined":"\/Date(1516965473683)\/"

I tried to change it into another format so far I couldn't manage it.

First I tried the usual DateTime formatting as toString("newFOrmat") but I got Linq errors mostly because toString() is not recognized in SQL Server

Then I came across this question format date in linq query result and tried the method there as this

return Json(customer.AsEnumerable().Select(r=>new CustomerDTO{
                date_joined = r.date_joined.GetValueOrDefault().ToString("dd.MM.yyyy")
            }) , JsonRequestBehavior.AllowGet);

I got "DateTime does not have a definition for GetValueOf()" error although I have the correct namespaces included.

Omitting it and using only the usual ToString("format") brought the Linq error above.

Any other suggestions?

EDIT: My DTO and Output has other fields too. I didn't include them in the question.

Seems to me that this date format comes from the conversion to JSON. See [this page](https://www.newtonsoft.com/json/help/html/DatesInJSON.htm) in Json.Net documentation. — Zohar Peled, Jan 26 '18 at 14:48
I don't know why you use `JsonResult` but if this is Web API (2 or core) you can easily change your code style so you never have to worry about Json (de)serialization any more. — Gert Arnold, Jan 26 '18 at 14:54

score 1 · Accepted Answer · answered Jan 26 '18 at 14:46

I think your best bet here is to load in the required data from the database by calling .ToList(), and then map that data to your DTO.
Make sure to change the type on your DTO from DateTime to string.

Something like this:

public JsonResult Customer(int? id)
    {
        var user = User.Identity.Name;
        if (!AccountController.IsInRole(user, "admin"))
        {
            return null;
        }

        id = id == null ? 0 : id;

        var customer = db.PrmCustomers
            .Where(x => x.EmailAddress != null && x.CustomerID > id)
            .OrderBy(x => x.CustomerID).ToList()  //  <-- ToList executes the query
            .Select(x => new CustomerDTO()
            {
                email = x.EmailAddress,
                phone = x.MobilePhoneNumber,
                username = "",
                first_name = x.Name,
                last_name = x.Surname,
                gender = x.GenderType,
                customer_code = x.CustomerID,
                is_active = "",
                date_joined = x.CreateDate.ToString("d/M/yyyy HH:mm:ss"),
                //date_joined = x.CreateDate,
                last_login = "",
                //date_of_birth = x.BirthDate,
                date_of_birth = x.BirthDate.ToString("d/M/yyyy HH:mm:ss"),
                password = x.Password,
                email_allowed = x.IsNewsletter,
                sms_allowed = x.IsSms,
                verified = x.IsApprovedEmail,
                social_account_facebook_uuid = "",
                social_account_facebook_extra = ""
            });

        return Json(customer, JsonRequestBehavior.AllowGet);
    }

Thank you, this worked though I needed to change DTO's type to string too. Got the job done :) — Ege Bayrak, Jan 26 '18 at 14:58
I think, it is not a proper solution to solve the problem. You could modify your web api json serializer settings to keep desired format. — lucky, Jan 26 '18 at 15:08

score 0 · Answer 2 · answered Jan 26 '18 at 14:53

So you basically need to call a Database function to convert your date to a string.

There is an awesome Library to help with mapping Database functions and Stored Procedures to Entity Framework.

I changed the code below to more or less what you need from code that worked for me converting a string to int. so there might be small mistakes that slipped in for changing to a DateTime function.

You can maybe consider mapping the "Format" function of SQL, and then you will be able to pass the date format from linq, but in the example below, i assume you created a function in SQL named DATE_TO_STRING

Install the Nuget package

- Install-Package EntityFramework.Functions

Create Extension Methods for functions:

public static class SQLFunctions
{
   [Function(FunctionType.BuiltInFunction, "DATE_TO_STRING")]
   public static string SqlDateToString(this DateTime value) => Function.CallNotSupported<string>();
}

Map it on your EntityFramework Context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Conventions.Add(new FunctionConvention(typeof(SQLFunctions)));
}

Call your newly mapped "SqlDateToString()" function in your LINQ queries:

.Select(p => p.YourDateColumn.SqlDateToString());

and bobs your uncle.

Another option will be to use MomentJs instead, that can understand that json date format very well, and then you do not need this mapping stuff.

score 0 · Answer 3 · answered Jan 26 '18 at 16:54

I don't like the accepted answer; I suggest you to extend JsonResult (googling you can find many post that use this approach) and override the ExecuteResult method:

public class JsonNetResult : JsonResult
{
  public object Data { get; set; }

  public JsonNetResult()
  {
  }

  public override void ExecuteResult(ControllerContext context)
  {
     HttpResponseBase response = context.HttpContext.Response;
     response.ContentType = "application/json";
     if (ContentEncoding != null)
         response.ContentEncoding = ContentEncoding;
     if (Data != null)
     {
          JsonTextWriter writer = new JsonTextWriter(response.Output) { Formatting = Formatting.Indented };
          JsonSerializer serializer = JsonSerializer.Create(new JsonSerializerSettings());
          serializer.Serialize(writer, Data);
          writer.Flush();
     }
  }
}

and finally:

return new JsonNetResult() { Data = customer };

This code return dates in ISO Format

Changing DateTime format in lambda Expression

3 Answers3

Linked