LINQ with MySQL

LINQ to SQL from Microsoft supports only their SQL Server. How about other popular DBMS such as MySQL ?

I investigate the first result returned from Google using "LINQ with MySQL" query: DbLinq. It's an opensource project which provides "LINQ to SQL" for other databases include MySQL, PostgreSQL, Oracle and SQLite.

However, the project is still at its very beginning stage and I think it will take some time before we can use it in production environment, IMHO.

Here is my setup of DbLinq with MySQL

• Visual Studio 2008 Professional
• MySQL 5
• DbLinq 0.18
• Windows Vista

1. I first have my database prepared, its name is "Items".

2. Run DbMetal which is a tool comes with DbLinq in "build" folder. I use the following command for my MySQL server. I have user root with no password.

C:\Downloads\DbLinq-0.18\DbLinq-0.18\build>DbMetal /server:127.0.0.1 /database:items /provider:MySQL /user:root /code:Items.cs
DbLinq Database mapping generator 2008 version 0.18.0.0
for Microsoft (R) .NET Framework version 3.5
Distributed under the MIT licence (http://linq.to/db/license)

>>> Reading schema from MySQL database
<<<>

3. You will have a C# code file, Items.cs in my case, automatically generated. This file contains C# classes generated from database schema and the mapping attributes.

4. You may want to compile the project now. I found some errors in my cases:

• There is no type specified for fields of MySQL's Enum type. You can fix this easily by insert C#'s "string" type. In my case: private _gender; to private string _gender;
• EntitySet cannot be resolved. Change every occurances to System.Data.Linq.EntitySet instead.

If you can compile your project, you can proceed to the next step.

5. Here is my code for querying from MySQL database. From my setup, it's an ASP.NET web application with a single GridView on the Default.aspx page.



6. Run and see the result : )

ASP.NET Provider Model Support in MySQL

After a long googling, I found that ASP.NET Membership Provider seems to be a good architectural choice for my project at the faculty. Microsoft prepared the providers for SQL Server out of the box. But I need my data store to be MySQL server. So something is needed to be done.

I first downloaded and installed MySQL Connector/Net 5.2.2. The installer also configured the machine.config file on my system too.

After that, I configured my project's web.config file to override the default connection string.

<connectionStrings> <remove name="LocalMySqlServer"/> <add
name="LocalMySqlServer"
connectionString="Datasource=localhost;Database=mydb;uid=root;pwd=root;"
providerName="MySql.Data.MySqlClient"/> </connectionStrings>

Then, I loaded the ASP.NET Web Site Administration Tool from Visual Studio 2008 and click on the Provider Configuration / Select a different provider for each feature (advanced) links to config MySQL providers with my project.

After the providers are configured, I went to the Security page but it provide me with error message.

Unable to initialize provider. Missing or incorrect schema.

I found out later that I need to create the database before MySQL connector can generated the schema. This is easily done by using MySQL's create database mydb command. Everything works fine in the end. All tables are generated.

mysql> show tables
-> ;
+-------------------------+
| Tables_in_mydb |
+-------------------------+
| my_aspnet_applications |
| my_aspnet_membership |
| my_aspnet_profiles |
| my_aspnet_roles |
| my_aspnet_schemaversion |
| my_aspnet_users |
| my_aspnet_usersinroles |
+-------------------------+
7 rows in set (0.00 sec)

Hope this helps people who are struggling with this problem :)

Other helpful blog on the topic:

• http://blog.nolanbailey.com/2008/03/08/mysql-aspnet-membership-and-role-provider/194