[Question] How can I format a datetime (nullable) field value in select?

[winsonet]

I want to format a nullable datetime value, how can I do that?

For example in Linq:

var expected1 = _context.Blogs.Select(x =>
    (string)((DateTime)(x.ModifyDate ?? x.ModifyDate.Value)).ToString("yyyy-MM-dd")).ToArray();

and I tried in dynamic Linq:

var test1 = _context.Blogs.Select<string>(
    "(string)((datetime)(ModifyDate ?? ModifyDate.Value)).ToString(\"yyyy-MM-dd\")").ToArray();

but I got the below error:

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

any ideas?

thanks!!

[jotab123]

I don't know if it's possible to do in Select but you could do:

var test1 = _context.Blogs.AsQueryable().Where("ModifyDate != null")
.Select("ModifyDate.Value.ToString(\"yyyy-MM-dd\")");

[winsonet]

jotab123, thanks for your reply, but this is not my purpose, in your way, just find the not null value, but actually, I need to find all values, if it's null then will show empty or other thing, otherwise will format it and display

[StefH]

Why not just execute the ToString method on a list instead of the queryable ? In that case you don't have issues with Linq-SQL.

[winsonet]

sorry, can you give me a sample how to do that? thanks!

[StefH]

var list = _context.Blogs.Select("it.ModifyDate").ToDynamicList<DateTime>();
var result = list.Select(x => x ?? x.Value)).ToString("yyyy-MM-dd")).ToList();

[winsonet]

but for my case, I need to generate a full dynamic query for get the result, and I don't want to convert to list at first, because I have a lot of data in the table, if it to list first then will get all data and the performance will be bad, that why I need to use IQueryable .

and I know if I convert to list object then I can easy to use HasValue method in the query.

[StefH]

Did you already try the ? operator ?

Like

var q = _context.Blogs.AsQueryable();
var x = q.Select("it.ModifyDate != null ? [[[format to string]]] : null");