Linq2Sql: Changing the Database Schema at Runtime (without XMLs)

Our company has too many databases. Not only we have a database server for development, another ~5 server per version for QA/integration, we also have a production database server for each country we deal with.

I don’t really know why but in our development server we have a database per country (which is valid) with different database schemas: BOB for our regular schema and BOB_JPN for japan. In our DAL (Data Access Layer) code out code that uses IWorkspace takes the DB Schema from an application config file but we also have code that uses Linq2Sql (since it is faster) that has the DB Schema hard coded in the designer.cs code:

1. [global::System.Data.Linq.Mapping.TableAttribute(Name="BOB.SOME_TABLE")]
2. public partial class SOME_TABLE
3. {
4.     
5.     private int _OBJECTID;
6.     
7.     private short _TypeId;

Until today we changed the designer code by deleting the schema and then the query is done with the user default schema, which in japan is BOB_JPN. My team leader has done this “simply” by changing the code in the designer.cs file.

 

I decided I can’t allow this to continue. I had two options:

1. Change the DB Schema in Linq2Sql
2. Writing the entity code by hand – without the DB Schema
3. Using some other technology (such as ADO.Net) instead of Linq2Sql
4. Use my team leader way and change the designer code

I of course preferred using Linq2Sql, simply because the code works and we are going to production soon.

My first Google search “linq2sql config db schema” was a bust .

My second search “change linq mapping in runtime” found this:

External Mapping Reference (LINQ to SQL) – using external XML files, nothing on runtime changes

LINQ to SQL - Tailoring the Mapping at Runtime – again too complicated, it was something like build the xml in runtime and tailor it in…

On my third search I decided to think outside the box (actually I decided to run away from the box): “reflection change attribute at runtime”

Change Attribute's parameter at runtime

My first trial was with the code that was marked as not working (I hoped the bug was fixed since 2008):

1. private void ChangeSchema()
2. {
3.     if (DefaultGisConfigSection.Instance.SchemaName.ToUpper().CompareTo("BOB") == 0)
4.         return;
5.  
6.     ChangeTableAttribute(typeof (STREET));
7. }
8.  
9. private void ChangeTableAttribute(Type table)
10. {
11.     var tableAttributes = (TableAttribute[])
12.                           table.GetCustomAttributes(typeof (TableAttribute), false);
13.     tableAttributes[0].Name = DefaultGisConfigSection.Instance.SchemaName + "." + TableName;
14. }

Didn’t work.

My second trial didn’t work either:

1. private void ChangeTableAttribute(Type table)
2. {
3.     TypeDescriptor.AddAttributes(table,
4.                                  new TableAttribute
5.                                      {Name = DefaultGisConfigSection.Instance.SchemaName + "." + TableName});
6. }

But I think this time it’s more my reflection code than anything else. The Exception in both cases was:

Test method Shepherd.Core.Dal.Tests.SomeTest threw exception:
System.Data.SqlClient.SqlException: Invalid object name 'BOB.SOME_TABLE'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList(IEnumerable`1 source)

For the last try before yelling quits I decided to try and see what Microsoft is doing behind the scenes – I activated the Debug Symbols option (this is something I don’t encourage anyone to try because the last time I did this with the ESRI symbols it just didn’t remove itself for several days and the performance was bad!).

Going over the code I reached this method in C:\Projects\SymbolCache\src\source\.NET\4\DEVDIV_TFS\Dev10\Releases\RTMRel\ndp\fx\src\DLinq\Dlinq\Mapping\AttributedMetaModel.cs\1599186\AttributedMetaModel.cs:

1. internal MetaTable GetTableNoLocks(Type rowType) {

It did:

1. TableAttribute[] attrs = (TableAttribute[])root.GetCustomAttributes(typeof(TableAttribute), true);

And got the original table name – Now I know what must be changed!

But I can’t since that was my first trial (going into the framework code here just got me into more trouble, at the end of a very long (~200 lines) method:

1. [System.Security.SecurityCritical]  // auto-generated
2. private unsafe static object[] GetCustomAttributes(
3.     RuntimeModule decoratedModule, int decoratedMetadataToken, int pcaCount,
4.     RuntimeType attributeFilterType, bool mustBeInheritable, IList derivedAttributes, bool isDecoratedTargetSecurityTransparent)

in C:\Projects\SymbolCache\src\source\.NET\4\DEVDIV_TFS\Dev10\Releases\RTMRel\ndp\clr\src\BCL\System\Reflection\CustomAttribute.cs\1305376\CustomAttribute.cs I found that the attribute is being built by:

 

Going into that got me this:

in text:

---------------------------
Microsoft Visual Studio
---------------------------
File Load

Some bytes have been replaced with the Unicode substitution character while loading file C:\Projects\SymbolCache\src\source\.NET\4\DEVDIV_TFS\Dev10\Releases\RTMRel\ndp\fx\src\DLinq\Dlinq\Mapping\Attributes.cs\1305376\Attributes.cs with Unicode (UTF-8) encoding. Saving the file will not preserve the original file contents.
---------------------------
OK  
---------------------------

The result is an empty TableAttribute, the value for the attribute comes from an unsafe method:

1. [System.Security.SecurityCritical]  // auto-generated
2. [ResourceExposure(ResourceScope.None)]
3. [MethodImplAttribute(MethodImplOptions.InternalCall)]
4. private unsafe extern static void _GetPropertyOrFieldData(
5.     RuntimeModule pModule, byte** ppBlobStart, byte* pBlobEnd, out string name, out bool bIsProperty, out RuntimeType type, out object value);

I give up, I should have given up when Google failed me but… I have decided to post it as a question stackoverflow:

Modifying Class Attribute on Runtime

 

I next decided to change the attribute by inheritance:

1. [TableAttribute(Name = "SOME_TABLE")]
2. public class SomeTable:SOME_TABLE

That got me the Exception:

System.InvalidOperationException: Data member 'Int32 OBJECTID' of type 'Project.Dal.SOME_TABLE' is not part of the mapping for type 'SomeTable'. Is the member above the root of an inheritance hierarchy?

Basically that is not possible because of a limitation in Linq2Sql this (damn!).

 

At the end I have chosen option 2, writing the code by hand. But I learned that going into .Net inner code is a trial of sanity, you either find what you are looking for and lose your sanity or you give up…

 

//TODO: Missing some code in the middle, given the previous paragraph do I really want to look for it?!?

 

Keywords: Linq2Sql, reflection, DB Schema

IceRocket Tags: Linq2Sql,reflection,DB Schema