将datetime转换为LINQ to SQL查询中的格式化字符串 [英] Convert datetime to a formatted string inside a LINQ to SQL query
问题描述
DateTime
转换为 string
,因为没有 SQL中的ToString()
但是如何将
DateTime
转换成格式化的字符串? 这是以下查询需要帮助:
StartDate = string.Format({0:dd.MM.yy},p。 StartDate)
整个查询:
var offer =(from p in dc.CustomerOffer
join q in dc.OffersInBranch
on p.ID equals q.OfferID
其中q.BranchID = = singleLoc.LocationID
let value =(p.OriginalPrice - p.NewPrice)* 100 / p.OriginalPrice
orderby value descending
select new Offer()
{
标题= p.OfferTitle,
描述= p.Description,
BestOffer = value,
ID = p.ID,
LocationID = q.BranchID,
LocationName = q.CustomerBranch.BranchName,
OriginalPr ice = SqlFunctions.StringConvert((decimal)p.OriginalPrice),
NewPrice = SqlFunctions.StringConvert((decimal)p.NewPrice),
StartDate = string.Format({0:dd.MM。 yy},p.StartDate)
})。First();
我收到以下错误消息:
LINQ to Entities不会识别方法'System.String ToString(System.String)'方法,并且此方法不能转换为存储表达式。
编辑:现在我明白了这个问题,我再给一个照片:)
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,
描述= 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,
描述= 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();
我知道这有点长,但这是Linq To SQL的问题。
当您使用linq时,数据库调用不会执行,直到您使用导致实际对象的ToList()或First()等等。一旦这个SQL调用由第一个.First()调用执行,你现在正在使用.NET类型,并且可以使用DateTime的东西。
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.
这篇关于将datetime转换为LINQ to SQL查询中的格式化字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!