CODEWITHSUNDEEP
sqlhibernatenhibernatenhibernate-mapping
Joseph Nields
Joseph Nields

Reputation: 5661

How to do you map an object to an aggregated view in NHibernate?

Basically, I have an existing database I'm trying to map to with NHibernate.

Here's a simplified example:

CREATE TEMPORARY TABLE exmplTable (
    id INT,
    changeNumber INT,
    name VARCHAR(255),
    address VARCHAR(255)   
)

which might contain the following records:

1  0 'John Doe' '123 Fake St'
1  1 'John Doe' '145 Another St' -- John moved
1  2 'John Doe' '42 Clark St'    -- John moved again

I only care about the most recent info for a single id. If I was to map these manually, I'd make a view:

SELECT id, name, address
FROM exmplTable E
INNER JOIN 
(
    SELECT id, MAX(changeNumber) cn
    FROM exmplTable
    GROUP BY id
) E2
ON E.id = E2.id AND E.changeNumber = E2.cn

and then get a record by id this way:

SELECT * FROM viewname WHERE id = @id

SO THEN:

Without making a view in the database, and without having an interface to the DAL to retrieve a record by manually performing the aggregate query, is it possible to just have NHibernate map to this sort of a relationship?

Note that although I am using NHibernate Hibernate xml works the same AFAIK.

Upvotes: 1

Views: 96

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123881

In case, that we need to map some complex SELECT statements (as the one mentioned above) and we

1) do not want create view for that
2) can accept that solution will be readonly (expectable, I know)

We can use NHibernate built-in (but not so deeply documented and presented) feature <subselect>. Check here more details:

NHibernate Criteria: Subquery After 'From' Clause

small snippet example 

<class name="MyEntity"... >
   <subselect>
    SELECT ...
    FROM ...
   </subselect>
   ...

Upvotes: 1

Related Questions