As part of trying to make my code work cross platform, I want to be able to use my existing LINQ to SQL code with other databases, ideally by changing only a connection string.

DbLinq is an open source implementation of LINQ to SQL that can connect to other databases. Being an open source product with a zero version number, it naturally required a little hacking to integrate it with Visual Studio.

DbLinq includes a command line tool, DbMetal, that will generate dbml files from a database or generate C# code from a dbml file. This is much the same as SqlMetal, the tool that comes with Visual Studio - the only real difference in the generated code is that the base class supports using multiple implementations of IDbConnection, so replacing the code generator used should enable cross platform databases without requiring too many code changes.

The dbml generated by Visual Studio doesn’t quite match the dbml expected by dbmetal, but this is mostly a matter of properties needing default values and easily fixed. I downloaded the source code and added some code to DbMetal\Generator\Implementation\Processor.cs in the load dbml section of ReadSchema.

            // add missing attributes so dbmetal will work with dbml from the visual studio designer
            if (string.IsNullOrEmpty(dbSchema.Provider)) dbSchema.Provider = "MySql";
            foreach (var t in dbSchema.Table)
            {
                if (string.IsNullOrEmpty(t.Name)) t.Name = t.Type.Name;
                if (string.IsNullOrEmpty(t.Name)) t.Name = t.Member;
                foreach (var c in t.Type.Columns) {
                    if (string.IsNullOrEmpty(c.Member)) c.Member = c.Name;
                }
            }

Rebuild DBMetal and the command line tool should be able to generate a suitable C# class from the dbml generated in Visual Studio.

The next step is to get it working in the IDE. Add LINQ to SQL classes to your project in the usual way. You can use server explorer to create the classes from an existing database, but be aware that this will add some server specific stuff (like “dbo.” on every table name) that may need to be removed later.

You will need to disable the default code generation. To do this, open the properties for the dbml file. Clear the field “Custom Tool”, which will be set to “MSLinqToSQLGenerator” by default. That will remove the designer.cs file.

The best way to integrate DbMetal would probably be to set up a custom tool so you can just change the value in the properties, but there is a much simpler method that is almost as good - adding an MSBuild task. Unload and edit the project. As the last child of the Project element add:

<target name="BeforeBuild">  
    <exec command="C:\Code\dblinq2007-read-only\build.dbg\dbmetal -code:DataClasses1.designer.cs DataClasses1.dbml">  
    </exec>  
</target>

Set the path to dbmetal and the file names appropriately. If you have multiple databases, just add additional Exec elements.

Reload the project and build it. This will create the designer.cs file, but will not add it to the project. You will need to turn on show all files and include it before everything will build without errors.

Using the generated classes is much the same as for standard LINQ to SQL. Since there is no default connection string, you will need to create data contexts using

var dc = new DataClasses1DataContext(connectionString);

To determine the correct connection type, DbLinq needs a couple of extra parameters on the connection string. Below are the connection strings I got working with the same code - additional provider valuse can be found in the DbMetal app.config.

<add name="SQLTestDB" connectionstring="Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=True;DbLinqProvider=SqlServer"></add>  
  
<add name="MySqlTestDB" connectionstring="Database=test;Data Source=servername;User Id=username;Password=password;DbLinqProvider=MySql;DbLinqConnectionType=MySql.Data.MySqlClient.MySqlConnection, MySql.Data"></add>