Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
874 views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...