This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I have a Product entity that is associated with ProductQC and PriceChange entities through 1:1 associations. Basically the primary key in all the tables is the same value, so they are just joined on the primary keys. So I set them up in my model with 1:1 associations on the primary key, but I cannot load my Product entity as it is trying to load too many columns from the database. It seems as though it is creating foreign key columns in the entity, which are not mapped to the database so I get an out of range error when trying to load the entity. Here is the XML for the associations: <targetEntity>
<oneToOneAssociation Id="498e5171-2c29-4c1e-9e5a-27c6f0dd5fea" sourceName="Product" targetName="QC" isSourceNullable="false" isTargetNullable="true" columnName="products_id">
<entityMoniker name="/StoreEntities/ProductQC" />
</oneToOneAssociation>
<oneToOneAssociation Id="8c4c733a-12b4-472a-a489-58a6233dc2e5" sourceName="Product" targetName="PriceChange" isSourceNullable="false" isTargetNullable="true" columnName="products_id">
<entityMoniker name="/StoreEntities/PriceChange" />
</oneToOneAssociation>
</targetEntity>
|
|
|
I should probably give up on trying to pretty print stuff when I paste into here :). I tried the Visual Studio CodeForBlog extension and thought it would work, but I guess not. |
|
|
Yes, a one-to-one association has a foreign key -- it is not an Id-to-Id association. The reason for this is that Ids are immutable. If the association were Id-to-Id then you would not be able to reassign an association. From a quick test it looks like you can create an Id-to-Id association using the ForeignKeyFieldAttribute, but this brings you into the unpleasant world of natural keys. We don't yet have FKFA support in the designer for one-to-one associations, but we can add this if you want (it already exists for one-to-many associations). Please see http://www.mindscapehq.com/documentation/lightspeed/Working-with-Legacy-Databases/Using-Natural-Keys for information about implementing natural keys and http://www.mindscapehq.com/documentation/lightspeed/Working-with-Legacy-Databases/Using-Composite-Keys for more info about ForeignKeyFieldAttribute. Please note we have not fully investigated using FKFA to implement one-to-one Id-to-Id associations -- I only gave it a trivial smoke test! |
|
|
I only have this on a few classes so I would be fine doing this in the XML. How do I change the associations to do this in the XML outside of the designer?
|
|
|
Ok I read all the documentation but it is not clear to me at all how I would set up this mapping? It is pretty common to split entities across multiple tables where some data is very sparse (as we have done in our schema) to save on space in the database so I think it would be pretty important to support Id to Id 1:1 associations and have it so the extension entities primary key values are derived from the main entity primary key.
Let me know how I would change my model t support this.
|
|
|
You can only do it in code, not XML. So you would need to: 1.Copy the designer generated code into partial classes. You will need to do this for each end of the association: one end will have an EntityHolder and a foreign key field, and the other will just have an EntityHolder (and there will be public wrapper properties for all of these). 2. Tell the designer not to generate the association code itself, by selecting the association and setting Generation to None. 3. In the partial class containing the unwanted foreign key, delete the foreign key field and its wrapper property which you copied from the generated code. Then go to the EntityHolder field and apply the following attribute: [ForeignKeyField("Id")] This tells LightSpeed that the association is backed by the Id field instead of by the normal foreign key field. |
|
|
So this would be done on the ProductQC and PriceChange entities, not in the main Product Entity (it would remain as normal)? This is not entirely clear at the moment to me; perhaps you can post a sample?
But if the designer supported this, it could be done in the designer for the association? That would probably be preferred.
|
|
|
It's probably easiest if I add support in the designer. It's too late to get this into the next nightly build, but I'll aim to get it into the one after. |
|
|
Designer support will be in the next nightly build. Select the association arrow and look for the new Key Property Reference setting. |
|
|
Awesome, thanks! |
|
|
Ok, I see the new property, but I am not sure what I am supposed to put in there? Should I put 'Id' in there? Or something else? Do I still need to do the other steps mentioned above to turn off the association generation? |
|
|
Ok, look like putting the Id property name in there works. It would be nice if many of these property editing boxes were actually drop down fields somehow, so you could select the value using a drop down of valid results, rather than guessing on what to put n there :) |
|
|
The new support works nicely. The ability to set the values in the designer using a drop down would be nice, but for the most part just setting the foreign key property to 'Id' is what you need to do for a 1:1 association on the primary keys. I would suggest that the documentation in the users guide be updated to discuss this kind of association, and how to model is, since I think it would be pretty common? |
|