如何使用NHibernate查询Oracle数据库?

发布于 2024-12-03 12:25:37 字数 946 浏览 1 评论 0原文

帮我将其转换为正确的 NHibernate...

我有一个包含 2 个表的 Oracle 数据库:

Employees:
    Id (unique id)
    FirstName (string)
    LastName (string)
    Location (string)

Locations:
    Name (string)
    Address (string)

如您所见,Employees 表有一个唯一的 id,但 Locations 表没有任何 id。名称字段是常规字段,并不唯一。

在 Oracle SQL 中,我可以运行以下查询:

SELECT *
FROM Employees e
LEFT OUTER JOIN Locations l
ON e.Location = l.Name
WHERE e.Id = 42

员工 42 所在的位置在位置表中有 2 行,因此此查询返回 2 行,每个位置对应于为员工 42 找到的每个位置。

好吧,我不知道如何将其转换为 NHibernate 映射 + 查询(我使用 Fluent NHibernate)。我倾向于认为我应该将Employees.Location映射为Locations.Name的引用,因此当运行我的HQL查询时它应该返回2个对象,但是NHibernate不允许我从引用中检索列表。所以我尝试了 HasMany 但它也不起作用,因为 NHibernate 希望 Locations 中有一个字段引用员工,这有点有意义。

我的 HQL 查询如下所示:

select e
from Employees e
left join e.Locations l
where e.SGId like :sgId

为什么我可以在常规 SQL 中执行此操作,而不能在 NHibernate 中执行此操作?

谢谢。

Help me translate this into proper NHibernate...

I have an Oracle database with 2 tables:

Employees:
    Id (unique id)
    FirstName (string)
    LastName (string)
    Location (string)

Locations:
    Name (string)
    Address (string)

As you can see the Employees table has a unique id, but the Locations table has no id whatsoever. The Name field is a regular field and is not unique.

In Oracle SQL I can run the following query:

SELECT *
FROM Employees e
LEFT OUTER JOIN Locations l
ON e.Location = l.Name
WHERE e.Id = 42

The location where the employee 42 is, has 2 rows in the Locations table, so this query returns 2 rows, one for each location found for employee 42.

Well, I can't figure out how to translate this into an NHibernate mapping + query (I use Fluent NHibernate). I tend to think that I should map Employees.Location as a Reference to Locations.Name, and so when running my HQL query it should return 2 objects, but NHibernate wouldn't let me retrieve a list from a Reference. So I tried HasMany but it doesn't work either, because NHibernate wants a field in Locations referring back to Employees, which kinda makes sense.

My HQL query looks like this:

select e
from Employees e
left join e.Locations l
where e.SGId like :sgId

Why can I do this in regular SQL and not in NHibernate?

Thanks.

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

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

发布评论

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

评论(2

心作怪 2024-12-10 12:25:37

我找到了解决方案,您必须将 HasMany(x => x.Locations) 与 .PropertyRef("Location") 结合使用,以便 hibernate 知道 Employee 中用于连接的字段应该是 Location (而不是默认为员工 ID)。

class Employee
{
    public virtual int Id {get;set}
    public virtual string FirstName {get;set;}
    public virtual string LastName {get;set;}
    public virtual string Location {get;set;}
    public virtual IList<Location> Locations {get;set;}
}

class EmployeeMapping : ClassMap<Employee>
{
    public EmployeeMapping()
    {
       Id(x=>x.Id);
       Map(x=>x.FirstName);
       Map(x=>x.LastName);
       Map(x=>x.Location);
       HasMany<Location>(x => x.Locations)
           .PropertyRef("Location")
           .KeyColumn("Name");
    }
}

此外,我发现我的方法存在一些缺陷,主要是由于我正在使用的奇怪的遗留数据库。这不会改变解决方案,但我想补充一点,如果您正在处理的表之一没有唯一的 ID,那么您就会遇到麻烦。 Hibernate 需要一个唯一的 id,所以在我的例子中,我必须想出一个由多个字段组成的复合 id,以使其唯一。这是另一场辩论,但我想在这里提到它,以帮助将来研究这个主题的人。

I found the solution, you have to use HasMany(x => x.Locations) in conjunction with .PropertyRef("Location") so that hibernate knows that the field in Employee to be used for the join should be Location (and not the id of employee as is the default).

class Employee
{
    public virtual int Id {get;set}
    public virtual string FirstName {get;set;}
    public virtual string LastName {get;set;}
    public virtual string Location {get;set;}
    public virtual IList<Location> Locations {get;set;}
}

class EmployeeMapping : ClassMap<Employee>
{
    public EmployeeMapping()
    {
       Id(x=>x.Id);
       Map(x=>x.FirstName);
       Map(x=>x.LastName);
       Map(x=>x.Location);
       HasMany<Location>(x => x.Locations)
           .PropertyRef("Location")
           .KeyColumn("Name");
    }
}

Also I found a few flaws in my approach due mainly to the weird legacy database I'm working with. That doesn't change the solution but I want to add that if one of the tables you're dealing with doesn't have unique ids, you're in trouble. Hibernate needs a unique id, so in my case I had to come up with a composite id made from multiple fields in order to make it unique. This is another debate but I wanted to mention it here to help whoever is researching this topic in the future.

蘸点软妹酱 2024-12-10 12:25:37

对于映射,如果要为一名员工返回 2 个位置,则需要在员工映射中使用集合/数组/列表/集合对象和 HasMany 来映射位置

class Employee
{
    public virtual int Id {get;set}
    public virtual string FirstName {get;set;}
    public virtual string LastName {get;set;}
    public virtual IList<Location> Location {get;set;}
}

class EmployeeMapping : ClassMap<Employee>
{
    public EmployeeMapping()
    {
       Id(x=>x.Id);
       Map(x=>x.FirstName);
       Map(x=>x.LastName);
       HasMany<Location>(x => x.Location)
                    .KeyColumn("Name")
                    .PropertyRef("Location") 
                    .LazyLoad();

    }
}

For you mapping you need to use a collection/array/list/set object and a HasMany on your employees mapping for the location if it is going to return 2 locations for the one employee

class Employee
{
    public virtual int Id {get;set}
    public virtual string FirstName {get;set;}
    public virtual string LastName {get;set;}
    public virtual IList<Location> Location {get;set;}
}

class EmployeeMapping : ClassMap<Employee>
{
    public EmployeeMapping()
    {
       Id(x=>x.Id);
       Map(x=>x.FirstName);
       Map(x=>x.LastName);
       HasMany<Location>(x => x.Location)
                    .KeyColumn("Name")
                    .PropertyRef("Location") 
                    .LazyLoad();

    }
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文