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