我把这个映射成这样的SP:
所以,你可以看到,审计信息与数据库上的实体本身是分开的,CreatedBy / CreatedOn (和ModifiedBy / ModifiedOn)存储在一个名为AuditTrail的独立表中。表上的AuditActionID字段指定它是否是创建/更新。 如何使用NHibernate设置这个映射?我看着JOIN,但它没有给我的选项来限制附加值(和一个连接是不是我想要的)。
另外,如果这在Fluent NHibernate中是可能的,这是一个额外的优势,但是如果它只是标准的NHibernate映射配置,如果它到达那里,我还可以。
$ b $ p UPDATE:
我找到了一个办法,但我不是粉丝。我已经设置了一个SQLQuery读取数据并将其映射回一个对象。它的工作原理,但我想通过映射来做到这一点。这是甚至可能的,因为我所映射的数据库的价值是一个子选择,而不是可编辑的?解决方案:$ / b $ b
感谢迭戈的提示,这是我找到的最终解决方案(在我的ClassMap文件中使用Fluent NHibernate):
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)); 谢谢, M
解决方案您可以指定select子句作为公式为您的属性。
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
解决方案 You can specify the select clause as the formula for your property.
更多推荐
NHibernate,如何将属性映射到子查询
发布评论