Mapping two links in the same class, but the reference class has a composite key

Question

I have the following database tables (they are shortened for clarity):

CREATE TABLE [dbo].[prod_uom]( [prod_id] [dbo].[uid] NOT NULL, --Primary key [uom_type] [numeric](9, 0) NOT NULL, --Primary Key ) CREATE TABLE [dbo].[order_line]( [order_line_id] [dbo].[uid] NOT NULL, --Primary key [prod_id] [dbo].[uid] NOT NULL, --Foreign key [uom_type_requested] [numeric](9, 0) NOT NULL, --Foreign key [uom_specified] [numeric](9, 0) NOT NULL --Foreign key ) 

The entity I came across is as follows (Abbreviated):

 public class OrderLine { public virtual Guid OrderLineId { get; private set; } public virtual ProductUom UomRequested { get; set; } public virtual ProductUom UomSpecified { get; set; } } public class ProductUom { public virtual Guid ProductId { get; private set; } public virtual decimal UomType { get; set; } } 

Basically, I have 2 references to the PROD_UOM table inside my ORDER_LINE class. My first attempt to match this:

 public OrderLineMap() { Table("ORDER_LINE"); Id(x => x.OrderLineId, "ORDER_LINE_ID"); References(x => x.UomSpecified) .Columns(new string[] { "PROD_ID", "UOM_SPECIFIED" }); References(x => x.UomRequested) .Columns(new string[] { "PROD_ID", "UOM_TYPE_REQUESTED" }); } 

This mapping gets a terrible error (PROD_ID is referenced twice):

 System.IndexOutOfRangeException: Invalid index 17 for this SqlParameterCollection with Count=17. 

Is there an easy way to match this relationship?

Answer 1

I am sure you cannot match the same column twice, so my suggestion was to split the PROD_ID column in ORDER_LINE into two: PROD_ID_SPEC and PROD_ID_REQ.

There are some other options, but this is the one I would suggest, as they are probably even more inelegant than that.