entity framework - Convert datetime to a formatted string inside a LINQ-to-entities query

Question

How can I convert DateTime into a formatted string?

This is the line in the following query that needs help:

StartDate = string.Format("{0:dd.MM.yy}", p.StartDate)

The whole query:

var offer = (from p in dc.CustomerOffer
             join q in dc.OffersInBranch
             on p.ID equals q.OfferID
             where q.BranchID == singleLoc.LocationID
             let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
             orderby value descending
             select new Offer()
             {
                 Title = p.OfferTitle,
                 Description = p.Description,
                 BestOffer = value,
                 ID = p.ID,
                 LocationID = q.BranchID,
                 LocationName = q.CustomerBranch.BranchName,
                 OriginalPrice = SqlFunctions.StringConvert((decimal)p.OriginalPrice),
                 NewPrice = SqlFunctions.StringConvert((decimal)p.NewPrice),
                 StartDate = string.Format("{0:dd.MM.yy}", p.StartDate)
             }).First();

I get the following error message:

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

Answer 1

Another option is using SqlFunctions.DateName, your code will be like this:

var offer = (from p in dc.CustomerOffer
                 join q in dc.OffersInBranch
                     on p.ID equals q.OfferID
                 where q.BranchID == singleLoc.LocationID
                 let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
                 orderby value descending
                 select new
                 {
                     Title = p.OfferTitle,
                     Description = p.Description,
                     BestOffer = value,
                     ID = p.ID,
                     LocationID = q.BranchID,
                     LocationName = q.CustomerBranch.BranchName,
                     OriginalPrice = SqlFunctions.StringConvert((decimal)p.OriginalPrice),
                     NewPrice = SqlFunctions.StringConvert((decimal)p.NewPrice),
                     StartDate = SqlFunctions.DateName("day", p.StartDate) + "/" + SqlFunctions.DateName("month", p.StartDate) + "/" +  SqlFunctions.DateName("year", p.StartDate)
                 })

I found it useful if you don't want to add an extra select new block.