using ToString() in linq expression to convert DateTime value

Question

I'm trying to convert a DateTime value to a string, but I'm getting this runtime error:

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

This is the query I'm using:

using (var db = new DbContext())
{
    var results = (from u in db.AspNetUserses
        join upi in db.UserPersonalInfoes on u.Id equals upi.UserId into upis
        from upi in upis.DefaultIfEmpty()
        join up in db.UserPreferenceses on u.Id equals up.UserId into ups
        from up in ups.DefaultIfEmpty()
                   join us in db.UserStatses on u.Id equals us.UserId into uss
                   from us in uss.DefaultIfEmpty()
        select new
        {
            Username = u.UserName,
            Telephone = (upi == null ? String.Empty : upi.Telephone),
            ID = u.Id,
            LastLogin = (us == null ? String.Empty : us.LastLoginDate) 
        }).ToList();

    gvUsers.DataSource = results;
    gvUsers.DataBind();

}

Any idea how can I fix this error?

Um, where in that code are you converting a `DateTime` to a `string`? Did you mean to write `us.LastLoginDate.ToString()`? — Jon Skeet, Jan 20 '15 at 19:57

score 1 · Accepted Answer · answered Jan 20 '15 at 20:21

Create a second list and do the transformation there.

using (var db = new DbContext())
{
    var results = (from u in db.AspNetUserses
        join upi in db.UserPersonalInfoes on u.Id equals upi.UserId into upis
        from upi in upis.DefaultIfEmpty()
        join up in db.UserPreferenceses on u.Id equals up.UserId into ups
        from up in ups.DefaultIfEmpty()
               join us in db.UserStatses on u.Id equals us.UserId into uss
               from us in uss.DefaultIfEmpty()
        select new
        {
            Username = u.UserName,
            Telephone = (upi == null ? String.Empty : upi.Telephone),
            ID = u.Id,
            LastLogin = (us == null ? DateTime.MinValue : us.LastLoginDate) 
        }).ToList();

       var list = (from r in results
                   select new {
                     Username = r.UserName,
                     Telephone = r.Telephone,
                     ID = r.ID
                     LastLogin = r.LastLogin == DateTime.MinValue ? "" : r.LastLogin.ToString()
                    }).ToList();


      gvUsers.DataSource = list;
      gvUsers.DataBind();
}

yeah.... LINQ struggles with converting in-query like this. You can also just `Select(x => new {...}` right after the first call to ToList(), or use SqlFunctions.DateName, as shown here: [LINQ Date conversion](http://stackoverflow.com/questions/7999771/convert-datetime-to-string-inside-a-linq-query). — DrewJordan, Jan 20 '15 at 20:29

using ToString() in linq expression to convert DateTime value

1 Answers1