生成的 C# LINQ 查询 DateTime 文字
我们使用以下代码生成 SQL 代码来查询 firebird 数据库；
DSRCash.GetAll(x => x.Account.ID == Account.ID
&& x.Date_Record <= dateTO
&& x.Date_Record >= dateFROM).ToList();
dateTO 和 dateFROM 参数都是不可为 null 的 DateTime，这对于它们各自的数据库列来说是相同的。
生成的SQL where子句如下；
where (struct_cas0_.DELETED IS NULL)
and struct_cas0_.ACCOUNT_ID = 372 /* @p0 */
and struct_cas0_.DATE_RECORD <= '2011-02-18T13:00:00.00' /* @p1 */
and struct_cas0_.DATE_RECORD >= '2010-02-17T13:00:00.00' /* @p2 */
您可以看到 DateTime 文字已使用“s”或标准可排序格式进行格式化。 Firebird 似乎不支持这种日期格式，如果我们从日期时间文字中删除“T”，查询将成功执行。
是否可以将正在执行的日期时间转换更改为字符串？
我还应该提到，我们正在使用 NHibernate 作为该项目的 ORM。
We are using the following code to generate SQL code to query against a firebird database;
DSRCash.GetAll(x => x.Account.ID == Account.ID
&& x.Date_Record <= dateTO
&& x.Date_Record >= dateFROM).ToList();
Both dateTO and dateFROM parameters are non nullable DateTime and this is the same for their respective database columns.
The SQL where clause generated is as follows;
where (struct_cas0_.DELETED IS NULL)
and struct_cas0_.ACCOUNT_ID = 372 /* @p0 */
and struct_cas0_.DATE_RECORD <= '2011-02-18T13:00:00.00' /* @p1 */
and struct_cas0_.DATE_RECORD >= '2010-02-17T13:00:00.00' /* @p2 */
You can see the DateTime literal has been formatted using "s" or Standard Sortable format. It appears that Firebird does not support this date format, if we remove the "T" from the datetime literal the query will execute successfully.
Is it possible to change the DateTime conversion to string that is being performed?
I should also mention that we are using NHibernate as an ORM for this project.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我想您可以使用表达式访问者，当遇到日期时，它会采用两条路径。
1. 如果它是常量 - 将其替换为格式正确并转换为日期时间的字符串
2. 如果是 MemberExpression，则不执行任何操作。
这会将您的 sql 更改为
(struct_cas0_.DELETED IS NULL)
和 struct_cas0_.ACCOUNT_ID = 372 /* @p0 */
和 struct_cas0_.DATE_RECORD <= CAST('2011-02-18' AS DATETIME) /* @p1 */
和 struct_cas0_.DATE_RECORD >= CAST('2010-02-18' AS DATETIME) /* @p2 */
I suppose you could use an expression visitor that when encountering a date takes two paths.
1. If it is a constant - replace it with a string formatted correctly with a convert to datetime
2. If it is a MemberExpression do nothing.
This will change your sql to
where (struct_cas0_.DELETED IS NULL)
and struct_cas0_.ACCOUNT_ID = 372 /* @p0 */
and struct_cas0_.DATE_RECORD <= CAST('2011-02-18' AS DATETIME) /* @p1 */
and struct_cas0_.DATE_RECORD >= CAST('2010-02-18' AS DATETIME) /* @p2 */