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

Joining across schemas is no problem, you just need to specify the schema in your mapping:

public sealed class CustomerMap : ClassMap<Customer>
{
   public CustomerMap()
   {
      Table("Customer");
      Schema("dbo");
      // etc.
   }
}

Your order table should have CustomerId as a foreign key, not CustomerName. That's the standard way to implement a one-to-many relationship and is not particular to NHibernate. If you have that, the mapping in OrderMap is:

References(x => x.Customer, "CustomerId");