问题描述:

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.

网友答案:

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
                }
            }
        }
    }
}
网友答案:

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")
网友答案:

I am not sure whether this will work our for you but you can always the addentity method to set tthe return type. the query will look like follows:

Session.CreateSqlQuery(selectStatement).AddEntity(typeof(FResult)).SetString(variableNameIfAny,value).List<FResult>();

Hope you can get the select query yourself.. just see how nhibernate builds the query by firing such a query and checking the sql using NHibernate PRofiler.

Hope this helps

网友答案:

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).

网友答案:

A possible solution is to create a view instead of a function (or one that calls the function, if a function is required):

CREATE VIEW [MatchTestResults]
AS
SELECT mt1.*, 
       mt2.*,
       CASE WHEN mt1.Word = mt2.Word THEN 1 ELSE 0 END AS sc
FROM
    dbo.tMatchesTest mt1, 
    dbo.tMatchesTest mt2

With a view, you can map it in NHibernate exactly like you would map a table. SQL Server will complain if NHibernate tries to commit updates to the objects, obviously, unless you want to create INSTEAD OF triggers.

相关阅读:
Top