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

entity-framework-4 linq-to-entities tostring
Zachary Scott picture Zachary Scott · Mar 20, 2011 · Viewed 26.1k times · Source

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

Zachary Scott picture Zachary Scott · May 3, 2013

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

Related questions

LEFT JOIN in LINQ to entities?

I'm trying out LINQ to entities. I have a problem with the following: I want it to do this: SELECT T_Benutzer.BE_User ,T_Benutzer_Benutzergruppen.BEBG_BE FROM T_Benutzer LEFT JOIN T_Benutzer_Benutzergruppen ON T_Benutzer_…

Read More
LINQ to Entities how to update a record

Okay, so I'm new to both EF and LINQ. I have figured out how to INSERT and DELETE but for some reason UPDATE seems to escape my grasp. Here is a sample of my code: EntityDB dataBase = new EntityDB(); Customer …

Read More
The specified type member is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported

var result = (from bd in context.tblBasicDetails from pd in context.tblPersonalDetails.Where(x => x.UserId == bd.UserId).DefaultIfEmpty() from opd in context.tblOtherPersonalDetails.Where(x => x.UserId == bd.UserId).DefaultIfEmpty() select new clsProfileDate() { DOB = pd.DOB }); foreach (…

Read More