LINQ convert DateTime to string

Question

List<Post> list =
(
    from c in db.TitleComments
    join t in db.Titles on c.TitleId equals t.Id
    join u in db.Users on c.UserId equals u.Id
    where t.Id == _titleId && c.Date > time
    orderby c.Date descending
    select new Post { Username = u.Username, PostingDate = c.Date.ToString(), Data = c.Comment }
).ToList();

The code above causes exception on the convertion of date to string, PostingDate = c.Date.ToString(). Any ideas how to get around this?

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

@BrunoLM, linq is trying to convert date to string using sql but since there is no ToString() method in sql it can't convert it, this behavior is by design i believe. — Joakim, Jul 31 '11 at 05:48
can you give us the exact exception. I dont believe that this is an error thrown in SQL. — Baz1nga, Jul 31 '11 at 05:55

score 17 · Accepted Answer · answered Jul 31 '11 at 06:04

linq is trying to convert date to string using sql but since there is no ToString() method in sql it can't convert it, this behavior is by design - Joakim

In other words, return the date itself and convert it to a string after it executes on SQL side:

(
select new { Username = u.Username,
    PostingDate = c.Date
    [...]
})
.ToList() // runs on SQL and returns to the application
.Select(o =>  // is not generating a SQL, it is running on the app
    new Post { Username = o.Username,
        PostingDate = o.PostingDate.ToString(),
        [...]
    })

score 2 · Answer 2 · answered Jul 31 '11 at 06:00

You can remedy your problem by projecting into an anonymous type, and then at a later step project into Post after the data has already been returned from the DB.

(from ....
 select new { /* stuff */, Date = c.Date })
.AsEnumerable()
.Select(p => new Post { /* stuff */, PostingDate = p.Date.ToString() })
.ToList();

However, given that you have a property called PostingDate, the original source being a date, I would recommend your revise your object to actually keep the value as a DateTime instead of a string.

I feel that is what I will do, make PostingDate as DateTime type. I wanted to save the client from having to perform the date formatting, but it looks like it can't be helped in this case. — Ronald, Jul 31 '11 at 06:10

Baz1nga · Answer 3 · 2011-07-31T06:13:58.080

I dont think this can be done in a direct way.

   var list =    
    select new Post { Username = u.Username, PostingDate =  SqlFunctions.StringConvert(c.Date), Data = c.Comment } 
from 
(from c in db.TitleComments
    join t in db.Titles on c.TitleId equals t.Id
    join u in db.Users on c.UserId equals u.Id
    where t.Id == _titleId && c.Date > time
    orderby c.Date descending).AsEnumerable()  
    ).ToList();

Also with EF4 you can try something like this:

List<Post> list =
(
from c in db.TitleComments
join t in db.Titles on c.TitleId equals t.Id
join u in db.Users on c.UserId equals u.Id
where t.Id == _titleId && c.Date > time
orderby c.Date descending
select new Post { Username = u.Username, PostingDate = SqlFunctions.DateName(c.Date), Data = c.Comment }
).ToList();

LINQ convert DateTime to string

3 Answers3

Linked