I have 2 classes.

Client and Job.

A Client can have many jobs.

I want to return all jobs that are approved (Job.Approved = true)

Each client has a collection of jobs.

Is this code correct, its not bringing back the correct details...

Client c = (Client)s.CreateCriteria(typeof(Client)).Add(Expression.Eq("id", id)).SetFirstResult(1).CreateCriteria("Jobs").Add(Expression.Eq("Approved", true)).UniqueResult();

return c;

I hope you can help me!

try using:

Client c = (Client)s.CreateCriteria(typeof(Client)).Add(Expression.Eq("id", id)).SetFirstResult(1).Add(Expression.Eq("Clients.Jobs.Approved", true)).UniqueResult();


If I'm useful please remember to rate me

Hi thanks for the attempt but this code doesn't work.

I'll try and summerise with a little more cohesion.

Trying to do the following doesn’t seem to be much documentation on it…

http://www.hibernate.org/hib_docs/refer ... teria.html

Its section 12.4

I have a Client Class and a Job Class. One Client can have many Jobs.

I want to return a Client based on their ID, and return all Jobs for that Client where Approved = true

So as well as returning the Client object I need to drill down and filter the Clients collection of jobs to return just Jobs where the approved variable is true.

In pseudo code...

GetClient(int id)
{
Client myClient = getClient(id).getJobs(where approved=true)
}

Any ideas? Many thanks in advance!

I think I've done it...

The only thing I was wondering was is it grabbing all the jobs for the client when I do s.Get and then doing replacing the Jobs collection with s.CreateFilter? Cos that would seem inefficient


public static Client GetClient(int id)

{

ISession s = NHibernateHelper.GetCurrentSession();

Client c = (Client)s.Get(typeof(Client), id);

c.Messages = s.CreateFilter(c.Messages, "order by created desc").List();

c.Jobs = s.CreateFilter(c.Jobs, "where Approved = true").List();

return c;

}

Can anyone help with this??? Is this efficient???!

Thanks!!!

All I'm asking is I want to be able to bring back an object for example (person), whose child collection for example (children) match certain criteria. I want to return the full (person) based on an ID, and then only bring back that (persons) children where the (child.active = true)

I beg for some help!

Try this, it should work:

Client c = (Client)s.CreateCriteria(typeof(Client)).Add(Expression.Eq("id", id)).Add(Expression.Eq("Jobs.Approved", true)).UniqueResult();


or:

Client c = (Client)s.CreateCriteria(typeof(Client)).Add(Expression.Eq("id", id)).CreateCriteria(typeof(Jobs)).Add(Expression.Eq("Approved", true)).UniqueResult();



remember to rate useful posts

Thanks for you help!

I tried both of these...

The first one:

Client c = (Client)s.CreateCriteria(typeof(Client)).Add(Expression.Eq("id", id)).Add(Expression.Eq("Jobs.Approved", true)).UniqueResult();

Comes up with an error saying Jobs.Approved is an unresolvable property

The second one:

Client c = (Client)s.CreateCriteria(typeof(Client)).Add(Expression.Eq("id", id)).CreateCriteria(typeof(Jobs)).Add(Expression.Eq("Approved", true)).UniqueResult();

Came up with an error regarding typeof(Jobs), I tried changing to typeof(Job) also errored even with correct class name, so changed to CreateCriteria("Jobs")

This worked but brought back the Client with all child jobs, not ones set to true, but all of them?

Any ideas?? I'm really stuck on this, 2 weeks now!

In trying to further clarify my situation...

Whenever you bring back a object for example (Person) if that person has children then it brings back the Person and all its children. This can be really inefficient if the person has a million children, especially if you only want to know the Persons name or want to see only children that are between a certain age or some other criteria...

So the aim is to return 1 person and only a portion of the children attached to this person by some sort of criteria without returning unnecesary data.

Hope someone can aid me !

Yes but if you set person id in the criteria, SQL will take only the one person with that id and only the children with selected criteria.
I'm not understanding your dubt

Basically both the statements above return all the jobs for the customer not just the ones with Approved set to true. So the criteria isn't working

It's strange. Which type is Approved of?

Hiya, thanks for helping me out here

Approved is a Boolean

I've attached a couple of the mapping files below...

The Job Mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="SoundArt.Job, App_Code" table="jobs">
<id name="Id" column="job_id" type="Int32">
<generator class="native" />
</id>
<property name="Name" column="name" type="String"/>
<property name="Info" column="info" type="String"/>
<property name="Created" column="created" type="DateTime" insert="false"/>
<property name="Active" column="active" type="Boolean"/>
<property name="Complete" column="complete" type="Boolean" />
<property name="Approved" column="approved" type="Boolean" />
<!--<property name="ClientId" column="client_id" type="String" />-->
<many-to-one name="ClientInstance" class="SoundArt.Client, App_Code" column="client_id"/>

</class>
</hibernate-mapping>

The Client Mapping File:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="SoundArt.Client, App_Code" table="clients">
<id name="Id" column="client_id" type="Int32">
<generator class="native" />
</id>
<property name="FirstName" column="first_name" type="String" />
<property name="LastName" column="last_name" type="String" />
<property name="Email" column="email" type="String" />
<property name="Phone" column="phone" type="String" />
<property name="Created" column="created" type="DateTime" insert="false" />
<property name="Address" column="address" type="String" />
<property name="Town" column="town" type="String" />
<property name="Postcode" column="postcode" type="String" />
<property name="Active" column="active" type="Boolean" />
<property name="Password" column="password" type="String" />
<property name="ClientName" column="client_name" type="String" />
<property name="LastLogin" column="last_login" type="DateTime" />
<property name="UserGroup" column="user_group" type="Int32" />
<bag name="Messages" cascade="all" lazy="true">
<key column="client_id" />
<one-to-many class="SoundArt.Message, App_Code"/>
</bag>
<bag name="Jobs" cascade="all" lazy="true">
<key column="client_id" />
<one-to-many class="SoundArt.Job, App_Code"/>
</bag>
</class>
</hibernate-mapping>

NHibernate won't load persistent collections partially - client.Jobs are all the client's jobs, no matter where the client came from - be it a query, load by id, or some code of yours. Your original query just means "give me all clients which have an approved job".

To filter the job list of a particular client, use IList filteredJobs = ISession.CreateFilter(client.Jobs, "where Approved = true").List();

(By the way, don't assign the resulting list back to client.Jobs as this will make NHibernate erase all the jobs for the client, except for the approved ones.)

it seems ok. Have you looked up to the SQL statement created?