Configurable Schema Names in Linq to SQL?

c#, database-schema, linq-to-sql

Solution

After a lot more poking around on Google, I eventually found an interesting approach here. The idea is to create a custom mapping source for the LINQ entities, which is nothing more than a pass-through for the normal functionality until you get to the point of the table name.

The exact code on that article wasn't actually working though (aside from an easily fixed compile error or two), since for some reason the `TableName` property wasn't actually being called. So I had to explicitly set it in the `MetaTable` object. And since it's a private field, this required reflection.

What I ended up with was as follows.

Custom Mapping Source

public class CustomMappingSource : MappingSource
{
    private AttributeMappingSource mapping = new AttributeMappingSource();

    protected override MetaModel CreateModel(Type dataContextType)
    {
        return new CustomMetaModel(mapping.GetModel(dataContextType));
    }
}

This is just a pass-through, there's nothing interesting happening here. But it does require the next level:

Custom Meta Model

public class CustomMetaModel : MetaModel
{
    private static CustomAttributeMapping mapping = new CustomAttributeMapping();
    private MetaModel model;

    public CustomMetaModel(MetaModel model)
    {
        this.model = model;
    }

    public override Type ContextType
    {
        get { return model.ContextType; }
    }

    public override MappingSource MappingSource
    {
        get { return mapping; }
    }

    public override string DatabaseName
    {
        get { return model.DatabaseName; }
    }

    public override Type ProviderType
    {
        get { return model.ProviderType; }
    }

    public override MetaTable GetTable(Type rowType)
    {
        return new CustomMetaTable(model.GetTable(rowType), model);
    }

    public override IEnumerable<MetaTable> GetTables()
    {
        foreach (var table in model.GetTables())
            yield return new CustomMetaTable(table, model);
    }

    public override MetaFunction GetFunction(System.Reflection.MethodInfo method)
    {
        return model.GetFunction(method);
    }

    public override IEnumerable<MetaFunction> GetFunctions()
    {
        return model.GetFunctions();
    }

    public override MetaType GetMetaType(Type type)
    {
        return model.GetMetaType(type);
    }
}

Again, all pass-throughs. Nothing interesting until we get to the next level:

Custom Meta Table

public class CustomMetaTable : MetaTable
{
    private MetaTable table;
    private MetaModel model;

    public CustomMetaTable(MetaTable table, MetaModel model)
    {
        this.table = table;
        this.model = model;

        var tableNameField = this.table.GetType().FindMembers(MemberTypes.Field, BindingFlags.NonPublic | BindingFlags.Instance, (member, criteria) => member.Name == "tableName", null).OfType<FieldInfo>().FirstOrDefault();
        if (tableNameField != null)
            tableNameField.SetValue(this.table, TableName);
    }

    public override System.Reflection.MethodInfo DeleteMethod
    {
        get { return table.DeleteMethod; }
    }

    public override System.Reflection.MethodInfo InsertMethod
    {
        get { return table.InsertMethod; }
    }

    public override System.Reflection.MethodInfo UpdateMethod
    {
        get { return table.UpdateMethod; }
    }

    public override MetaModel Model
    {
        get { return model; }
    }

    public override string TableName
    {
        get
        {
            return table.TableName
                        .Replace("CRPDTA", ConfigurationManager.AppSettings["BusinessDataSchema"])
                        .Replace("CRPCTL", ConfigurationManager.AppSettings["ControlDataSchema"]);
        }
    }

    public override MetaType RowType
    {
        get { return table.RowType; }
    }
}

That's where the (semi-) interesting stuff happens. The `TableName` property is still the custom one I wrote based on the original article I found (linked earlier). All I needed to add was the reflection in the constructor to explicitly set the table name on the tables.

With this, I just needed to set my data context usage to use this custom mapping:

using (var db = new BusinessDBContext(ConfigurationManager.ConnectionStrings["BusinessDBConnectionString"].ConnectionString, new CustomAttributeMapping()))

Problem

Original source