Custom schema names in Linq to SQL?

Question

Let's say I'm creating a Linq to SQL context for a database for some simple data access for the application. The tables in this database refer to a specific schema (in this case, “CRPDTA” for some tables, “CRPCTL” for others). However, when the application proceeds to production, the same tables will belong to a different schema in the production database (in this case, “PRODTA” and “PRODCTL”).

Is there a way to customize the schema name for Linq 2 SQL, something defined in the application configuration file?

In the data context class, I see the attributes of the table name:

[global::System.Data.Linq.Mapping.TableAttribute(Name="CRPDTA.TableName")] 

Technically, I could manipulate this line directly, but editing an automatically generated file would mean having to edit it anytime it is generated. Therefore, I would prefer something more sustainable. But so far, nothing of the kind has been found.

Is this something that simply cannot be done with this tool? Maybe someone has a creative solution that worked in a similar situation?

Answer 1

After much more appeared on Google, I found an interesting approach here here . The idea is to create a custom mapping source for LINQ objects, which is nothing more than a pass-through for normal functionality until you reach the table name point.

The exact code of this article did not actually work (except for a simple fixed compilation error or two), because for some reason the TableName property was not actually called. Therefore, I had to explicitly set it in a MetaTable . And since this is a private field, it requires reflection.

What I finished was as follows.

Custom match 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 passage, nothing interesting happens here. But this requires the following level:

Custom metamodel

 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 the aisles. 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; } } } 

What happens is (semi) interesting stuff. The TableName property is still normal, which I wrote based on the original article that I found (linked earlier). All I needed to add was a reflection in the constructor to explicitly specify the table name in the tables.

In doing so, I just needed to configure the use of the data context to use this custom mapping:

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

Answer 2

Could you please advise where you get your CustomAttributeMapping type? Thanks, John