How to I define/change the mappings for Linq To Sql in code.

Question

I wish to be able to change the table a class is mapped to at run time, I can’t do this if all the mappings are defined with attributes. Therefore is there a way to define the mappings at runtime in code.

(I would rather not have to maintain xml mapping files.)

---

Say I have two tables:

• OldData
• NewData

and sometimes I wished to query OldData and other times I wished to query NewData. I want to use the same code to build the queries in both cases.

---

See also "How to map an Entity framework model to a table name dynamically"

Answer 1

Use a GenericList to class to Query this

Public class Querybuilder where T: Class {

}

Answer 2

In order to make this truly transparent, you have to jump through some pretty crazy hoops, but it can be done by overriding all the Meta*** classes with your own derived types.

This would actually be fairly straightforward with a proxy/method interception library like Castle, but assuming the lowest common denominator here, it's basically a long and boring ordeal of implementing every single meta method to wrap the original type, because you can't derive directly from any of the attribute mapping classes.

I'll try to stick to the important overrides here; if you don't see a particular method/property in the code below, it means that the implementation is literally a one-liner that wraps the "inner" method/property and returns the result. I've posted the whole thing, one-line methods and all, on PasteBin so you can cut/paste for testing/experimentation.

The first thing you need is a quick override declaration, which looks like this:

class TableOverride
{
    public TableOverride(Type entityType, string tableName)
    {
        if (entityType == null)
            throw new ArgumentNullException("entityType");
        if (string.IsNullOrEmpty(tableName))
            throw new ArgumentNullException("tableName");
        this.EntityType = entityType;
        this.TableName = tableName;
    }

    public Type EntityType { get; private set; }
    public string TableName { get; private set; }
}

Now the meta classes. Starting from the lowest level, you have to implement a MetaType wrapper:

class OverrideMetaType : MetaType
{
    private readonly MetaModel model;
    private readonly MetaType innerType;
    private readonly MetaTable overrideTable;

    public OverrideMetaType(MetaModel model, MetaType innerType,
        MetaTable overrideTable)
    {
        if (model == null)
            throw new ArgumentNullException("model");
        if (innerType == null)
            throw new ArgumentNullException("innerType");
        if (overrideTable == null)
            throw new ArgumentNullException("overrideTable");
        this.model = model;
        this.innerType = innerType;
        this.overrideTable = overrideTable;
    }

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

    public override MetaTable Table
    {
        get { return overrideTable; }
    }
}

Again, you have to implement about 30 properties/methods for this, I've excluded the ones that just return innerType.XYZ. Still with me? OK, next is the MetaTable:

class OverrideMetaTable : MetaTable
{
    private readonly MetaModel model;
    private readonly MetaTable innerTable;
    private readonly string tableName;

    public OverrideMetaTable(MetaModel model, MetaTable innerTable,
        string tableName)
    {
        if (model == null)
            throw new ArgumentNullException("model");
        if (innerTable == null)
            throw new ArgumentNullException("innerTable");
        if (string.IsNullOrEmpty(tableName))
            throw new ArgumentNullException("tableName");
        this.model = model;
        this.innerTable = innerTable;
        this.tableName = tableName;
    }

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

    public override MetaType RowType
    {
        get { return new OverrideMetaType(model, innerTable.RowType, this); }
    }

    public override string TableName
    {
        get { return tableName; }
    }
}

Yup, boring. OK, next is the MetaModel itself. Here things get a little more interesting, this is where we really start declaring overrides:

class OverrideMetaModel : MetaModel
{
    private readonly MappingSource source;
    private readonly MetaModel innerModel;
    private readonly List<TableOverride> tableOverrides = new 
        List<TableOverride>();

    public OverrideMetaModel(MappingSource source, MetaModel innerModel,
        IEnumerable<TableOverride> tableOverrides)
    {
        if (source == null)
            throw new ArgumentNullException("source");
        if (innerModel == null)
            throw new ArgumentNullException("innerModel");
        this.source = source;
        this.innerModel = innerModel;
        if (tableOverrides != null)
            this.tableOverrides.AddRange(tableOverrides);
    }

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

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

