仅从连接表中选择列，无需连接
给定这些表：
create table Orders (
Id INT IDENTITY NOT NULL,
primary key (Id)
)
create table Items (
Id INT IDENTITY NOT NULL,
primary key (Id)
)
create table OrdersItems (
OrderId INT not null,
ItemId INT not null,
primary key (OrderId, ItemId)
)
是否可以使用 HQL/criteria API 构造一个查询，该查询会产生以下 SQL：
SELECT
[OrderId], [ItemId]
FROM
[OrdersItems]
我已经尝试了这两种方法：
var hqlResults = session
.CreateQuery("select order.id, item.id from Order order inner join order.Items item")
.List();
var criteriaResults = session
.CreateCriteria<Order>()
.CreateAlias("Items", "item", NHibernate.SqlCommand.JoinType.None)
.SetProjection(Projections.Property("id"), Projections.Property("item.id"))
.List();
但是这两种方法都坚持生成连接（或者失败，因为连接不是存在，在使用条件中），导致 SQL 例如：
select order.Id,
item.Id
from Orders order
inner join OrdersItems ordersItems
on order.Id = ordersItems.ArticleId
inner join Items item
on ordersItems.CategoryId = item.Id
有没有办法让 NHibernate 生成一个仅从连接表中选择列的查询，而不需要连接？
Given these tables:
create table Orders (
Id INT IDENTITY NOT NULL,
primary key (Id)
)
create table Items (
Id INT IDENTITY NOT NULL,
primary key (Id)
)
create table OrdersItems (
OrderId INT not null,
ItemId INT not null,
primary key (OrderId, ItemId)
)
Is it possible to use HQL/criteria API to contruct a query that results in the following SQL:
SELECT
[OrderId], [ItemId]
FROM
[OrdersItems]
I've tried both of these approaches:
var hqlResults = session
.CreateQuery("select order.id, item.id from Order order inner join order.Items item")
.List();
var criteriaResults = session
.CreateCriteria<Order>()
.CreateAlias("Items", "item", NHibernate.SqlCommand.JoinType.None)
.SetProjection(Projections.Property("id"), Projections.Property("item.id"))
.List();
But both approaches insist on generating a join (or fail because the join isn't present, in using criteria), resulting in SQL such as:
select order.Id,
item.Id
from Orders order
inner join OrdersItems ordersItems
on order.Id = ordersItems.ArticleId
inner join Items item
on ordersItems.CategoryId = item.Id
Is there any way to have NHibernate generate a query that selects columns only from the join table, without requiring a join?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我怀疑是否有办法使用 HQL 来做到这一点，因为 HQL 处理 NHibernate 实体，而 OrderItems 不是实体。在这种情况下，看起来您实际上并没有使用任何 ORM 功能，因此您可以简单地执行 SQL 查询 - 如果您愿意，可以通过 NHibernate。只需调用 ISession.CreateSQLQuery() 即可。
编辑：
我怀疑 NHibernate 坚持执行连接的原因是这样的：您已向它询问
的和Id属性OrderItem实体，因此必须确保Order和Item表中确实存在这些 ID 的行。OrderItems表中可能存在一行，其 ID 在Order或Item中不存在。当然，这样做是糟糕的数据库设计，而且不太可能，但是 NHibernate 不能确定情况并非如此，除非它查看表模式并看到适当的外键 - 但我怀疑它会做类似的事情那。但这只是我的猜测。您可以在NHibernate 论坛上询问开发人员更明确的答案。
I doubt that there's a way to do it using HQL, because HQL deals witn NHibernate entities and OrderItems is not an entity. In this case it looks like you're not actually using any ORM features, so you can simply do a SQL query - via NHibernate if you wish. Just call
ISession.CreateSQLQuery().Edit:
I suspect that the reason NHibernate insists on doing the join is this: you've asked it for the
Idproperties ofOrderandItementities, so it must ensure that there are actually rows in theOrderandItemtable for those IDs. It's possible that a row exists in theOrderItemstable with IDs that don't exist inOrderorItem. Sure, it would be bad database design to do that and it's unlikely, but NHibernate can't be sure that this is not the case unless it looks at the table schema and sees the appropriate foreign keys - but I doubt that it does things like that.This is just my speculation, though. You could ask on the NHibernate forum for a more definitive answer from the developers.