Many-to-many ThroughAssociation issue

Hi,

We are trying to create a many-to-many relationship between two entities where the through entity also has it's own entity properties. If we create a ThroughAssociation between the two entities and enter an auto through entity then the relationship works. However this means that we can not add the extra entity properties as the through entity is not shown in the model.

I have tried the following:
- If I convert the through entity to explicit and update the database then LightSpeed tries to create an Id column in the through entity that already exists and therefore errors.
- If I add the properties directly to the database table without converting the through entity to explicit and then update my model from source then the extra properties are not available in code.
- If I add the through entity manually in the model (either from an existing database table or as a new entity in the model) and create the associations then update the database it will succeed initially. However on subsequent updates to the database it will try to recreate the existing Id in the through entity and error, and updates from source will delete the relationship between the first entity and the through entity (leaving the through association intact) despite the relationship existing in the database.

This means that currently the only way we can create through many-to-many relationships is to have auto through associations but as mentioned above we need extra entity properties on a through entity.

Any suggestions would be appreciated as we cannot proceed without a resolution to this.

Visual Studio Premium 2013,
LightSpeed v5

You will want to create the Through entity manually either by hand or from an existing database table and ensure it has the associations defined with the existing entities. Then create the through association as required and specify the Through entity on that relationship.

It sounds like you are doing that already but you are seeing issues on subsequent updates - is that correct? If so can you give some details about your model, is there a composite key on any of the entities etc?

Jeremy

Hi Jeremy,

Yes you're correct, I'm creating the through entity manually and specifying the entity on the relationship. Then on subsequent updates from source it will delete one of the relationships and update to database will try to add an Id column that already exists.

I have emailed a script with the database schema to Support@mindscape.co.nz and put this thread id in the subject.

The specific tables i am trying to join are "offer.Offer" and "offer.PricePlan". We need an "offer.OfferPricePlan" through entity that also contains extra entity properties (a date column and a decimal column).

Thanks Matt

Hi Matt,

Ive managed to track down whats occurring here. You have a Offer table in the migration schema which has the same PK constraint name as the table in the Offer schema. We expect constraint names to be unique so this is what is confusing the designer when it is checking for the relationships.

If you rename the constraint this will resolve the issue, e.g.

ALTER TABLE migration.[Offer]
DROP CONSTRAINT PK_Offer
GO
ALTER TABLE migration.[Offer] ADD CONSTRAINT
PK_Offer_1 PRIMARY KEY CLUSTERED 
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Jeremy