将日期时间转换为 LINQ-to-entities 查询中的格式化字符串
如何将 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
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();
我收到以下错误消息：
LINQ to Entities 无法识别“System.String ToString(System.String)”方法，并且该方法无法转换为存储表达式。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
另一种选择是使用 SqlFunctions.DateName，你的代码将是这样的：
如果你不想添加额外的选择新块，我发现它很有用。
Another option is using SqlFunctions.DateName, your code will be like this:
I found it useful if you don't want to add an extra select new block.
编辑：现在我明白了这个问题，我再试一次:)
我知道它有点长，但这就是 Linq To SQL 的问题。
当您使用 linq 时，数据库调用不会执行，直到您使用 ToList() 或 First() 等产生实际对象的内容。一旦第一个 .First() 调用执行了该 SQL 调用，您现在就可以使用 .NET 类型，并且可以使用 DateTime 内容。
EDIT: Now that I understand the question, I'm giving it another shot :)
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.
我最终使用了sql函数
FORMAT；这是此实现的简化版本：https://weblogs.asp.net/ricardoperes/registering-sql-server-built-in-functions-to-entity-framework-code-first
首先您需要定义函数在 EF 中：
然后将其定义为 C# 方法：
将其注册到您的
DbContext中：最后，您可以像这样调用它：
I ended up using the sql function
FORMAT; here's a simplified version of this implementation:https://weblogs.asp.net/ricardoperes/registering-sql-server-built-in-functions-to-entity-framework-code-first
First you need to define the function in EF:
Then define it as C# methods:
Register it in your
DbContext:And finally, you can call it like so:
这就是我们所做的，我们向类添加了一个新函数，并在查询中像平常一样查询日期：
然后您可以调用 FormattedDate 字段并传递所需的格式。
或者可以将其定义为仅包含 getter 的字段：
如果您的类是实体，则需要声明该类的新部分并添加该字段。
希望这对某人有帮助。
That is what we did, we added a new function to the class and we query the date as normal in the query:
Then you can just call the FormattedDate field passing the desired format.
Or can can define it as a field with just the getter:
In case you class is an Entity, you need to declare a new partial of that class and add the field.
Hope this help someone.
在 vb 中（对 c# 也有效，语法也发生了变化）：
注意：ToList()、ToEnumerable() 不是方法，因为它执行查询，用户想要 linq to sql..
In vb (valid to c# too changing syntax):
Note: ToList(), ToEnumerable() are not the way because its executes a query, the user wants linq to sql..
我发现对数字或日期时间对象执行字符串格式的最简单且最有效的方法是使用字符串插值。它将带回 SQL 查询中实际的 DateTime/int/float/double/etc.. 对象，然后客户端将在投影期间执行字符串格式。我在下面修改了您的查询，请注意 OriginalPrice、NewPrice 和 StartDate 是如何转换的：
The easiest and most efficient way I have found to do string formats on numeric or datetime objects is by using string interpolation. It will bring back the actual DateTime/int/float/double/etc.. objects in the SQL query, and then client side it will do the string format during projection. I modified your query below, note how OriginalPrice, NewPrice, and StartDate are converted:
对于仅 SqlFunctions 的答案，这也将 0 填充日期和月份，可与 null 一起使用，并且不需要 ToList AsEnumerable 或任何其他方法首先将数据带入内存。
For a SqlFunctions only answer, this will also 0 pad the day and month, works with nullable, and does not need ToList AsEnumerable or any other method to bring the data to memory first.
如果它是日期时间，则需要使用
.ToShortDateString()。但您还需要将其声明为 AsEnumerable()。if it's a datetime you need to use the
.ToShortDateString(). But you also need to declare it AsEnumerable().