[ACCEPTED]-Entity Framework 4 / Linq: How to convert from DateTime to string in a query?-tostring

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 3 out the month name where DatePart("mm", ..) provides a numeric 2 value, thus the StringConvert( ), but this left pads the 1 result with spaces, thus the .Trim().

ToString() is not supported in Linq to Entities - there 9 is a list of function helpers as part of 8 SqlFunctions but this doesn't support Date to string 7 conversion.

Easiest would be to first project 6 to an anonymous type within the query and 5 then cast to an IEnumerable by using AsEnumerable() - after that 4 you can use ToString() because you are now using Linq 3 to Objects for the remainder of the query 2 expression (there's a lengthy article on 1 this topic here).

   var results = Products.Select( p => new { a.id, a.modified })
                         .AsEnumerable()
                         .Select(p => new ProductVM() 
                                { id = p.id, 
                                  modified = p.modified.ToString() 
                                });

This may not add a lot, but just in case 22 anyone is as crazy as me, here is the full 21 code if you need to build the expression 20 tree for Dr. Zim's answer using DatePart/DateName 19 including the time part as well. Obviously, for 18 other purposes you can change Product->YourInitialType, ProductVM->YourResultType, and 17 modified->YourProperty.

Edit (1/23/08): The SQL generated 16 from this changed between 6.0.2 and 6.1.3. Initially, if 15 the value were null, the SQL generated would 14 create a null result. I considered this 13 desirable in this case, but I can see why 12 in other cases it would not be desired (null 11 + "a string value" = null) and could cause 10 output not equal to what you would prefer. I'll 9 detail how the column output changed below, but 8 the rub of it is that this now will output 7 "// ::" for null values. I simply handled 6 this output in my calling code as a special 5 case and manually changed it back to null, but 4 others may want to tackle adding more robust 3 results to ensure nulls output as null. It 2 is also worth noting that the SQL statement 1 is very long in the new version.

ParameterExpression paramExp = Expression.Parameter(typeof(Product));
string propertyName = "modified";            
Expression propertyOrField = Expression.PropertyOrField(paramExp, propertyName);

MethodInfo datePartMethod = typeof(System.Data.Entity.SqlServer.SqlFunctions).GetMethods().Where(x => x.Name == "DatePart" && x.GetParameters().Length == 2 && x.GetParameters()[1].ParameterType == typeof(DateTime?)).First();
MethodInfo dateNameMethod = typeof(System.Data.Entity.SqlServer.SqlFunctions).GetMethods().Where(x => x.Name == "DateName" && x.GetParameters().Length == 2 && x.GetParameters()[1].ParameterType == typeof(DateTime?)).First();
MethodInfo stringConvertMethod = typeof(System.Data.Entity.SqlServer.SqlFunctions).GetMethods().Where(x => x.Name == "StringConvert" && x.GetParameters().Length == 1 && x.GetParameters()[0].ParameterType == typeof(decimal?)).First();
MethodInfo stringConcatMethod = typeof(string).GetMethods().Where(x => x.Name == "Concat" && x.GetParameters().Length == 2 && x.GetParameters()[0].ParameterType == typeof(string) && x.GetParameters()[1].ParameterType == typeof(string)).First();
MethodInfo stringTrimMethod = typeof(string).GetMethods().Where(x => x.Name == "Trim" && x.GetParameters().Length == 0).First();
Type projectedType = typeof(ProductVM);
NewExpression newHolder = Expression.New(projectedType);  
MemberInfo member = anonType.GetMember("modified")[0];
var monthPartExpression = Expression.Call(null, datePartMethod, Expression.Constant("mm", typeof(string)), propertyOrField);
var convertedMonthPartExpression = Expression.Call(null, stringConvertMethod, Expression.Convert(monthPartExpression, typeof(decimal?)));
var convertedDayPartExpression = Expression.Call(null, dateNameMethod, Expression.Constant("dd", typeof(string)), propertyOrField);
var convertedYearPartExpression = Expression.Call(null, dateNameMethod, Expression.Constant("yyyy", typeof(string)), propertyOrField);
var convertedHourPartExpression = Expression.Call(null, dateNameMethod, Expression.Constant("hh", typeof(string)), propertyOrField);
var convertedMinutePartExpression = Expression.Call(null, dateNameMethod, Expression.Constant("n", typeof(string)), propertyOrField);
var convertedSecondPartExpression = Expression.Call(null, dateNameMethod, Expression.Constant("ss", typeof(string)), propertyOrField);

var allAddedExpression = Expression.Call(null, stringConcatMethod, 
            convertedMonthPartExpression, 
            Expression.Call(null, stringConcatMethod,
                Expression.Constant("/", typeof(string)), 
                Expression.Call(null, stringConcatMethod, 
                    convertedDayPartExpression, 
                    Expression.Call(null, stringConcatMethod, 
                        Expression.Constant("/", typeof(string)), 
                        Expression.Call(null, stringConcatMethod,
                            convertedYearPartExpression,
                            Expression.Call(null, stringConcatMethod,
                                Expression.Constant(" ", typeof(string)),
                                Expression.Call(null, stringConcatMethod,
                                    convertedHourPartExpression,
                                    Expression.Call(null, stringConcatMethod,
                                        Expression.Constant(":", typeof(string)),
                                        Expression.Call(null, stringConcatMethod,
                                            convertedMinutePartExpression,
                                            Expression.Call(null, stringConcatMethod,
                                                Expression.Constant(":", typeof(string)),
                                                convertedSecondPartExpression

))))))))));
var trimmedExpression = Expression.Call(allAddedExpression, stringTrimMethod, new Expression[] { });    
var month = Expression.Bind(member, trimmedExpression);

MemberInitExpression memberInitExpression =
    Expression.MemberInit(
        newHolder,
        new MemberBinding[] { month });
var lambda = Expression.Lambda<Func<Product, ProductVM>>(memberInitExpression, paramExp);

Create a new POCO with this structure (I'm 6 assuming that the data type is DateTime):

public class UserProductVM {
    ...
    private DateTime _modified;

    public DateTime SetModified { set { _dateEvent = value; } }
    public string Modified { get { return _modified.ToString("dd MMM yyyy @ HH:mm:ss"); } }
    ...
}

Then 5 you assign the value to SetModified, changing 4 your code like this:

from a in Products
select new UserProductVM
{
     ...
     SetModified = a.modified
}

Pay attention i'm using 3 UserProductVM instead ProductVM and SetModified instead modified.

Then when you get 2 the property Modified, the new POCO is gonna bring 1 it as the string that you formatted.