title: NHibernate QueryOver the values of a dictionary(map)
body: <p>For a project i'm working on, i need to query over the values of a mapped dictionary in Nhibernate. Something like </p>
  
  <pre><code>SELECT * FROM EntityWithDictionary WHERE EntityWithDictionary 
      in (select EntityFromDictionaryId FROM Dictionary where value = 'testvalue')
  </code></pre>
  
  <p>I've tried several things but I can't figure out how i can do it. Here is what i have. </p>
  
  <h2>Entity</h2>
  
  <pre><code>public class EntityWithDictionary
  {
      public virtual Guid Id { get; set; }
      public virtual string Description { get; set; }
      private IDictionary&lt;string, string&gt; _dictionary = new Dictionary&lt;string, string&gt;();
      public virtual IDictionary&lt;string, string&gt; Dictionary
      {
          get { return _dictionary; }
      }
  }
  </code></pre>
  
  <p>It's in the EntityWithDictionary.Dictionary i want to investigate the values.</p>
  
  <h2>Mappings:</h2>
  
  <pre><code>public EntityWithDictionaryMap()
  {
      Id(s =&gt; s.Id);
      Map(s =&gt; s.Description);
      HasMany(m =&gt; m.Dictionary)
          .Table("`Dictionary`")
          .KeyColumn("EntityWithDictionaryId")
          .AsMap&lt;string&gt;("`Key`")
          .Element("`Value`")
          .LazyLoad()
          .Access.CamelCaseField(Prefix.Underscore)
          .Cascade.All();
  }
  </code></pre>
  
  <p>This is what i've tried so far:</p>
  
  <h2>Queryover query :</h2>
  
  <pre><code>EntityWithDictionary entityWithDictionary = null;
  var result = session.QueryOver&lt;EntityWithDictionary&gt;(() =&gt;entityWithDictionary)
          .JoinQueryOver(dictionary =&gt;dictionary.Dictionary)
          .UnderlyingCriteria.Add(Restrictions.Eq("elements", "test")).List();
  </code></pre>
  
  <p>This results in the following sql-statement =></p>
  
  <pre><code>SELECT this_.Id as Id0_0_, this_.Description as Descript2_0_0_ FROM
  [EntityWithDictionary] this_ inner join [Dictionary] dictionary3_ on
  this_.Id=dictionary3_.EntityWithDictionaryId WHERE dictionary1_.[Value] = @p0 ]
  </code></pre>
  
  <p>In this query NHibernate chooses the use 2 aliasses (dictionary1_, dictionary3_) where only 1 is created (dictionary3_). </p>
  
  <h2>ICriteria</h2>
  
  <pre><code>ICriteria criteria = session.CreateCriteria&lt;EntityWithDictionary&gt;();
  criteria("Dictionary").Add(Restrictions.Eq("elements", "testValue"));
  var result = criteria.List();
  </code></pre>
  
  <p>This generates the same sql query as with queryover, with the same problem as result.</p>
  
  <p>I know that this is possible in hql with something like, but how can i do this with QueryOver of ICriteria?</p>
  
  <pre><code>from EntityWithDictionaryId e where 'aDictionaryValue' in elements(m.Dictionary).
  </code></pre>