We had an enquiry from a customer about using the LightSpeed designer to generate a database from a model containing many-to-many associations and I thought it was worth posting here.
Recall that in LightSpeed, many-to-many associations are represented using through associations, in which the two associated entities are related via a “through” entity. (The through entity exists to work around the fact that in the relational model, a foreign key can point to only one other record.) In the designer, a through association can be represented in two ways: with an explicit through entity, which is shown on the designer and can have properties of its own, or with an auto through entity, which isn’t shown as a separate entity but is defined implicitly by the association. These two representations result in the same generated code and database structure, but get set up in slightly different ways.
Model-first development using an auto through entity
In most cases, through entities are very simple objects which exist only to link the main entities in a many-to-many way. They don’t need their own properties, they don’t have custom table mappings, they don’t have any attributes such as caching or validation, etc. In these cases you can get away with using an auto through entity. This is easy to set up using the designer: just drag a Through Association arrow between your entities, and enter a name into the Auto Through Entity property on that arrow:
The only option we might want to worry about there is the Auto Through Entity Identity Type. By default, the designer will create a through table with an integer (Int32) identity column, but if you love GUIDs or you’re expecting a stupendous number of entities, then you might want to change this to Guid or Int64. In any case, when you do an Update Database, the designer will create the through table for you:
Model-first development using an explicit through entity
Occasionally you will need to customise the through entity to a greater extent than just name and identity type. For example, you may need to associate data with each through entity. An example would be a tagging system in a video sharing site, where there is a many-to-many association between videos and tags, but you might want to capture who tagged a given video with a given tag and when. You could store this information on the through entity. In such cases you must represent the through association using an explicit through entity, and you must also explicitly show the one-to-many associations between the “main” entities and the through entity. Let’s see how that looks in the designer:
Now when you do an Update Database, the prompt looks slightly different, because the through entity appears at the top level instead of under one of the main entities, but the result is still the same: the designer offers to create the through table for you.
You don’t need to do anything special to create the database from your model just because it contains through associations. However, you do need to make your through associations are set up correctly, and how you do that differs depending on whether you need to customise the through entity.