Linq-to-Entities: Format Date in select query expression

Question

I am trying to get a formatted date string directly from a LINQ-to-Entities query expression.

nonBusinessDays = (from ac in db.AdminCalendar
                   where ac.DateTimeValue >= calendarStartDate && ac.DateTimeValue <= calendarEndDate && ac.IsBusinessDay == false
                   select ac.MonthValue + "/" + ac.DayOfMonth + "/" + ac.FullYear).ToList();

But, I get the folloinw error message: "Unable to cast the type 'System.Nullable`1' to type 'System.Object'. LINQ to Entities only supports casting Entity Data Model primitive types."

Is there any way to do this besides iterating through the result set? Thanks! Abe

Answer 1

I found one workaround:

 nonBusinessDays = (from dt in
                            (from ac in db.AdminCalendar
                             where ac.DateTimeValue >= calendarStartDate && ac.DateTimeValue <= calendarEndDate && ac.IsBusinessDay == false
                             select ac.DateTimeValue).ToList()
                    select string.Format("{0:M/d/yyyy}", dt)).ToList();

Answer 2

Try changing your select to use ToString:

nonBusinessDays = (from ac in db.AdminCalendar
                   where ac.DateTimeValue >= calendarStartDate && ac.DateTimeValue <= calendarEndDate && ac.IsBusinessDay == false
                   select ac.DateTimeValue.ToString("MM/dd/yyyy")).ToList();

Answer 3

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