Select columns from join table only without requiring a join
- by Kent Boogaart
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?
