NHibernate，如何将属性映射到子查询 [英] NHibernate, how to map a property to a subselect

问题描述

我现在有一个遗留系统，专门用来访问数据库。我的域对象看起来像这样：

$ p $ public class User：EntityBase
{
public virtual string Name {get; set;}
公共虚拟字符串CreatedBy {get; set;}
public virtual DateTime CreatedDate {get; set;}
}


我把这个映射成这样的SP：

  {
 @ID int 
} 
 select 
名称
，（SELECT TOP 1用户名FROM AuditTrail WHERE EntityID = [用户]。[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 
  

所以，你可以看到，审计信息与数据库上的实体本身是分开的，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，如何将属性映射到子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！