Converting a date time field to string in linq

Question

I am trying to convert a datetime field to a string. The error message I got is found below. Have you seen this before? I am not getting a solution. How can I resolve this please?

            public IEnumerable<SelectListItem> GetStatusDate(string strprojectId)
                    {
                        var queryResult = (from dt in _objContext.tbl_Project_Status_MSTR
                            where dt.ProjectID.Equals(strprojectId)
                            orderby dt.ProjectID
                            select new SelectListItem {Text = Convert.ToString(dt.StatusDate),Value = Convert.ToString(dt.StatusDate)});
                        List<SelectListItem> objStatDate = queryResult.ToList();

                        return objStatDate;
                    }

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

You can use dt.StatusDate.ToString() which will be converted to expression. — vendettamit
– vendettamit, Commented May 11, 2016 at 19:28

Yacoub Massad · Accepted Answer · 2016-05-11 19:28:39Z

3

One way to do it is to query StatusDate from the database, and then convert it to string in memory like this:

var objStatDate =
    (from dt in _objContext.tbl_Project_Status_MSTR
    where dt.ProjectID.Equals(strprojectId)
    orderby dt.ProjectID
    select new {dt.StatusDate})
    .ToList() //Execute database query
    .Select(x =>
        new SelectListItem
        {
            Text = Convert.ToString(x.StatusDate), //Convert data in memory
            Value = Convert.ToString(x.StatusDate)
        })
    .ToList();

answered May 11, 2016 at 19:28

Yacoub Massad

27.9k2 gold badges42 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Baba Over a year ago

Big thanks to everyone. It works now with the solution above. Appreciate it.

Baba Over a year ago

Will I be able to cut off the time from the code above?

Yacoub Massad Over a year ago

Yes. I think you can do it both at the database side (there are special functions that translate to SQL functions (see DbFunctions)) and at the local side by using x.StatusDate.Date.

Baba Over a year ago

Thanks. will try that.

Yacoub Massad Over a year ago

If you don't want to display time when you convert, you can use x.StatusDate.ToString(.. (see the documentation here) with a special formatting string that does not include the time.

zfrank · Accepted Answer · 2016-05-11 19:34:07Z

EF doesn't have a corresponding database function for Convert.ToString(), so it spits up that error.

The easiest way would be to do something like this, that first hits the database to only get the date, then converts to a string once it's all in memory as a normal collection.

                    var queryResult = (from dt in _objContext.tbl_Project_Status_MSTR
                        where dt.ProjectID.Equals(strprojectId)
                        orderby dt.ProjectID
                        select dt.StatusDate)
                        .AsEnumerable()
                        .Select(d => new SelectListItem {Text = Convert.ToString(d),Value = Convert.ToString(d)});

Although I might recommend using d.ToString() instead of convert, especially since you can use string formatting and stuff that way. You could also just slap a .ToList() onto the end of the query and return that, instead of doing it on the next line.

mohsen · Accepted Answer · 2016-05-11 20:08:20Z

0

Use this

List<string> ListOfDateString = (from dt in ListOfDateTime let str=dt.ToString() select str).ToList();

edited May 11, 2016 at 20:08

answered May 11, 2016 at 19:31

mohsen

1,8143 gold badges23 silver badges63 bronze badges

Collectives™ on Stack Overflow

Converting a date time field to string in linq

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related