How to query Oracle database with NHibernate?

1
votes

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.

2
nhibernatefluent-nhibernate

2 Answers

1
votes

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.

0
votes

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();

    }
}