NHibernate - returning complex object from sql function

Question

I've got an application witch uses NHibernate as an ORM. I have one persistent class:

public class Match : IEntity
{
    public virtual int ID { get; set; }
    public virtual string Word { get; set; }
    public virtual int WordIntervalBeginning { get; set; }
    public virtual int WordIntervalEnding { get; set; }
}

and I have an SQL function on the server side:

CREATE FUNCTION ftMatchTest
( )
RETURNS TABLE 
AS
RETURN 
(
    SELECT mt1.*, mt2.*,
    CASE WHEN mt1.Word = mt2.Word THEN 1 ELSE 0 END AS sc
    FROM
        dbo.tMatchesTest mt1, dbo.tMatchesTest mt2
)

I want to be able to call this function and map the result from it into the following class

public class FResult
{
    public Match Match1 { get; set; }
    public Match Match2 { get; set; }
    public int sc { get; set; }
}

Is it possible to do it with NHibernate 3.0? Is it possible to do it with FluentNHibernate?
Thanks in advance!

UPDATED
I map Match class into tMatchesTest table.
Structure of tMatchesTest table is:

CREATE TABLE [dbo].[tMatchesTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Word] [varchar](50) NOT NULL,
    [WordIntervalBeginning] [int] NOT NULL,
    [WordIntervalEnding] [int] NOT NULL,
 CONSTRAINT [PK_tMatchesTest] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

UPDATED2
The solution I found on my own:
1. Create named query like this

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
                   namespace=" ConsoleApplication8.Domain.Entities"
                   assembly="ConsoleApplication8">

  <resultset name="fresult-resset">
    <return alias="Match1" class="Match"/>
    <return alias="Match2" class="Match"/>
    <return-scalar column="sc" type="int"/>
  </resultset>


  <sql-query name="getfresult" resultset-ref="fresult-resset">
    SELECT {Match1.*}, {Match2.*},
    CASE WHEN Match1.Word = Match2.Word THEN 1 ELSE 0 END sc
    FROM dbo.tMatchesTest Match1, dbo.tMatchesTest Match2
  </sql-query>

</hibernate-mapping>

and execute the query like this:

Session.GetNamedQuery("getfresult")
                .SetResultTransformer(new AliasToBeanResultTransformer(typeof(FResult)))
                .List<FResult>();

This is the shortest and simples way I found so far to perform the task.

Answer 1

IResultTransformer is used to transform query results into a application-visible types.

Also, mapping the SQL function call as a named SQL query will give cleaner code.

var list = Session.GetNamedQuery("ftMatchTest")
    .SetResultTransformer(new AliasToFResultTransformer())
    .List<FResult>();

Since we have a multi-table result, AliasToBeanResultTransformer is not directly usable. Instead we will subclass it and convert the result to the desired type.

public class AliasToFResultTransformer : AliasToBeanResultTransformer
{
    public AliasToFResultTransformer() : base(typeof(FMatches)) {}

    object IResultTransformer.TransformTuple(object[] tuple, string[] aliases)
    {
        FMatches fm = base.TransformTuple( tuple, aliases ) as FMatches;

        return fm.ToFResult();
    }

    public class FMatches
    {
        public int sc { get; set; }
        public virtual int Mt1ID { get; set; }
        public virtual string Mt1Word { get; set; }
        public virtual int Mt1WordIntervalBeginning { get; set; }
        public virtual int Mt1WordIntervalEnding { get; set; }
        public virtual int Mt2ID { get; set; }
        public virtual string Mt2Word { get; set; }
        public virtual int Mt2WordIntervalBeginning { get; set; }
        public virtual int Mt2WordIntervalEnding { get; set; }

        public FResult ToFResult()
        {
            return new FResult {
                sc = this.sc,
                Match1 = new Match {
                    Id = this.Mt1Id,
                    Word = this.Mt1Word,
                    WordIntervalBeginning = this.Mt1WordIntervalBeginning,
                    WordIntervalEnding = this.Mt1WordIntervalEnding
                },
                Match2 = new Match {
                    Id = this.Mt2Id,
                    Word = this.Mt2Word,
                    WordIntervalBeginning = this.Mt2WordIntervalBeginning,
                    WordIntervalEnding = this.Mt2WordIntervalEnding
                }
            }
        }
    }
}

Answer 2

Well the only way i can think of that might work is this:

map your function to a view, lets name it FResult which will have only 3 columns:

[mt1Id, mt2Id, sc]

now you can map an entity on that view and then map it to an entity FResult with a composite-id [many-to-one{mt1Id}, many-to-one{mt2Id}] and have all the bells and whistles of HQL and criteria or if you're not interested in all that and your just want a function, this should work but keep in mind that you will still just output the Ids of the entities and the result values from the function:

var result = Session.CreateSqlQuery(
    @"select {m1.*}, {m2.*}, ft.sc 
      from dbo.ftMatchTest ft, Match m1, Match m2 
      where m1.Id = ft.mt1Id and m2.Id = ft.mt2Id")
      .AddEntity("m1",typeof(Match))
      .AddEntity("m2",typeof(Match))
      .AddScalar("sc", NHibernateUtil.BlaBla)
      .List();

Note that i am 'froming' the function directly but i wrote the query while thinking of a view so you will probably have to modify the sql for a function

Now, the result is an arraylist of object[] where object[0] and object[1] are hydrated Match objects and object[2] is the sc value

At this point, if its absolutely necessary, you can just construct an FResult object and apply the results since the FResult class is not a fully mapped class NHibernate is limited in that it can do with it (to make it output an IList<FResult> directly).

Answer 3

I would use dynamic instantiation, like so:

CREATE FUNCTION ftMatchTest
( )
RETURNS TABLE 
AS
RETURN 
(
    -- select each column
    SELECT mt1.ID ID1, mt2.ID ID2, <etc>
    CASE WHEN mt1.Word = mt2.Word THEN 1 ELSE 0 END AS sc
    FROM
        dbo.tMatchesTest mt1, dbo.tMatchesTest mt2
)

Then write the ctor on your reporting class:

public class FResult
{
    public FResult(int id1, int id2, ..., int sc)
    {
        Match1 = new Match(id1, ...);
        Match2 = new Match(id2, ...);
        this.sc = sc;
    }

    public Match Match1 { get; set; }
    public Match Match2 { get; set; }
    public int sc { get; set; }
}

And to run the query...

var result = Session.CreateSqlQuery(
   @"select new FResult(ft.ID1, ft.ID2, ...,  ft.sc) 
   from dbo.ftMatchTest ft")