Query for item in a value-type collection

[Kyle Baley]

Here's a simplified HBM file:
<class name="Job" table="Job">
<bag name="PlansUsed" lazy="false" cascade="all-delete-orphan">
<key column="JobID" />
<element type="String" column="PlanNumber" />
</bag>
</class>

My Job object contains a List<string> collection called PlansUsed to
map to this. All works well for adding and removing items to the list.

My question: How do I retrieve all jobs that have a particular value
in their PlansUsed collection? Preferably using the API rather than
HQL.

For example, in code it would be:
jobList.FindAll( j => j.Plans.Contains( planToFind ) );

In SQL:
SELECT * FROM Job
WHERE jobID IN ( SELECT JobID FROM PlansUsed WHERE PlanNumber =
@planToFind )

Just don't know how to accomplish it in NHibernate.

Have tried using a subquery as shown below. This works when querying a
collection of non-value types:

var subquery = DetachedCriteria.For<Job>( );
var criteria = subquery
.SetProjection( Projections.Property( "JobId" ) )
.CreateCriteria( "PlansUsed" );

criteria.Add( Query.ForProperty( "PlanNumber" ).Like( plansUsed,
MatchMode.Anywhere ) );
query.Add( Subqueries.PropertyIn( "Id", subquery ) );

In this case JobId and PlanNumber are the database column names for
PlansUsed. This gave an error MappingException: collection was not an
association. Probably because PlansUsed is a collection of value-
types.


Also tried the following:

query.Add( Expression.Sql("? in ( select 'PlansUsed' )",
planUsed,NHibernateUtil.String));

This doesn't fail but also doesn't do anything.

Thanks
Kyle

[Kyle Baley]

As usual, after hours of searching and experimenting, the solution did
not present itself until I asked the question publicly.

Here is *a* solution, though it feels like there should be a cleaner
one:

query.Add( Expression.Sql( "jobId in ( SELECT JobId FROM PlansUsed
WHERE PlanNumber = ? )", plansUsed, NHibernateUtil.String ) );

[James Kovacs]

You can do this using HQL:

var query = session.CreateQuery("from Job j where :plan in elements(j.PlansUsed)")

    .SetParameter("plan", "BBB");

var jobsViaHql = query.List<Job>();

foreach(var job in jobsViaHql) {

    Console.WriteLine(job.Id);

}

I couldn't figure out a way to do it with the Criteria API other than using direct SQL subquery as you suggest. Like in the HQL, you want to check whether some plan ("BBB") is in the PlansUsed bag. There are Criteria for checking whether a property (single-valued) is in a collection of values that you supply, but not the other way around. Maybe someone else knows a way around this.

James
--
James Kovacs, B.Sc., M.Sc., MCSD, MCT
Microsoft MVP - C# Architecture
http://www.jameskovacs.com
jko...@post.harvard.edu
403-397-3177 (mobile)

[Gustavo Ringel]

Hi did you try

 

string[] plansForSerach = new string[] { PlanToLookFor }

var criteria = DetachedCriteria.For<Job>()

     .Restrictions.In("PlansUsed", plans)

 

I tried this when i have one to many, never with <element...> but may be it works.

 

Gustavo.

[James Kovacs]

I tried something like that, but you get this error message:

Cannot use collections with InExpression

James
--
James Kovacs, B.Sc., M.Sc., MCSD, MCT
Microsoft MVP - C# Architecture
http://www.jameskovacs.com
jko...@post.harvard.edu
403-397-3177 (mobile)

[Gustavo Ringel]

I didn't try it with value types...What a pity if it doesn't works, it might be a reason...