如何在 Fluent Nhibernate 中对两个名称不匹配的非键属性创建联接?

发布于 2024-11-17 23:37:02 字数 701 浏览 2 评论 0原文

我的旧数据库中有两个表

Purchasing

  • Id int(PK)
  • name varchar(50)
  • MasterAccount char(10)
  • BuyerAccount char(10)

MasterAccounts

  • Id int(PK)
  • Name varchar(50)
  • MasterAccountNumber char(10)
  • AccountNumber char(10)

我有一个将 1:1 映射到购买表的对象。我想将 MasterAccounts 表中的“名称”列作为属性添加到购买对象中。

当我想要连接的两列时,如何告诉 Fluent Nhibernate 执行连接:

  1. 未在数据库中定义为外键
  2. 每个表中的名称不相同

查看我想要的 SQL 可能会有所帮助产生。

Select Purchases.*, MA.Name
from Purchases
left join MasterAccounts MA
on 
MA.MasterAccountNumber = Purchases.MasterAccount
and
MA.AccountNumber = Purchases.BuyerAccount

I have two tables in my legacy database

Purchases

  • Id int(PK)
  • name varchar(50)
  • MasterAccount char(10)
  • BuyerAccount char(10)

MasterAccounts

  • Id int(PK)
  • Name varchar(50)
  • MasterAccountNumber char(10)
  • AccountNumber char(10)

I have an object that maps 1:1 to the purchases table. I want to add the "Name" column from the MasterAccounts table to the purchases object as a property.

How do I tell Fluent Nhibernate to perform a join when the two columns I want to join on:

  1. are not defined as foreign keys in the database
  2. Do not have the same name in each table

It might be helpful to see the SQL that I want to generate.

Select Purchases.*, MA.Name
from Purchases
left join MasterAccounts MA
on 
MA.MasterAccountNumber = Purchases.MasterAccount
and
MA.AccountNumber = Purchases.BuyerAccount

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

執念 2024-11-24 23:37:02

假设 Purchases 中的 MasterAccount 字段与 MasterAccounts 中的 MasterAccountNumber 匹配...在 sql 中创建一个视图,其中包含您尝试映射的表格式:

CREATE VIEW [dbo].[v_PurchaseMasterAccountName]
AS
SELECT     dbo.Purchases.Id, dbo.Purchases.Name, dbo.Purchases.MasterAccount, dbo.Purchases.BuyerAccount, dbo.MasterAccounts.Name AS MasterAccountName
FROM         dbo.MasterAccounts INNER JOIN
                      dbo.Purchases ON dbo.MasterAccounts.MasterAccountNumber = dbo.Purchases.MasterAccount

创建一个 ClassMap 来映射您的视图:

public class Purchase
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string MasterAccountName { get; set; }
    public virtual string MasterAccount { get; set; }
    public virtual string BuyerAccount { get; set; }
}

public class PurchaseClassMap : ClassMap<Purchase>
{
    public PurchaseClassMap()
    {
        Table("v_PurchaseMasterAccountName");
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.MasterAccount);
        Map(x => x.BuyerAccount);
        Map(x => x.MasterAccountName);
    }
}

确保您的 ClassMap 在您的 FluentMappings 中被选取。

更新:

这可能会起作用:

public class PurchaseMap : ClassMap<Purchase>
{
    public PurchaseMap()
    {
        Table("Purchases");
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.MasterAccount);
        Map(x => x.BuyerAccount);
        Map(x => x.MasterAccountName).ReadOnly()
            .Formula("(SELECT TOP 1 MasterAccounts.Name FROM MasterAccounts WHERE MasterAccounts.MasterAccountNumber = [MasterAccount] AND MasterAccounts.MasterAccountNumber = [BuyerAccount])");

    }
}

感谢 Darren Kopp 指向“公式”选项的指针。

Assuming that the MasterAccount field in Purchases matches the MasterAccountNumber in MasterAccounts...create a view in sql with the table format you are trying to map:

CREATE VIEW [dbo].[v_PurchaseMasterAccountName]
AS
SELECT     dbo.Purchases.Id, dbo.Purchases.Name, dbo.Purchases.MasterAccount, dbo.Purchases.BuyerAccount, dbo.MasterAccounts.Name AS MasterAccountName
FROM         dbo.MasterAccounts INNER JOIN
                      dbo.Purchases ON dbo.MasterAccounts.MasterAccountNumber = dbo.Purchases.MasterAccount

Create a ClassMap to map your view:

public class Purchase
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string MasterAccountName { get; set; }
    public virtual string MasterAccount { get; set; }
    public virtual string BuyerAccount { get; set; }
}

public class PurchaseClassMap : ClassMap<Purchase>
{
    public PurchaseClassMap()
    {
        Table("v_PurchaseMasterAccountName");
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.MasterAccount);
        Map(x => x.BuyerAccount);
        Map(x => x.MasterAccountName);
    }
}

Ensure your ClassMap is picked up in your FluentMappings.

UPDATE:

This may work instead:

public class PurchaseMap : ClassMap<Purchase>
{
    public PurchaseMap()
    {
        Table("Purchases");
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.MasterAccount);
        Map(x => x.BuyerAccount);
        Map(x => x.MasterAccountName).ReadOnly()
            .Formula("(SELECT TOP 1 MasterAccounts.Name FROM MasterAccounts WHERE MasterAccounts.MasterAccountNumber = [MasterAccount] AND MasterAccounts.MasterAccountNumber = [BuyerAccount])");

    }
}

Thanks to Darren Kopp for the pointer to the Formula option.

做个少女永远怀春 2024-11-24 23:37:02

这是一个不寻常的问题,很可能还有其他选择,但这是我的一些想法。

  1. 使用公式。该属性不可更新,但在您的情况下我认为这很好(将属性​​标记为只读)。
  2. 我认为您必须为此创建一个 hbm.xml 文件,但是您可以 在 nhibernate 中使用您自己的查询作为加载器查询
  3. 弄清楚 JoinPart 上的 Subselect 在 fluid nhibernate 中做了什么?怀疑这是否有效,但值得一试

This is kind of an unusual question and there may very well be other options, but here's my couple of ideas.

  1. Use a formula. The property wouldn't be updateable but in your situation i think that's fine (mark property as read only).
  2. I think you will have to do a hbm.xml file for this, but you can use your own query for the loader query in nhibernate.
  3. Figure out what Subselect on the JoinPart does in fluent nhibernate? Doubt this will work but worth a try
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文