500 Error when converting nullable DateTime to string in IQueryable

Question

I have the following code where I get data from db context and convert the nullable DateTime DateOfUpdate to a string:

var result = from data in context.releventData
select new ReleventData
{
    Id = data.Id,
    Status = data.Status,
    DateOfUpdate = data.DateOfUpdate.HasValue ? data.DateOfUpdate.Value.ToString("dd-MM-yyyy") : string.Empty,
};

When ToList() on result gets executed, I get stuck and the browser gives me an 500 Internal Server Error underwater.

When I use this: DateOfUpdate = string.Empty it works totally fine so I'm probably doing something wrong to convert the DateTime to string.

Any idea how to fix this?

Answer 1

First. As far as I can see this does not belong to Linq query aansluiting. What is it ? Secondly why don't you try to materialize the data first calling .ToList() so if something bad happens you can see right away and not just a 500 error.

I guess calling .ToString("dd-MM-yyyy") inside the Linq is not a wise move since Linq is not happy with that function. Try to retrieve the data first and then convert them to your format. In your case you end up getting this error:

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

You could do something like this:

 var result = context.releventData.ToList().Select(data => new ReleventData
 {
     Id = data.Id,
     Status = data.Status,
     DateOfUpdate = data.DateOfUpdate.HasValue ? data.DateOfUpdate.Value.ToString("dd-MM-yyyy") : string.Empty,
 }).ToList();

BUT this is not best solution since you end up materializing all of your data which means all table data will be processed in your application which is overkilling unless the table is small.

So the best solution to this:

Get the data you need from the Linq query(Only what you need). Then materialize these data and format them as you want and then show to the user.

My own opinion: It should not be the responsibility of Linq query to generate the data format. This should be done in upper layer of the application or at least after you have materialized your data.