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.