I've a simple question on how to get to something, but I do not see how I can do it, so therefore, I open this topic. :)
What I have, is 2 classes which map to 2 tables in my DB. These 2 tables have a one to many relationship:
Table : Prestation (PrestationId, Code, ...)
Table : PrestationName (PrestationNameId, PrestationId, Language, Description)
As you can see, a Prestation can have zero to multiple names. Each name is in a certain language.
My objects are very similar:
I have a Prestation class which has a set of PrestationNames.
Now, I want to retrieve an overview of all the Prestations I have, and I want to display them with only one name, which should be in a language that I define.
I do this using Projections & ResultTransformers:
Code:
ICriteria crit = session.CreateCriteria(typeof(Prestation));
crit.CreateAlias ("Names", "n", JoinType.LeftOuterJoin).
Add(Restrictions.Eq("n.Language", somelanguage));
crit.SetProjection ( .... );
crit.SetResultTransformer(Transformers.AliasToBean ( ... ));
return crit.List<PrestationView>();
Now, this all works fine in most of the situations. The above ICriteria results in the follwing query:
Code:
select ....
FROM Prestation
LEFT OUTER JOIN PrestationName ON Prestation.Id = PrestationName.PrestationId
WHERE PrestationName.Language = 1
However, this gives a little problem when I have a Prestation which does not have a Name for languagecode=1: this prestation is not present in my result-set, but I do want to have it present, be it with an empty name.
So, in fact, the SQL query that I want NHIbernate to generate for me, would be :
Code:
select ...
FROM Prestation
LEFT OUTER JOIN PrestationName n ON Prestation.Id = n.PrestationId AND n.Language = 1
(Note that I filter in the JOIN clause, instead of in the WHERE clause).
Is there any way or possibility to do this using an ICriteria query ? And if so, how can i do this ?