Convert datetime to a formatted string inside a LINQ to SQL query

Question

Convert datetime to a formatted string inside a LINQ to SQL query

entity-framework linq linq-to-sql

Question

I know that LINQ to SQL does not convert DateTime to string since there is no ToString() in SQL. But how can I convert the 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.

kandroid

Popular Answer

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.

Alberto Montellano

View more on Stack Overflow

mccow002 · Accepted Answer · 11/3/2011 7:09:18 PM

EDIT: Now that I understand the question, I'm giving it another shot :)

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=p.StartDate

                     })
                     .ToList()
                     .Select(x => new Offer()
                     {
                         Title = x.OfferTitle,
                         Description = x.Description,
                         BestOffer=value,
                         ID=x.ID,
                         LocationID=x.BranchID,
                         LocationName=x.CustomerBranch.BranchName,
                         OriginalPrice=x.OriginalPrice,
                         NewPrice=x.NewPrice,
                         StartDate=x.StartDate.ToString("dd.MM.yy")
                     }).First();

I know it's a bit long, but that's the problem with Linq To SQL.

When you use linq, the database call isn't executed until you use something such as ToList() or First() that results in actual objects. Once that SQL call is executed by the first .First() call, you're now working with .NET types, and can use DateTime stuff.

Convert datetime to a formatted string inside a LINQ to SQL query

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related