Mapping issues with legacy database

I'm currently evaluating the free version of LightSpeed for use in my company and have liked what I've seen so far, but I've run into a bit of a problem mapping one of the relationships in our legacy database.

We have the following entities in our schema (each one has its own table):

• ExpectedLineItem
• ActualLineItem
• LineItemGroupDetail

LineItemGroupDetail has a LineItemId column that can correspond to the PK in either the ExpectedLineItem or ActualLineItem tables depending on the LineItemTypeID. ExpectedLineItem and ActualLineItem have exactly the same columns but are in different tables. Not a good design but it would be hard to change at this point.

Our current stored procedure based code would do the select something like this:

SELECT * FROM tbLineItemGroupDetail LIG
LEFT JOIN tbExpectedLineItem E on E.ExpectedLineItemID = LIG.LineItemID AND LIG.LineItemTypeID = 1
LEFT JOIN tbActualLineItem A ON A.ActualLineItemID = LIG.LineItemID AND LIG.LineItemTypeID = 2

I've read the documentation on inheritance here and hope I'm not just missing something obvious, but it seems our situation is a little different from the examples there. The LineItemGroupDetail entity is not a base class for the Expected or Actual line items, it only contains them, so it doesn't seem as if I can set up an inheritance relationship using LineItemTypeID as the discriminator.

What I've tried to do so far is to set up a one-to-many relationship for both ExpectedLineItem and ActualLineItem to LineItemGroupDetail and this property to LineItemGroupDetail:

    public virtual LineItem LineItem
    {
        get
        {
            if (LineItemType == LineItemType.Expected)
                return ExpectedLineItem;

            return ActualLineItem;
        }
    }

The classes look like this:

public partial class ActualLineItem : LineItem
{
}

public partial class ExpectedLineItem : LineItem
{
}

public abstract class LineItem : EntityBase
{
// all properties defined here since Actual and Expected have the same columns
}

... however this doesn't work because it seems the ID column of the backreference isn't configurable and it expects there to be an ExpectedLineItemId and ActualLineItemId in the LineItemGroupDetail, when it is really just one ID.

Is there any way around this, or is there a better way to map this relationship? Thanks!

I dont think you will be able to map this directly but you could handle the association manually by issuing the queries from within the LineItem property on LineItemGroupDetail, e.g.

public virtual LineItem LineItem
{
    get
    {
        if (LineItemType == LineItemType.Expected)
            return UnitOfWork.FindById<ExpectedLineItem>(LineItemId);

        return UnitOfWork.FindById<ActualLineItem>(LineItemId);
    }
}

Unfortunately this means you will also need to manage any assignment via a settor manually also and LightSpeed wont be able to handle any eager loading on your behalf since it isn't a direct association.

Jeremy