    public override MetaFunction GetFunction(MethodInfo method)
    {
        return innerModel.GetFunction(method);
    }

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

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

    public override MetaTable GetTable(Type rowType)
    {
        return Wrap(innerModel.GetTable(rowType));
    }

    public override IEnumerable<MetaTable> GetTables()
    {
        return innerModel.GetTables().Select(t => Wrap(t));
    }

    private MetaTable Wrap(MetaTable innerTable)
    {
        TableOverride ovr = tableOverrides.FirstOrDefault(o => 
            o.EntityType == innerTable.RowType.Type);
        return (ovr != null) ?
            new OverrideMetaTable(this, innerTable, ovr.TableName) : 
            innerTable;
    }

    private MetaType Wrap(MetaType innerType)
    {
        TableOverride ovr = tableOverrides.FirstOrDefault(o =>
            o.EntityType == innerType.Type);
        return (ovr != null) ?
            new OverrideMetaType(this, innerType, Wrap(innerType.Table)) :
            innerType;
    }

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

We're almost done! Now you just need the mapping source:

class OverrideMappingSource : MappingSource
{
    private readonly MappingSource innerSource;
    private readonly List<TableOverride> tableOverrides = new
        List<TableOverride>();

    public OverrideMappingSource(MappingSource innerSource)
    {
        if (innerSource == null)
            throw new ArgumentNullException("innerSource");
        this.innerSource = innerSource;
    }

    protected override MetaModel CreateModel(Type dataContextType)
    {
        var innerModel = innerSource.GetModel(dataContextType);
        return new OverrideMetaModel(this, innerModel, tableOverrides);
    }

    public void OverrideTable(Type entityType, string tableName)
    {
        tableOverrides.Add(new TableOverride(entityType, tableName));
    }
}

Now we can FINALLY start using this (phew):

var realSource = new AttributeMappingSource();
var overrideSource = new OverrideMappingSource(realSource);
overrideSource.OverrideTable(typeof(Customer), "NewCustomer");
string connection = Properties.Settings.Default.MyConnectionString;
using (MyDataContext context = new MyDataContext(connection, overrideSource))
{
    // Do your work here
}

I've tested this with queries and also with insertions (InsertOnSubmit). It's possible, actually rather likely, that I've missed something in my very basic testing. Oh, and this will only work if the two tables are literally exactly the same, column names and all.

It will probably mess up if this table has any associations (foreign keys), since you'd have to override the association names too, on both ends. I'll leave that as an exercise to the reader, since thinking about it makes my head hurt. You'd probably be better off just removing any associations from this particular table, so you don't have to deal with that headache.

Have fun!

Answer 3

I have written an extension method to change the table name in runtime. It is done by reflection, so not really supported, but it works. Hope it helps

Dim table As MetaTable = ctx.Mapping.GetTable(GetType(TLinqType))
table.SetTableName("someName")


<Extension()> _
    Public Sub SetTableName(ByVal table As MetaTable, ByVal newName As String)
        Try
            'get the FieldInfo object via reflection from the type MetaTalbe
            Dim tableNameField As FieldInfo = table.GetType().FindMembers(MemberTypes.Field, BindingFlags.NonPublic Or BindingFlags.Instance, Function(member, criteria) member.Name = "tableName", Nothing).OfType(Of FieldInfo)().FirstOrDefault()

            'check if we found the field
            If tableNameField Is Nothing Then
                Throw New InvalidOperationException("Unable to find a field named 'tableName' within the MetaTable class.")
            End If

            'get the value of the tableName field
            Dim tableName As String = TryCast(tableNameField.GetValue(table), [String])

            If String.IsNullOrEmpty(tableName) Then
                Throw New InvalidOperationException("Unable to obtain the table name object from the MetaTable: tableName field value is null or empty.")
            End If

            'set the new tableName
            tableNameField.SetValue(table, newName)
        Catch ex As Exception
            Throw New ApplicationException(String.Format("Error setting tablename ({0}) for entity {1}!", newName, table), ex)
        End Try
    End Sub