I have a question similar to this but in the context of L2S. I want to create a lookup table to store values that could be one of several possible datatypes. As suggested in the referenced question, I could use sql_variant datatype. However, L2S maps sql_variant to Object, which is suboptimal. I'm guessing it's possible to get at the table metadata to find out the exact type, but I expect that would be too messy. Has anyone tried it?
Another possibility is to split the data across multiple tables, each containing one strongly-typed field, and a master table that would contain the discriminator. But, here I run into another problem: L2S only supports one type of inheritance: Table per Class Hierarchy. So, naturally, my next thought was to put all the value columns and the discriminator in one table. This way I could have an abstract parent class and several inheriting members. The columns would have to be called something like ValueInt, ValueDec, etc. although in the ORM I can rename them all Value. So, that seems like an OK solution but still slightly awkward: all the NULL's in the table for one. Anyone with better thoughts?
P.S. As an aside, all the lookup values are currently stored as applicationSettings in the config file, which does provide strong-typing, but there are other issues with it.