How to query over a key-value collection using NHibernate?

Question

I have the following scenario, a Customer who can have bank accounts in several Banks. This relation is described as an IDictionary in the Customer class (mapped using the map tag):

public class Customer
{
    public virtual IDictionary<Bank, string> Banks
    {
        get { return _Banks; }
        set { _Banks = value; }
    }
    private IDictionary<Bank, string> _Banks = new Dictionary<Bank, string>();
}

Each key in the IDictionary represents a Bank where the Customer has an account, and the string value is a simple status not relevant for this question.

What I would like to do is a NHibernate query to retrieve all Customers that have an account in a specified Bank. I tried this inside the Bank class (hence the this keyword is referring to a Bank):

IList<Customer> customers = session.QueryOver<Customer>()
                                   .Where(x => x.Banks.Keys.Contains(this))
                                   .List();

And even thought the above query compiles without errors when I try to run it I get the following exception:

System.Exception: Unrecognised method call: System.Collections.Generic.ICollection`1[[Finance.Bank, Finance, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]:Boolean Contains(Finance.Bank)

So how can I correctly perform this kind of query? Thanks!

PS: This SO question shows how to do it for an ISet collection, and this SO question states that what I'm attempting may not be possible using the ICriteria API.

score 1 · Accepted Answer · answered Jun 16 '12 at 15:09

After some more research I realized that the logic contained in the Contains method probably can't be easily translated into SQL in a generic way, and that's why I was getting that exception.

For anyone interested, I came up with the following workaround:

IList<Guid> customerIds = session.CreateSQLQuery(
            "SELECT Customer_ID FROM Banks WHERE Bank_ID = '" + this.ID + "'")
            .List<Guid>();

Guid[] array = new Guid[customerIds.Count];
customerIds.CopyTo(array, 0);

IList<Customer> customers = session.QueryOver<Customer>()
                .Where(Restrictions.In("ID", array)).List();

First it fetches all Customer_IDs from the association table for a given Bank_ID. Then the resulting list of Customer_IDs is used in a second query to retrieve the Customers objects.

PS: This solution worked on SQL Server, but when I switched to MySql I had to slightly change it because they represent the Guid structure in different ways.

How to query over a key-value collection using NHibernate?

1 Answers1