I am extracting data from a set of JSON files into an MS T-SQL database using Entity Framework.
There are a bunch of sub-collections in the JSON files (counted 20 so far) that all follow the pattern "CollectionName":{"Code":"SomeCode","Description":"Some Description"}.
For example: "Country":{"Code":"GB","Description":"Great Britain"} or "Language":{"Code":"FR","Description":"French"}
The code I am working with uses this pattern: an Entity is created called CollectionName, which maps to a table with PK, Code & Description columns and then another Entity called SourceCollection (ex: PersonLanguage) which maps to a Bridge Table having the Source PK & CollectionName PK for each. When you have a lot of these little sub-collections, that is a lot of tables.
As a T-SQL programmer, I have solved similar problems in the past by creating a 'catchall table' that has a PK, a CollectionName Column, and then a Code & Description column as above. So all these little collections reside in a single table, with a foreign key pointer in the source table.
I cannot find any description of how to implement this in Entity Framework, can anyone point me in the right direction with either a link or some sample code?
The pattern you're describing is sometimes called a "common lookup table" and is generally considered an anti-pattern for reasons of referentially integrity and constraints.
Merits of the design decision aside, you have two options:
A) Create a new EF entity with properties for Id, CollectionName, Code and Description and map your existing classes and data to that entity for CRUD operations via some pattern such as Repository.
B) Use EF type inheritance with table-per-hierarchy mapping and allow EF to map multiple entities to the same table. The abstract parent type would have Id, Code and Description properties. EF will automatically create a discriminator column that serves the same purpose as CollectionName.