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

i have entity class datetime filed, want select distinct 'mon-yyyy' format datetime filed value , populate drop down list.

the following code giving me 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 entities not recognize method 'system.string tostring(system.string)' method, , method cannot translated store expression.

how can correct error message?

thanks sr

remember query going translated sql , sent database. attempt format date not supported in query, why seeing particular error message. need retrieve results , format after data has been materialized.

one option select date is. iterate on result, format add list. can achieve construction of list formatted date in single statement using method chaining.

list<selectlistitem> _months = db.envelopes.orderbydescending(d => d.reportdate)         .select(d => d.reportdate)         .asenumerable() // <-- key method         .select(date => date.tostring("mmm-yyyy"))         .distinct()         .select(formatteddate => new selectlistitem { text = formatteddate, value = formatteddate })         .tolist();  

the method .asenumerable() force execution of first portion of query against database , rest working results in memory.