NHibernate, how to map a property to a subselect

Question

I currently have a legacy system that uses SPs exclusively for access to the DB. My domain object looks something like this:

public class User : EntityBase
{
    public virtual string Name {get;set;}
    public virtual string CreatedBy {get;set;}
    public virtual DateTime CreatedDate {get;set;}
}

The SP I have that mapped this looked like this:

CREATE PROCEDURE getUser
{
    @ID int
}
select
     Name
     ,(SELECT TOP 1 UserName FROM AuditTrail WHERE EntityID = [User].[ID] AND EntityName = 'User' AND AuditActionID = 1 ORDER BY DateStamp) AS CreatedBy
     ,(SELECT TOP 1 DateStamp FROM AuditTrail WHERE EntityID = [User].[ID] AND EntityName = 'User' AND AuditActionID = 1 ORDER BY DateStamp) AS CreatedDate
     FROM [User]
     WHERE [User].ID = @ID

So, as you can see, the audit information is separated from the entity itself on the database and the CreatedBy/CreatedOn (and ModifiedBy/ModifiedOn) are stored in a separate table called AuditTrail. the AuditActionID field on the table specifies if it was a create/update.

How can I setup this mapping with NHibernate? I looked into JOIN but it doesn't give me the option to restrict by the additional values (and a join isn't what I want).

Also, if this is possible in Fluent NHibernate, that's a bonus but I'm fine with trying just standard NHibernate mapping config if it gets me there.

UPDATE:

I have found one way to do this, but I'm not a fan. I have setup a SQLQuery that reads the data and maps it back to an object. It works, but I'd love to do this via mapping. Is it even possible since the "values" from the database I'm mapping to is a subselect and not editable?

Solution:

Thanks to the tip from Diego, this was the final solution I found (using Fluent NHibernate, in my ClassMap file):

Map(x => x.CreatedBy).Formula("(SELECT TOP 1 AuditTrail.UserName FROM AuditTrail WHERE AuditTrail.EntityID = [ID] AND AuditTrail.EntityName = 'User' AND AuditTrail.AuditActionID = 1 ORDER BY AuditTrail.DateStamp)");
Map(x => x.CreatedDate).Formula("(SELECT TOP 1 AuditTrail.DateStamp FROM AuditTrail WHERE AuditTrail.EntityID = [ID] AND AuditTrail.EntityName = 'User' AND AuditTrail.AuditActionID = 1 ORDER BY AuditTrail.DateStamp)");

Thanks, M

Answer 1

You can specify the select clause as the formula for your property.