使用 HQL 查找 Null ID
我正在构建一个 hql 查询，需要返回用户 ID 为空的订单：
IList<Order> rows = DataContext.LoadList<Order>(
"from Order " +
"where OrderDate < ? " +
" and User.ID is null" //have also tried 'and User is null'
, DateTime.Today.AddMonths(-1));
即使对数据库的直接查询返回几十条记录，也不会返回任何记录。
如果我使用相同的查询，但将“is null”替换为“33”（有效的用户ID），我将获得该用户的订单。所以查询的基础是有效的 - 我表达“is null”位的方式有问题。
可能与以下事实有关：在 .NET 项目中，Order 对象包含一个复杂的 User 对象，而在数据库中，Orders 表只保存整数 (ID)，它是 User 表的外键。
尽管如此 - 由于“and User.ID = 33”按预期工作，我不明白为什么“为空' 没有。
这个答案表明我需要切换 Criteria 路由。 这个答案表明我当前的代码应该可以工作。
谢谢
I'm constructing a hql query that needs to return orders where the User's ID is null:
IList<Order> rows = DataContext.LoadList<Order>(
"from Order " +
"where OrderDate < ? " +
" and User.ID is null" //have also tried 'and User is null'
, DateTime.Today.AddMonths(-1));
This returns no records even though a direct query against the db returns a few dozen.
If I use the same query but replace the 'is null' with '33' (a valid userID), I'll get that user's orders. So the foundation of the query is valid - something's wrong with how I'm expressing the 'is null' bit.
Probably has something to do with the fact that, in the .NET project the Order object contains a complex User object where as in the database, the Orders table just holds integer (ID) that's a foreign key to the User table.
Still - since 'and User.ID = 33' works as expected I don't follow why 'is null' doesn't.
This answer suggests I need to switch of the Criteria route. This answer suggests that my current code should work.
thx
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用查询工厂方法而不是构建查询语句：
有效。
Rather than building a query statement, use a Query factory approach:
Works.