DateTime to string conversion using LINQ

asp.net-mvc entity entity-framework linq

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();

PostingDate = c.Date.ToString in the code above throws an error when converting a date to a string (). Any suggestions on how to avoid this?

Unexpected error: The function "System.String ToString()" cannot be converted into a store expression because LINQ to Entities does not recognize it.

1
6
7/31/2011 8:20:16 AM

Accepted Answer

Joakim, Due to the lack of a ToString() function in SQL, linq tries to convert a date to a string but is unsuccessful; this behavior is intentional.

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

(
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(),
        [...]
    })
17
7/31/2011 6:04:49 AM

Popular Answer

By projecting into an anonymous type and then afterwards projecting into a specific type, you may solve your issue.Post once the DB has already sent the data back.

(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 with the name PostingDate but the original source was to be a date, I would advise you to modify your object so that the value is truly retained as a property.DateTime as opposed to a string.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow