Using Linq2Db with SQLServer Table with unknown extra columns

[Fungusware]

We have a situation where (for good or bad) we deploy a standard set of database Tables for every customer. There is also another set of Tables that can have multiple extra columns in them.

Example :

Customer
ID, Name, Address

CustomerCustomValues
ID, CustomerID, WorkLocation, LastContacted

The CustomerCustomValues table varies per customer.

Is there a way we can use Linq2DB to work with these CustomValues columns. Either with Runtime compilation or Custom Schema Mapping at runtime. I've tried both but so far can't get it to work.

Obviously I'm happy to refer to the custom columns by name, since they wont be known upfront.

Thanks in advance, open to any ideas.

[MaceWindu]

It is not clear to me how you want to use it, so there are some options

Option 1: custom configuration per-customer

1. You use customer-specific configuration name when create connection
2. In custom table mapping you specify column mapping with this configuration name

var db = new DataConntection(new DataOptions()/*...*/.UseConfigurationString("SomeCustomer"));

class Table
{
    [NotColumn] // without configuration - default mapping
    [Column("name", Configuration = "SomeCustomer")] // applied only to specific configuration
    [Column("name_long", Configuration = "SomeOtherCustomer")] // applied only to specific configuration
    public string Name { get; set; }
}

Cons:

• same connection cannot be used to work with several configurations
• because query cache use configuration name as one of keys, if application instance use several configurations it could lead to duplicate cache entries and cache starvation resulting in lower performance

Option 2: use Sql.Property dynamic mappings

Some examples could be found in tests here, here and here

Options 3: just define per-customer table mappings

[Table("CustomFields")] class Customer1Fields { /*...*/ }
[Table("CustomFields")] class Customer2Fields { /*...*/ }
[Table("CustomFields")] class Customer3Fields { /*...*/ }