LINQ convert SQL server Datetime to string using a DTO

Question

I've been tasked to add a page to an API that we didn't build but are tasked to work on. The API is using C# MVC5. I don't really know MVC5, and but I'm attempting to keep the same design pattern that the rest of the API is using. I have to add functionality that will allow a user on the front end to upload a file to a server that will be processed for inserting into a SQL Server DB. This functionality will also return a list of all the files names and status of the imported files from a table in the DB.

The issue I'm having is converting a Datetime to a string in the LINQ query that is pulling the list of files.

I've attempted to try using this answer LINQ convert DateTime to string, but with no luck.

This is what I have so far, I've marked the line that is causing the issue:

[Route("ImportLogs", Name ="GetImportLogs")]
[HttpGet]
[ResponseType(typeof(List<IHttpActionResult>))]
public IHttpActionResult GetImportLogs()
{
    var query =
        from dbitem in db.ImportLogs
        orderby dbitem.import_log_id
        select new ImportLogDto()
        {
            Id = dbitem.import_log_id,
            FileName = dbitem.import_file_name,
            ImportTimeStamp = dbitem.import_timeStamp,
            ImportStatus = dbitem.import_status,
            ImportMessage = dbitem.import_message
         };

         query.ToList()
             .Select(o => new ImportLogDto
             {
                 Id = o.Id,
                 FileName = o.FileName,
                 ImportMessage = o.ImportMessage,
                 ImportStatus = o.ImportStatus,
                 ImportTimeStamp = o.ImportTimeStamp.ToString() //PROBLEM LINE
              });

         return Ok(query);
}

The error that I'm getting is

Cannot implicitly convert type 'string' to 'System.DateTime'

What am doing wrong? Any help would be appreciated. TIA.

EDIT:

Here is the DTO:

public class ImportLogDto
{
    public int Id { get; set; }
    public string FileName { get; set; }
    public DateTime ImportTimeStamp { get; set; }
    public string ImportStatus { get; set; }
    public string ImportMessage { get; set; }

}

What is the type of `ImportLogDto.ImportTimeStamp`? (If you could please show the class) Seems like it is a `DateTime` and not a `string` — Gilad Green, Apr 21 '17 at 13:25
If it's already a `DateTime`, and you're trying to store it in a model that expects a `DateTime`, why are you trying to convert it to a string in the first place? It's not really clear what you're trying to accomplish here. — David, Apr 21 '17 at 13:27
The datetime looks like this when I access the api `2017-04-19T16:11:25.875883`, so I'm trying to convert it to something more readable — Rob M, Apr 21 '17 at 13:29

Gilad Green · Accepted Answer · 2017-04-21T13:36:48.773

You are trying to assign a string into a DateTime so you get that exception. If you want to cast it to a string change your model as follows:

public class ImportLogDto
{
    public int Id { get; set; }
    public string FileName { get; set; }
    public string ImportTimeStamp { get; set; } // Changed type
    public string ImportStatus { get; set; }
    public string ImportMessage { get; set; }    
}

And then your query:

var query = (from dbitem in db.ImportLogs
             orderby dbitem.import_log_id
             select new {
                 Idbitem.import_log_id,
                 dbitem.import_file_name,
                 dbitem.import_timeStamp, // Still DateTime
                 dbitem.import_status,
                 dbitem.import_message
             }).AsEnumerable(); // Retrieved from Database
            .Select(o => new ImportLogDto
            {
                Id = o.import_log_id,
                FileName = o.import_file_name,
                ImportMessage = o.import_message,
                ImportStatus = o.import_status,
                ImportTimeStamp = o.import_timeStamp.ToString() // Changes to string
            });

If you wnat to change the format of the DateTime for the API then then use its overload of ToString and specify a format:

ImportTimeStamp = o.ImportTimeStamp.ToString("dd/MM/yyyy HH24:MI:ss")

For more on the overload read: Custom Date and Time Format Strings

I tried that, but when I do I get an error on `ImportTimestamp = dbitem.import_timestamp` with the error of `Cannot implicitly convert type 'System.DateTime' to 'string'` — Rob M, Apr 21 '17 at 13:30
@RobM - yes - you can't have the DTO as a string and then in the database do that assignment (you'll get that exception) but as you did - retrieve the data using `ToList` or `AsEnumerable` and then you can cast it — Gilad Green, Apr 21 '17 at 13:32

score 1 · Answer 2 · answered Apr 21 '17 at 13:36

Your types are already DateTime, and since these types are tied to the backing data* you probably shouldn't change them. But where you return the values on the API you can really return anything you want. So an anonymous type could be a quick solution:

.Select(o => new // <--- notice no type definition here
{
    Id = o.Id,
    FileName = o.FileName,
    ImportMessage = o.ImportMessage,
    ImportStatus = o.ImportStatus,
    ImportTimeStamp = o.ImportTimeStamp.ToString()
})

The compiler will know based on the type returned by .ToString() that you want ImportTimeStamp to be a string. You can then add formatters to .ToString() to customize the output however you like.

*If your DTO isn't actually tied to the database then you can change the type there from DateTime to string, of course. It's not really clear from the context of the code shown whether these are data DTOs or application DTOs.

Like I said in my question, I'm not familiar with the design pattern, so I can't answer if the DTO is an application or data DTO. There is a EDMX file with a "db" diagram that can be used to `Generate Database from Model`. I created this new import table in sql server, and used the `Update Model from Database` option. I don't know if that helps — Rob M, Apr 21 '17 at 13:44
@RobM: Probably best not to change the types if you don't know the repercussions then, *especially* if that DTO is a generated type. Returning an anonymous type from the API should be fine though. Type information doesn't really matter at that point, it's just serialized data. (Unless there's more custom tweaking outside of what's shown in the question that is.) — David, Apr 21 '17 at 13:47

LINQ convert SQL server Datetime to string using a DTO

2 Answers2