Hi all -
I have the following classes mapped (heavily stripped down for briefness). Assume that everything is public and that every class has a property Oid for its primary key.
Code:
class Subtotal {
List<Verkaufsposition> Verkaufspositionen;
int Subtotalnummer;
}
class Verkaufsposition {
Subtotal Parent;
}
class ArtikelPosition : Verkaufsposition { // mapped as "joined-subclass"
Artikel Artikel;
}
class Artikel {
string Kurzbezeichnung;
}
My task now:
Formulate a query using the NHibernate Criteria API that
Finds all from <Subtotal> with <Subtotalnummer> equals "2"
that have a <Verkaufsposition> of subtype <ArtikelPosition>
that points to an <Artikel> with <Kurzbezeichnung> equals "d".
Right now I have no idea how to do that with "pure" Criterions. So I came up with the following solution that requires SQL:
Code:
string sql = @"
EXISTS(
SELECT 1 FROM
Verkaufsposition
INNER JOIN
Artikelposition ON Verkaufsposition.Oid =
ArtikelPosition.Oid
INNER JOIN
Artikel ON Artikelposition.Artikel =
Artikel.OID
WHERE
Artikel.Kurzbezeichnung = 'd'
AND
Verkaufsposition.ParentOid = this.Oid
)
";
ICriteria crit = sess.CreateCriteria(typeof(Subtotal));
crit.Add(Expression.Sql(sql));
crit.Add(Expression.Eq("Subtotalnummer", 2));
Unfortunately this use of SQL is heavyly frowned upon in our project. Is the an alternative way of doing that?
TIA
Ralf Kretzschmar
Login
FAQ
Search