C# – Formatting date in Linq-to-Entities query causes exception

centity-framework-4linqlinq-to-entities

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

Best Answer

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.

Related Solutions

C# – LINQ query on a DataTable

You can't query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. You need to use the AsEnumerable() extension for DataTable. Like so:

var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("RowNo") == 1
select myRow;

And as @Keith says, you'll need to add a reference to System.Data.DataSetExtensions

AsEnumerable() returns IEnumerable<DataRow>. If you need to convert IEnumerable<DataRow> to a DataTable, use the CopyToDataTable() extension.

Below is query with Lambda Expression,

var result = myDataTable
    .AsEnumerable()
    .Where(myRow => myRow.Field<int>("RowNo") == 1);

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

Just save the string to a temp variable and then use that in your expression:

var strItem = item.Key.ToString();

IQueryable<entity> pages = from p in context.pages
                           where  p.Serial == strItem
                           select p;

The problem arises because ToString() isn't really executed, it is turned into a MethodGroup and then parsed and translated to SQL. Since there is no ToString() equivalent, the expression fails.

Note:

Make sure you also check out Alex's answer regarding the SqlFunctions helper class that was added later. In many cases it can eliminate the need for the temporary variable.

Best Answer

Related Solutions

C# – LINQ query on a DataTable

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

Note:

Related Topic