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

Anthony Pegram · Accepted Answer · 2011-04-30T04:40:00.740

18

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.

edited Apr 30 '11 at 04:40

answered Apr 30 '11 at 04:00

Anthony Pegram

123,721
27
225
246

@Anthony Pegram: thanks for the response. from your code I want to get the distinct value and order by ReportDate in descending order. where I need to add these two in your code. – sfgroups Apr 30 '11 at 04:20
I have updated the answer to include a descending date sort and a distinct prior to the `AsEnumerable()` call. These will then be part of the SQL and handled by the database. – Anthony Pegram Apr 30 '11 at 04:21
@Anthony Pegram, sorry my question is not clear I think. I want get distinct 'yyyy-MMM' value, not the distinct reportdate. – sfgroups Apr 30 '11 at 04:37
@sfgroups, gotcha. In that case, you would move the `.Distinct()` to after `.AsEnumerable()`. I'll update again. – Anthony Pegram Apr 30 '11 at 04:39
`2011-03-23 2011-04-19 2011-04-26 2011-05-26` I have these value. expecting this output: `May-2011 Apr-2011 Mar-2011` – sfgroups Apr 30 '11 at 04:39
@sfgroups, flip the format string, I was simply using the one you provided in code in the question. For your desired format, you need `"MMM-yyyy"`. – Anthony Pegram Apr 30 '11 at 04:40
@Anthony Pegram , Excellent, this is the result I wanted. thanks for the help. – sfgroups Apr 30 '11 at 04:50

Zachary Scott · Answer 2 · 2013-05-03T18:37:58.883

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.

Love the answer, but it kills me when people use classes without indicating the namespace where the class may be found. SqlFunctions is in the System.Data.Objects.SqlClient namespace. You will need a reference to the System.Data.Entity assembly in your project to find it. — Tim, Oct 17 '14 at 15:08

score 2 · Answer 3 · answered Jun 26 '14 at 12:55

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.

score 1 · Answer 4 · answered Aug 23 '16 at 01:20

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

Formatting date in Linq-to-Entities query causes exception

4 Answers4

Linked

Related