How to implement options with multiple return types

Question

I have an application where a multiple shops will share the data. There is an Options table that defines various program options. I have a varchar column that defines the value type, int, bool, Guid, string, etc. There are two tables that define the option values, one for system wide options and one for local options. The base Options table defines if the local option can be selected before the system wide option. The Shop and Global option tables are basically the same structure except the ShopOption table has a ShopId FK for the shop the record belongs to. Each of these tables store the option values as varchar although the string may represent an integer, Guid, bool, or may actually be a string. I need to show a form with a tab for the local option, a tab the global option, and a tab to specify if the shop can overide the global. What I am not doing right is to get an option entity and get the value as the type it should be.

For instance:
GetOption(SessionTimeout) should return an Option entity and the value should be an integer type.
GetOption(DefaultCustomer) should return an Option entity and the value should be a Guid type.

I am searching for an answer using design patterns, and think a factory pattern may be what I want, but I am just not getting it.

Answer 1

The underlying issue is that you are suffering from the inner platform effect, whereby you are trying to make a database within a database by storing as varchar what should be distinct, typed columns.

You have given yourself the ability to add options at runtime. However, they won't mean anything if the application doesn't understand them, and you can't add that understanding at runtime. The set of options must be known at design-time, which means the schema can be known at design-time, which means you have no need to abstract the structure into varchar values.

Make a table with columns representing each of your options, and use normal ORM practices to declare a data type to which it maps.

The abstraction isn't actually buying you anything.

Edit in response to comment from OP:

To implement cascading settings, you could make an OptionSet table with a column per option. There would only be one row, representing the global set. For each option which can be overridden by a manager, add a nullable column to the Store table.

You can then have a method which asks a Store to coalesce the effective options:

public class Store
{
    public virtual bool? AllowSavePasswords { get; set; }

    public virtual OptionSet GetEffectiveOptions(OptionSet globalOptions)
    {
        return new OptionSet
        {
            AllowSavePasswords = this.AllowSavePasswords ?? globalOptions.AllowSavePasswords,
            LoginTimeout = globalOptions.LoginTimeout

            // Repeat pattern for all options
        }
    }
}

As you can see, this allows everything to remain strongly-typed, while addressing the issue of options which can't be overridden. It also expresses the intent of which options can be overridden by specifying all of them on the Store table (reflecting their scope) and making them nullable (reflecting their optional nature).

The nice part is that there are no new techniques to learn or "magic" to implement (unless you hadn't seen the ?? operator, which is the equivalent of T-SQL's COALESCE function).

Answer 2

What you're asking for is late-binding, ie the ability to assign the variable type at runtime instead of compile-time. The immediate answer is that C# doesn't currently support that, and when it is supported it still won't fully solve your issue.

Your best bet is to use generics, which will increase type-safety but still won't prevent stupid errors. Creating a method like:

public T GetOption<T>(string key)
{
    // Retrieve the option type and value
    // Check that the option type and return type (T) are compatible
    // cast the option value to T
    // return the value
}

will let you attempt to cast the database result to the return type of T, but it will generate an exception if the cast fails (ie: you try requesting a GUID option as an int).