Here is how I do things like this:
Table A:
ID TableAName
----------- -------------
1 A-ONE
2 A-TWO
3 A-THREE
4 A-FOUR
Table B:
ID TableAID TableBName
--- ----------- ----------------
1 1 B-ONE (A1)
2 1 B-TWO (A1)
3 1 B-THREE (A1)
4 2 B-FOUR (A2)
5 2 B-FIVE (A2)
Create a detatched query that returns the ID of the parent (Table A),
but filtering on the child table (table B) and return the A-IDs that
you like.
DetachedCriteria TableAsThatILike = DetachedCriteria
.For<TableB>()
.Add(Restrictions.Eq("prop", "something i
like"))
.SetProjection(Projections.Property("TableAID"));
Then use the detached query to filter TableA
var tableAstuff = MySession.
.CreateCriteria<TableA>()
.Add(Subqueries.PropertyIn("ID", TableAsThatILike))
.List<TableA>();
you can add as many subqueries as you need. This pattern is very
handy.
On Oct 21, 10:33 am, xanatos <[email protected]> wrote:
> I'm using NHibernate 2.1.2. I'm using the Criteria API. I have to
> write this query. I'm using DetachedCriteria. I'm not able to write
> this query, because while I'm able to write the NOT EXISTS (SELECT 1
> FROM B WHERE A.ID = B.ID) (it's something like
> Restrictions.IsEmpty("B"), I can't write the other part of the
> subquery AND connect it with an OR.
>
> SELECT A.ID FROM A WHERE
> A.ID > 100 AND (
> NOT EXISTS (SELECT 1 FROM B WHERE A.ID = B.ID)
> OR EXISTS (SELECT 1 FROM B WHERE A.ID = B.ID AND B.PROP =
> 'Something')
> )
>
> My problem is that in the Subquery I can't reference the A.ID (or at
> least I wasn't able to do it)
>
> ID is an INT. Propr is a VARCHAR
> There is a relationship 1...many on A and B. ID is PK of A and FK of
> B. In the A class there is a one-to-many relationship to B, with "B"
> being the name of the collection. A and B entities are full classes.
>
> I think the hbm should be similar to this.
>
> <class name="A">
> <id name="Id" type="Int32" />
> <bag name="B">
> <key column="Id" />
> <one-to-many class="B" />
> </bag>
> </class>
>
> <class name="B">
> <id name="Id2" type="Int32" />
> <property name="Id" type="Int32" />
> <property name="Prop" type="String" />
> <many-to-one name="A">
> <column name="Id" />
> </many-to-one>
> </class>
>
> Thanks a lot!
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
