The problem
You have SQL Servers for each of your environments (development, test, pre-prod, production) and in each one, the table names are different. In fact, it’s not the table names that are different but the schema names but since the schema name is part of the table name, it must be specified.
Ex : dev.ZeTable et prod.ZeTable
Easy to solve, right? Just put the schema value in the config file and do a simple concatenation at runtime. Well, it’s not as easy as it seams because the table name is stored in an attribute of the partial class generated by the LINQ to SQL designer and Microsoft didn’t provide a way or method to change it at runtime.
[Table(Name="dev.ZeTable")]
public partial class TheTable
Solution
The solution is to dynamically load at runtime a mapping specific for each environment. Here’s how to do it:
Initial Mapping
Use the LINQ to SQL designer to create the initial mapping. The .dbml file will act as the starting point for our specialized mappings.
SqlMetal
Next, use the SqlMetal.exe tool to generate a mapping file but instead of pointing the tool to the database and redo all our customization in XML, the trick is to point it to the .dbml file we created earlier using the LINQ to SQL designer. This way, all our initial customizations are preserved. Nice!
From the Visual Studio 2008 Command Prompt, invoque SqlMetal like this :
C:\>SqlMetal /map:"MyApp.PROD.map" "C:\Visual Studio 2008\Projects\MyDataClasses.dbml" /code:"test.cs"
Microsoft (R) Database Mapping Generator 2008 version 1.00.21022
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.
SqlMetal.exe will generate a C# or VB class. We won’t use it so you can delete it.
Next, open the mapping file and change the name of the tables to reflect the right schema:
<Table Name="prod.ZeTable" Member="TheTables">
A good practice might be to include the schema name in the mapping file name:
Ex : MyApp.PROD.map
DataContext
Next, we need to dynamically load the mappings at runtime by specifying a connection string and the mapping info in the DataContext constructor. The Microsoft’s documentation is located here :
http://msdn.microsoft.com/en-us/library/bb534562.aspx
You can store that info in the config file and load it dynamically:
<appSettings>
<add key="MyAppConnectionString" value=" MyAppDev" />
<add key="MyAppLinqMappings" value="c:\MyApp.DEV.map" />
</appSettings>
<connectionStrings>
<add name="MyAppDev"
connectionString="Data Source=MyDevServer;Initial Catalog=MyDatabase;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
//Read the ConnectionStrings from the config file
TheConnectionString = ConfigurationManager.ConnectionStrings[ConfigurationManager.AppSettings.Get("MyAppConnectionString")].ConnectionString;
//Read the LINQ mapping and store it in memory
System.Data.Linq.Mapping.XmlMappingSource TheLinqMappings = System.Data.Linq.Mapping.XmlMappingSource.FromReader(XmlReader.Create(ConfigurationManager.AppSettings.Get("MyAppnLinqMappings")));
Next, feed the DataContext’s constructor :
TheDataClassesDataContext ctx = new TheDataClassesDataContext(TheConnectionString, TheLinqMappings)
That's it!