Hello,
I have an NHibernate application that currently makes use of a SQL
user-defined function. I would like to avoid having to call this
function, and instead express its logic using the NH criteria API.
Unfortunately, I'm having difficulty applying the criteria examples in
the NH documentation to my particular case. So I'm turning to this
mailing list for help.
Here is the function. It takes a single string argument and returns a
table. The function performs 3 joins between the same 2 tables but
with different join criteria, and then coalesces the result.
CREATE FUNCTION dbo.GetRevisionText
(
@LangId NVARCHAR(16)
)
RETURNS TABLE
AS
RETURN
(
SELECT r.RevisionId,
COALESCE(lp1.Title, lp2.Title, lp3.Title) Title,
COALESCE(lp1.Description, lp2.Description,
lp3.Description)
Description
FROM Revision r
LEFT JOIN LocalizedProperty lp1
ON lp1.RevisionId = r.RevisionId
AND lp1.LanguageId = @LangId
LEFT JOIN LocalizedProperty lp2
ON lp2.RevisionId = r.RevisionId
AND lp2.LanguageId = LEFT(@LangId, 2)
LEFT JOIN LocalizedProperty lp3
ON lp3.RevisionId = r.RevisionId
AND lp3.LanguageId =
r.DefaultPropertiesLanguage
);
Here is the mapping for the 3 classes involved :
<class name="Revision">
<id name="RevisionId" type="Guid">
<generator class="assigned"/>
</id>
<set name="LocalizedProperties" inverse="true" lazy="true"
cascade="all-delete-orphan">
<key column="RevisionId"/>
<one-to-many class="LocalizedProperty"/>
</set>
</class>
<class name="Language">
<id name="LanguageId" type="String" length="16">
<generator class="assigned"/>
</id>
<property name="Lcid" type="Int32" unique="true"
not-null="true"/>
</class>
<class name="LocalizedProperty" mutable="false">
<composite-id>
<key-many-to-one name="Revision" class="Revision"
column="RevisionId"/>
<key-many-to-one name="Language"
class="Fusion.Core.Languages.Language, Fusion.Core"
column="LanguageId"/>
</composite-id>
<property name="Title" type="String" length="200"
not-null="true"/>
<property name="Description" type="String" length="1500" not-
null="false"/>
</class>
Thanks in advance,
Paul Lalonde
--
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=.
