Getting Linq to play nice with Mysql and Mono, is it possible?

Question

I've been trying to get Linq to work with Mysql on Ubuntu, using the Mono version in the repositories (2.10.5) and I'm getting nothing but headaches.

First of all, I had to modify the MySQL provider sqlmetal.exe.config because it was set to an old and deprecated version by default, I managed to get it to work setting the MySQL connector I have installed on this machine (from the repositories) as the provider for sqlmetal. I generated the DataContext with sqlmetal for my target database, and that seemed to work, but I'm not completely sure.

After generating the DataContext I created a new project on monodevelop just to test this,at first I tried using a simple MysqlConenction and check the connector was working properly, I had to addd the connector assembly to the project, but that's fine, it worked. Then I tried to connect using the DataContext using this code:

    using System;
    using System.Linq;
    using System.Data.Linq;
    using MySql.Data.MySqlClient;

    namespace test
    {
            public class test
        {
            public static void Main (String[] args)
            {           
                Test db = new Test (new MySqlConnection("Userid=root;database=test;server=localhost;password=password"));

                foreach(var tr in db.Users)
                {
                    Console.Write(tr.Username);
                }
            }
        }
    }

This code fails. It seems Linq is generating bad SQL code for MySQL, at least the exception it's throwing seems to say that:

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Id], [Password], [Username]
FROM [test].[Users]' at line 1
  at MySql.Data.MySqlClient.MySqlStream.ReadPacket () [0x00000] in <filename unknown>:0 
  at MySql.Data.MySqlClient.NativeDriver.GetResult (System.Int32& affectedRow, System.Int32& insertedId) [0x00000] in <filename unknown>:0 
[ERROR] FATAL UNHANDLED EXCEPTION: MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Id], [Password], [Username]
FROM [test].[Users]' at line 1
  at MySql.Data.MySqlClient.MySqlStream.ReadPacket () [0x00000] in <filename unknown>:0 
  at MySql.Data.MySqlClient.NativeDriver.GetResult (System.Int32& affectedRow, System.Int32& insertedId) [0x00000] in <filename unknown>:0 

Does anyone know if it is possible to connect to MySQL using Linq and Mono? Is there something I'm missing?

Thanks!

Answer 1

I don't know why everybody seems to think that LINQ to SQL is not working on Mono.
Mono evolves regularly: as of today, LINQ to SQL works fine with Mono and MySQL (and I use it successfully with PostgreSQL).

There is a little error in your code: you don't specify the DbLinqProvider in the connection string. This is required in order to produce SQL code targeted at MySQL.

In this case, the generated SQL code is specific to SQL Server :

"FROM [test].[Users]"

should have been

"FROM `test`.`Users`"

Try to add "DbLinqProvider=MySql" in your connection string. This should fix your problem.
See : http://www.mono-project.com/Release_Notes_Mono_2.6#LINQ_to_SQL

You should be aware that the Mono/DbLinq implementation is still incomplete (for example, compiled queries aren't supported), but more than enough to develop a complete application.