c# - NHibernate - 从 sql 函数返回复杂对象

Question

我有一个应用程序使用 NHibernate 作为 ORM。我有一个持久类:

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; }
}

我在服务器端有一个 SQL 函数:

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
)

我希望能够调用这个函数并将它的结果映射到下面的类

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

NHibernate 3.0 可以做到吗？是否可以使用 FluentNHibernate 来实现？
提前致谢!

已更新
我将 Match 类映射到 tMatchesTest 表中。
tMatchesTest 表的结构是:

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]

已更新2
我自己找到的解决方案:
1. 像这样创建命名查询

<?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>

然后像这样执行查询:

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

这是迄今为止我发现的执行任务的最短、最简单的方法。

Answer 1

IResultTransformer用于将查询结果转换为应用程序可见的类型。

此外，将 SQL 函数调用映射为 named SQL query将提供更清晰的代码。

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

由于我们有一个多表结果，AliasToBeanResultTransformer 不能直接使用。相反，我们会将其子类化并将结果转换为所需的类型。

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
                }
            }
        }
    }
}