NHibernate - Populate a single property from a stored procedure.

Question

Currently have the following mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
    namespace="NHibernateHelpers"
    assembly="App_Code.NHibernateHelpers">
  <class name="NHibernateHelpers.Fixture, App_Code" table="Fixture_Lists">
    <id name="Id" column="UniqRefNo">
      <generator class="guid" />
    </id>
    <property name="Date" column="FixDate"/>
    <property name="HomeTeamId" column="HomeId"/>
    <property name="HomeTeamName" column="Home_Team"/>
    <property name="AwayTeamId" column="AwayId"/>
    <property name="AwayTeamName" column="Away_Team"/>
    <property name="Kickoff" column="Kickoff"/>
    <bag name="Goals">
      <key column="FixID" />
      <one-to-many class="NHibernateHelpers.Goal, App_Code"/>
    </bag>
    <bag name="Bookings">
      <key column="FixID" />
      <one-to-many class="NHibernateHelpers.Booking, App_Code"/>
    </bag>
    <many-to-one name="HomeTeam" class="NHibernateHelpers.Team" column="HomeId" />
    <many-to-one name="AwayTeam" class="NHibernateHelpers.Team" column="AwayId" />
    <many-to-one name="Division" class="NHibernateHelpers.Division" column="Div_Comp" />
    <property name="HomeFullTimeScoreCode" column="Home_FT_Score"/>
    <property name="AwayFullTimeScoreCode" column="Away_FT_Score"/>
  </class>
</hibernate-mapping>

Which maps nicely to the legacy database I have inherited, but I would like to add a property named "MatchTime" that contains the output of the stored procedure:

EXEC GetMatchTime @FixtureId = :Id

where :Id is the Id of the current Fixture object.

Is this possible in the mapping file?

Answer 1

You may have to tweak the parameter a little, but it should work (Id matches up with the name of Fixture.Id):

<property name='MatchTime' formula='(EXEC GetMatchTime Id)'/>

http://ayende.com/Blog/archive/2006/12/26/LocalizingNHibernateContextualParameters.aspx

Answer 2

@Watson

The contents of the formula are added as a sub-query by NHibernate so the resulting SQL ends up looks something like:

SELECT FieldA, FieldB, FieldC, ( EXEC GetMatchTime Id ) FROM Fixture_Lists

Which unfortunately fails due to stored procedures not being allowed as a sub-query.

I could convert the stored procedure to a function but there are many of them, and it could potentially break legacy code.

Answer 3

Little kludgy -- but what about not converting the sp to functions, but creating new functions and using them as wrappers around the existing sp? You can add the Id to the function, and have it pass it to the stored procedure, grab the results of executing the sp, and pass them back.

http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx