Duplicate Association using QueryOver

[Adrian]

I am writing a query for a sortable grid using this QueryOver:

IQueryOver<Deal, Deal> query = session.QueryOver(() => _OrderAlias);

query.Left.JoinQueryOver(() => _OrderAlias.Employees, () =>
_OrderEmployeeAssigneeAlias,
() =>
_OrderEmployeeAssigneeAlias.OrderEmployeeType ==
OrderEmployeeType.Assignee);
query.Left.JoinQueryOver(() => _OrderAlias.Employees, () =>
_OrderEmployeeSalespersonAlias,
() =>
_OrderEmployeeSalespersonAlias.OrderEmployeeType ==
OrderEmployeeType.SalesPerson);
query.OrderBy(() => _OrderEmployeeSalespersonAlias.LastName).Desc;

Conditionally, the query will be built to sort by Assignee, not by
Salesperson, so this is the reason for the multiple joins to the same
table.

Running this query produces this exception:
NHibernate.QueryException: duplicate association path: Employees --->
System.ArgumentException: An item with the same key has already been
added.
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue
value, Boolean add)
at NHibernate.Util.LinkedHashMap`2.Add(TKey key, TValue value)
at
NHibernate.Loader.Criteria.CriteriaQueryTranslator.CreateAssociationPathCriteriaMap()

I have seen posts about doing this with HQL, and a few that say this
is not currently supported in the Criteria API.

Is this correct? Is HQL the only way to join to a collection twice?
Can I do this using QueryOver? (Also, is there a better way to do
this than using two joins?)

Thanks in advance.

[Itzik Saban]

Hi Adrian,

So if i get you right, each order has a set of different employees
related to it. e.g. one employee is the SalesPerson of the order and
another is the Assignee of the order.
Now, you want to get a list of Orders sorted by their SalesPerson's
last name, correct?

if so, why are you also joining the Assignees?

[Adrian]

That's correct. I am joining to both because I need to display both
Assignee and Salesperson. I'd like to do it in one query because this
is for a large data grid, so extra queries will come at a cost.

[Itzik Saban]

Hi Adrian,

you should try the Future method, like this:

var Orders = session.QueryOver(() => _OrderAlias)

.Left.JoinQueryOver(() => _OrderAlias.Employees, () =>
_OrderEmployeeAssigneeAlias,
() =>
_OrderEmployeeAssigneeAlias.OrderEmployeeType ==

OrderEmployeeType.Assignee).Future<Deal>();

session.QueryOver(() => _OrderAlias)

.Left.JoinQueryOver(() => _OrderAlias.Employees, () =>
_OrderEmployeeSalespersonAlias,
() =>
_OrderEmployeeSalespersonAlias.OrderEmployeeType ==
OrderEmployeeType.SalesPerson)

.OrderBy(() =>
_OrderEmployeeSalespersonAlias.LastName).Desc.Future<Deal>();

return Orders;

Now, only when you will access the Orders enumerable, e.g 'foreach'
statement , the two different queries will be executed, but the would
in one batch.

you should read a bit more about 'Future' method