Associations in LINQ to SQL dbml files are somewhat confusing. Here is my aide-memoir to how they work, having had to wrap my own head around these without much documentation on how they work..
Associations In Brief
- There are always two entries for any association, with the same name. They may be in different tables or in the same table in the reference is to the same table.
- It follows that there should always be a matching pair – otherwise you might get errors. If you add one, always add the reciprocal. If you remove one, always remove the reciprocal.
- To find the matching association:
– use the Type attribute to locate the reciprocal table (from Table.Type.Name)
– find an association in the reciprocal with the same name but where IsForeignKey is the reverse (e.g. find false if your association has it set to ‘true’ – see below)
- One association is always the parent (IsForeignKey is absent or "false"), and the other is the child (IsForeignKey="true") – even if one-to-one.
- If the relation is not One-to-Many (the default) the cardinality is always specified in the parent.
- The "Member" is how the association will appear inside the containing table: e.g. Orders is the member of the customer-order association in the Customer class. Ensure it does not conflict with
- If SQL specifies cascaded deletes on a child table the DeleteOnNull="true" attribute will be defined: this is always defined on the Child association.
- A parent association always specifies "OtherKey", which relates to the reciprocal table
- A child association always specifies "ThisKey" which relates to its own table
- A parent won’t specify "ThisKey" if the key is the Primary Key of the table
- A child association won’t specify "OtherKey" unless it isn’t the PrimaryKey on the other table.
- The value of the ThisKey and OtherKey attributes represent the Member value of a column – this is blank on the table unless you’ve renamed the column to something other than the database column name.
- If an association has multiple column references, these are listed in the attribute comma-separated values in the attribute value.
More later if I find any other things of note.