0

I want to select the primary keys of a parent's child collection in a many-to-many relationship without using a join. Due to other constraints that have been removed from this example, the querying must occur on the child level (Tag) instead of parent level (Item).

Item item = null;
Tag tag = null;

var qoTags = QueryOver.Of<Tag>(() => tag)
             .JoinQueryOver(x => x.Items, () => item)
             .Select(Projections.Group<Item>(() => item.ItemId));

generates

SELECT Item.ItemId
FROM Tag 
inner join ItemsTags on Tag.TagId  =  ItemsTags.TagId 
inner join Item on ItemsTags.ItemId  =  Item.ItemId 
GROUP BY Item.ItemId

but ideally, the generated SQL would be:

SELECT ItemsTags.ItemId
FROM Tag 
inner join ItemsTags on Tag.TagId  =  ItemsTags.TagId  
GROUP BY ItemsTags.ItemId

Notice the unnecessary join is removed and the 'group by' and 'select' clauses are referencing the junction table's ID's.

Thanks!

Mehraban
  • 3,164
  • 4
  • 37
  • 60
Sardonic
  • 126
  • 1
  • 4
  • Have you compared the actual query plans for both query? It might be possible that these two queries are executed the same way. – biziclop Jun 26 '12 at 06:31
  • Yes, the execution plan shows a small cost for joining to the Item table. Normally, I would accept this cost, but the Item table has 2 subclasses (tables). Therefore, joining with Item results in 3 extraneous joins, amounting to about ~60% of the query cost. – Sardonic Jun 26 '12 at 16:49

1 Answers1

0

My guess would be that when you tell NHibernate you want to join Tags to Items with JoinQueryOver it will always put in all the steps to get from the Tag to the Item objects.

It's not smart enough to take a step back, think "Hang on, there's a group by projection in the query for item.ItemId and no other item properties are mentioned so I can skip one of the joins"

I'm not sure even switching to criteria or hql queries would help here as NHibernate will always try to create all the joins necessary between your 2 mapped objects.

You could:

  • Do a direct sql query (not great if you're using all that queryover stuff for a bunch of other criteria)
  • change your model to have a mapped class between Tag and Item. Tag->one-to-many->TagItem->many-to-one->Item (YUK! I Don't like the smell of that one!)

I'd love someone to correct me below...

brendanrichards
  • 309
  • 2
  • 6