How to make loose association

Hello guys

I'm just started with LS and i start to like it.
I'm for the moment working with a legacy database and trying to make a loose association between two entitys.
Is this possible in the designer at all? As i read the documentation there has to be key/id.
Is it possible to do this in a partial class programtically using a naturalkeys?

Thanks in advance
greg

Hi Greg,

Not sure what you mean by a "loose association."  A LightSpeed association does have to be backed by a key field/column which contains the Id of the associated entity.  That column doesn't have to have a foreign key constraint in the database -- if it doesn't, then the designer won't infer the association, but you can still create the association manually.  If this is your scenario, let us know and I'll post a walkthrough.

If your lookup key doesn't match the Id of the associated entity, but instead matches some other unique column, you can still perform lookups in the partial class using LINQ or query objects:

partial class House {
  public Person Owner {
    get { return this.UnitOfWork.People.FirstOrDefault(p => p.CustomerNumber == this.OwnerNumber); }
  }
}

You can't represent this in the designer though.

Yes please. Post me a walk through.
This is really what i want to achieve.

Thanks again.
Greg

Okay, I'm going to assume you have two tables, Customer and Order.  Each has a single identity column which I'll assume is called Id.  Furthermore, Order has a CustomerRef column which contains the Id of the customer the order is for, but which does NOT have a foreign key constraint in the database.

When you drag these two tables onto the designer, you'll see two entities, Customer and Order.  The Order entity will have a CustomerRef property (amongst others).  There will be no association between them because there was no foreign key constraint in the database.

The first thing we need to do is set up the association.  Open the toolbox, select the OneToManyAssociation tool, and drag a line from Customer to Order.  This creates an association in LightSpeed between the two.

You will see that at the Order end, the association is named Customer (i.e. there will be an Order.Customer property).  LightSpeed defaults to assuming that the backing column for the association will be CustomerId (the name of the association plus "Id").  Since I've assumed that the backing column is actually named CustomerRef, I need to override this default.  To do this, select the arrow you just created, go into the Properties grid, find the Column Name setting, and enter CustomerRef in there.

This leaves us with one more thing to do.  At the moment, the CustomerRef column is mapped to both the CustomerRef property and the Customer association.  LightSpeed can't map one column to two places.  So you need to just delete the CustomerRef property from the Order entity.  If you need to access this value, don't worry, it is still mapped, but it is named CustomerId (because in LightSpeed the backing field for an association is always named XxxId, even if it is mapped to a different column).

With these changes in place you should be good to go!

Sorry but no success.

Here are mytables:

CutPiece (Customer)
    ID [uniqueidentifier],
    CutPatternID [varchar(20)],
    PieceID [int],
    OptionID [int],

CutOption (Order)
    [ID] [uniqueidentifier],
    [CutPatternID] [varchar],
    [OptionID] [int],
    [Mc] [int],

As you can see no foreign keys. :-(
Made a relation as you described, and changed columnname to CutPatternId. Dragged a one to many relation to the designer, the Oneside  in CutPiece and many in CutOption.
I also removed the property CutPatternID on the many side. Right?

When using i got the error, Operator '==' cannot be applied to operands of type 'System.Guid' and 'string', it seems that it still using the ID (guid)?

I also tried to do a LINQ for this to tables without any relations, just as two free tables:

var query = from cutpiece in UnitOfWork.CutPieces
                   join cutoption in UnitOfWork.CutOptions
                        on new { cutpiece.CutPatternId, cutpiece.OptionId }
                equals new { cutoption.CutPatternId, cutoption.OptionId }
            where
                cutpiece.PieceId == 1
            group new { cutpiece, cutoption } by new
            {
                cutpiece.CutPatternId, cutpiece.PieceId
            } into g
            orderby
                g.Key.CutPatternId, g.Key.PieceId
            select new
            {
                g.Key.CutPatternId,
                PieceID = ( Int32? )g.Key.PieceId,
                Maxdim = ( System.Int64? )g.Max(p => p.cutoption.Mc/ 100),
            };

     but it ended up with an error

".. [LightSpeedException: Query Error: Could not find field [Key] on model .."

In this case the grouping a think is involved in someway.

So any hints in right direction would apreicated.

Best regards
greg

Looking at your tables this looks more like you want to match to a non-identity column.

To use a LightSpeed association, your 'foreign key' MUST refer to the Id of the other table.  Since CutPatternID is a varchar and the Id of the other table is a GUID, I don't think this can be the case for you.  I'm guessing you actually want to match CutPatternID to CutPatternID.  As previously mentioned, this is not possible in the designer -- it can only be done in the partial class (see http://www.mindscapehq.com/forums/Post.aspx?ThreadID=4673&PostID=16647, second paragraph).  In your case something like:

partial class CutPiece {
  public IEnumerable<CutOption> CutOptions {
    get { return this.UnitOfWork.Query<CutOption>().Where(opt => opt.CutPatternId == this.CutPatternId); }
  }
}

partial class CutOption {
  public CutPiece CutPiece {
    get { return this.UnitOfWork.Query<CutPiece>().FirstOrDefault(p => p.CutPatternId == this.CutPatternId); }
  }
}