How to use MySql with Linq to SQL

The vs2008 and vs2010 don't support the generation LINQ to SQL business objects from a MySQL database, if you drop a MySql table to a Linq to Sql Class, it will popup a "The selected object(s) use an unsupported data provider" error.



Generation tool DBLinq

DbLinq is a LINQ to SQL data context provider and allows you to create LINQ to SQL business objects from a MySQL database and perform LINQ queries directly against MySQL tables. Utilizing LINQ, it functions in the same way as a typical SQL Server data layer.

a) Download DBLinq from: http://code.google.com/p/dblinq2007/downloads/list

b) Run the LINQ to SQL generation tool DbMetal.exe as follows:
DbMetal.exe -provider=MySql -database:MyDatebase -server:you host computer -user:mysql user -password:you pwd -namespace:mysqllinq -code:mysqllinq.cs -sprocs



Modify the generated file

The generated file can't be use in project right now, we must take some modification on it.

a) Delect all code with #if !MONO_STRICT(take MONO_STRICT as True is ok).

b) Delete the constructors from the DataContext class and add the following constructor:

        public MyDBDataContext()
            : base(new MySqlConnection(ConfigurationManager.ConnectionStrings["MyDb"].ConnectionString))
        {
        OnCreated();
        }

c) For "Incorrect AutoSync specification for member" error, we should set AutoSync of pk field as AutoSync.OnInsert, the full configuration for pk field:
[Column(Storage = "_userID", Name = "userId", DbType = "int", IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, CanBeNull = false)]

As the .NET compiler will convert the code into a SQL Server syntax statement, run this code under MySql will throw error(Like this issue:http://lists.mysql.com/mysql/215385)

d) Change inheritance of class from System.Data.Linq.DataContext to DbLinq.MySql.MySqlDataContext(the DbLinq.dll & DbLinq.MySql.dll can be found in DBLinq)

e) Change the  public Table<Table1> Table1 { get { return GetTable<Table1>(); } } to public DbLinq.Data.Linq.Table<Table1> Table1 { get { return GetTable<Table1>(); } }


That is it, you can use MySql with Linq to SQL.