c# - Formatting date in Linq-to-Entities query causes exception

Question

I have Entity class with datetime filed, I want to select distinct 'mon-yyyy' format datetime filed value and populate drop down list.

the following code giving me the error:

var env = db.Envelopes.Select(d => new
        {
            d.ReportDate.Year,
            d.ReportDate.Month,
            FormattedDate = d.ReportDate.ToString("yyyy-MMM")
        }).Select(d => d.FormattedDate)

    List<SelectListItem> _months = new List<SelectListItem>();         

    foreach (var mname in env)
    {
        _months.Add(new SelectListItem() { Text = mname, Value = mname });
    }

Error message:

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

How can I correct this error message?

Thanks SR

Answer 1

Remember that your query is going to be translated to SQL and sent to the database. Your attempt to format the date is not supported in the query, which is why you are seeing that particular error message. You need to retrieve the results and then format after the data has been materialized.

One option is to simply select the date as it is. As you iterate over the result, format it as you add it to your list. But you can also achieve the construction of the list with the formatted date in a single statement by using method chaining.

List<SelectListItem> _months = db.Envelopes.OrderByDescending(d => d.ReportDate)
        .Select(d => d.ReportDate)
        .AsEnumerable() // <-- this is the key method
        .Select(date => date.ToString("MMM-yyyy"))
        .Distinct()
        .Select(formattedDate => new SelectListItem { Text = formattedDate, Value = formattedDate })
        .ToList(); 

The method .AsEnumerable() will force the execution of the first portion of the query against the database and the rest will be working with the results in memory.