The answers seemed to apply to collections of entities, not values.
For the simple element case, ...
Code:
namespace Eg
{
public class QuestionEntity
{
public virtual Guid ID { get; set; }
public virtual ISet<string> StringSet { get; set; }
}
}
and mapping
Code:
<class name="QuestionEntity">
<id name="ID" type="Guid">
<generator class="guid" />
</id>
<set name="StringSet">
<key column="ExampleEntityID" />
<element column="StringValue" type="String" />
</set>
</class>
the hql
Code:
SELECT q FROM Eg.QuestionEntity AS q WHERE EXISTS elements(q.StringSet)
will generate the SQL
Code:
select questionen0_.ID as ID30_ from QuestionEntity questionen0_ where (EXISTS(select stringset1_.StringValue from StringSet stringset1_ where questionen0_.ID=stringset1_.ExampleEntityID))
but how do you generate SQL as follows
Code:
select questionen0_.ID as ID30_ from QuestionEntity questionen0_ where (EXISTS(select stringset1_.StringValue from StringSet stringset1_ where questionen0_.ID=stringset1_.ExampleEntityID AND stringset1_.StringValue IN (?)
where ? is some parameter? (Or an equivalent join?)
I've looked everywhere and tried a bunch of things and spent an hour tracing the source code without figuring this out. I started with ICriteria and realized it wasn't possible. So I switched to hql, but still can't come up with anything good. Something like...
Code:
SELECT q FROM Eg.QuestionEntity AS q WHERE EXISTS elements(q.StringSet WHERE element IN (:stringSetParm))
... would be nice, but that's not it.
For now, I have a 'workaround' which is to replace the subquery with something like
Code:
... :parm0a in elements(q.StringSet) or :parm0b in elements(q.StringSet) or ...
It "works", but it's definitely not optimal SQL.
Any suggestions appreciated. I'm happy to adjust the mapping for the ISet, but I don't want to have to create entities to use instead of the <string>. (I know that this whole problem is not an issue if the set or other collection is a collection of entities rather than values. The issue seems to revolve around the fact that there's no way to reference the
value of individual elements in order to apply select conditions. )
Thanks! :-)