Entity Framework 4 / Linq: How to convert from DateTime to string in a query?

Question

I have the following query:

from a in Products
select new ProductVM
    {
         id = a.id,
         modified = a.modified.ToString()
    }

Which gives me an error of:

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

The modified in the Products table is DateTime. The modified in the ProductVM class is string.

Any ideas? This has to be a trivial issue.

Answer 1

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().

Answer 2

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

Easiest would be to first project to an anonymous type within the query and then cast to an IEnumerable by using AsEnumerable() - after that you can use ToString() because you are now using Linq to Objects for the remainder of the query expression (there's a lengthy article on 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() 
                                });

Answer 3

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

Edit (1/23/08): The SQL generated from this changed between 6.0.2 and 6.1.3. Initially, if the value were null, the SQL generated would create a null result. I considered this desirable in this case, but I can see why in other cases it would not be desired (null + "a string value" = null) and could cause output not equal to what you would prefer. I'll detail how the column output changed below, but the rub of it is that this now will output "// ::" for null values. I simply handled this output in my calling code as a special case and manually changed it back to null, but others may want to tackle adding more robust results to ensure nulls output as null. It is also worth noting that the SQL statement 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);

Answer 4

Create a new POCO with this structure (I'm 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 you assign the value to SetModified, changing your code like this:

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

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

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