Formatting date in Linq-to-Entities query causes exception

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.开发者_如何学JAVAString ToString(System.String)' method, and this method cannot be translated into a store expression.

How can I correct this error message?

Thanks SR

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.

Here's an alternative:

.Select( p -> SqlFunctions.StringConvert((double)
                  SqlFunctions.DatePart("m", p.modified)).Trim() + "/" +
              // SqlFunctions.DateName("mm", p.modified) + "/" + MS ERROR?
              SqlFunctions.DateName("dd", p.modified) + "/" +
              SqlFunctions.DateName("yyyy", p.modified)

Apparently DateName("MM", ..) spells out the month name where DatePart("mm", ..) provides a numeric value, thus the StringConvert( ), but this left pads the result with spaces, thus the .Trim().

Like Anthony Pegram said above, this happens in the database rather than in C# (.AsEnumerable() pulls all data local to C# so make sure you filter data prior to using it.)

Obviously you'd want to rearrange the output slightly to fit yyyy-MM and use either DatePart for the digit or DateName for the month name.

Here's an alternative that uses the common dd/MM/yyyy format. Confirmed on SQL Server 2012 with Entity Framework 5

invoices.Select(i => new 
{
    FormattedDate = (     EntityFunctions.Right(String.Concat(" ", SqlFunctions.StringConvert((double?) SqlFunctions.DatePart("dd", i.DocumentDate))), 2)
                        + "/"
                        + EntityFunctions.Right(String.Concat(" ",SqlFunctions.StringConvert((double?) SqlFunctions.DatePart("mm", i.DocumentDate))), 2)
                        + "/"
                        + EntityFunctions.Right(SqlFunctions.StringConvert((double?) SqlFunctions.DatePart("yyyy", i.DocumentDate)), 4)
                       ).Replace(" ", "0")
}

Produces dates in a format dd/MM/yyyy with leading zeros.

I used a workaround to build the formats containing yyyy and MM manually. I would like to mention it here if it may help someone (at least temporarily):

FormattedDate = d.ReportDate.Year.ToString() + "-" + d.ReportDate.Month.ToString()

this makes the format yyyy-MM