Join between two non-key fields

Question

I'm working on putting together a simple POC app using Fluent NHibernate to attempt to show that it can do everything our current hand-rolled data access tool and so much more. One of the fringe cases that my boss is worried about is the ability to access multiple schemas within the same database in one query. So far I have been able to pull data from tables in both schemas so long as the query only touches one schema at a time. If I try to execute a command that will join tables from both schemas, it blows up.

Based on the error messages that I'm seeing, I don't believe that the problem is with joining across schemas, but rather with the fact that the two fields I need to join the tables on are both non-key fields. The structure of the two table is something like this:

Customer (in schema 1)
--------
int CustomerId (Primary Key)
string Name
...other fields

Order (in schema 2)
-------- 
int OrderId (primary key)
string CustomerName
...other fields

Using sql directly I can join on the Name/CustomerName fields and get the data from both tables. However, using NHibernate I keep getting an "System.FormatException : Input string was not in a correct format" when trying to pull data from the Order table and include data from the Customer table. This leads me to believe that NHibernate is trying to join on the CustomerName field and CustomerId field.

I know how to tell it to use the CustomerName field in my Order mapping, but I can't figure out a way to telling to join on the Name field of the Customer table.

My Mappings look something like this:

public class CustomerMap : ClassMap<Customer>
{
    public CustomerMap()
    {
        Id(x => x.Id)
            .Column("CustomerId");
        Map(x => x.Name);
    }
}


public class OrderMap : ClassMap<Order>
{
    public OrderMap()
    {
        Schema("schema2");
        Id(x => x.Id)
            .Column("OrderID");
        Map(x => x.CustomerName)
            .Column("CustomerName");
        References<Customer>(x => x.Customer, "CustomerName");
    }
}

The SQL I'd write to get the results I want would be something like:

select o.OrderId, o.CustomerName, c.CustomerId
from order o
inner join customer c on c.Name = o.CustomerName

Is this even possible? Is there a different/better way to go about this?

Answer 1

I haven't working with multiple schemas, but the approach I've found for mapping non-key fields is as follows:

In OrderMap... References(order => order.Customer).Column("CustomerName").PropertyRef("Name");

Where PropertyRef("Name") is actually referring to the Name property on your Customer class (which you would define in CustomerMap).

I'm just getting started with FNH, so you may find a better solution, but I hope this helps.

Answer 2

I am giving example how can you Map NON key fields in Hibernate using annotation.
Please convert it to corresponding nHibernate.

CREATE TABLE `Customer` (
  `CUSTOMER_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(100) NOT NULL,
  PRIMARY KEY (`CUSTOMER_ID`)
)

CREATE TABLE `Order` (
  `ORDER_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_NAME` varchar(100) NOT NULL,
  PRIMARY KEY (`ORDER_ID`)
)

Customer Entity

@Entity
@Table(name = "CUSTOMER")
public class Customer{

    private long customerId;
    private String name;
    private Order order;


    public Customer() {
    }

    public Customer(String name) {
        this.name= name;

    }

    @Id
    @GeneratedValue
    @Column(name = "CUSTOMER_ID")
    public long getCustomerId() {
        return this.customerId;
    }

    public void setCustomerId(long customerId) {
        this.customerId= customerId;
    }

    @Column(name = "NAME", nullable = false, length = 100, insertable=false, updatable=false)
    public String getName() {
        return this.name;
    }
    public String setName(String name) {
        return this.name;
    }

    @ManyToOne
    @JoinColumn(name = "NAME", referencedColumnName = "CUSTOMER_NAME")
    public Order getOrder() {
        return order;
    }


    public void setOrder(Order order) {
        this.order= order;
    }


}

Order Entity

@Entity
@Table(name = "ORDER")
public class Order implements Serializable {

    private long orderId;
    private String customerName;

    public Ortder() {
    }

    public Order(String customerName) {
        this.customerName= customerName;

    }

    @Id
    @GeneratedValue
    @Column(name = "ORDER_ID")
    public long getOrderId() {
        return this.orderId;
    }

    public void setOrderId(long orderId) {
        this.orderId= orderId;
    }

    @Column(name = "CUSTOMER_NAME", nullable = false, length=250)
    public String getCustomerName() {
        return this.customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName= customerName;
    }


}

---

Customer customer = new Customer("C1");
session.load(customer , 5L);    
System.out.println(customer.getName());
Order order = customer.getOrder();
System.out.println(order.getCustomerName());

SQL will be generated like ( I have removed the alias generated by Hibernate)

 select customer.CUSTOMER_ID, customer.NAME, order.ORDER_ID,
 order.CUSTOMER_NAME 
 from CUSTOMER  
left outer join ORDER **on NAME=CUSTOMER_NAME** where CUSTOMER_ID=?