LINQ To MySQL

LINQ to SQL is a great tool that saves a lot of time. However it has one disadvantage – it supports only SQL Server. Anyway it’s possible to write your own LINQ provider. Sure, there is one for MySQL. It is called DbLinq, in fact in supports many databases:

• MySQL
• Oracle
• PostreSql
• Ingress
• SqlLite

It’s not finished yet, however you can still try to use it. I’m going to explain how to use it in this article; we’ll go through 3 steps:

1. Installation
2. Using DbLinq in a console application
3. Using DbLinq in a ASP.NET application

Installation

1. Go here and download the ZIP archieve
2. Unpack the archive
3. Open the Visual Studio solution - DbLinq
4. Build the solution

Using DbLinq in a console application

Ok, let’s try it in action, we are going to create a console application that will retrieve, insert, update and delete records from the database.

First, we need to generate the data context and the table classes. Unfortunately you cannot do this in Visual Studio, but there is a command line utility called DbMetal and its visual analogue – Visual Metal that is written in WPF, by the way.

Go to the \dblinq2007\DbMetal\ folder, there are several BAT files, open in your favourite text editor run_myMetal.bat, since we are dealing with MySQL, and change the necessary parameters, save and run it. You must have got a generated CS file, copy it to your console application project.
Then, it’s time to add the references, you should add the following:

• System.Data.Linq
• DbLinq.dll (located in \dblinq2007\DbLinq.MySql\bin\release\)
• DbLinq.MySql.dll (located in \dblinq2007\DbLinq.MySql\bin\release\)
• MySql.Data.dll (located in \dblinq2007\lib\)

Since the table is called Products, I want the class that represents a stored entity to be called Product, not Products, as it was called by DbMetal. So, I change the generated code.

Compile the project to check if there are no errors. If you get any error, check if there are all the necessary references.

Let’s write some code; basically we want just to display the data:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient; //Don't forget to add this line

namespace MySqlLinqTest
{
    class Program
    {
        static void Main(string[] args)
        {
            LinqTest db = new LinqTest(new MySqlConnection("Database=<your database>;Data Source=localhost;User Id=<user>;Password=<password>"));

            var products = from p in db.Products
                           where p.Category == "Sport cars"
                           select p;

            foreach (var product in products)
            {
                Console.WriteLine(product.Name);
            }
        }
    }
}

That works. What about inserting a new record? No pro blems!

db.Products.InsertOnSubmit(new Product { Name = "Some car", Category = "Some category" });
db.SubmitC hanges();

var products = from p in db.Products
               orderby p.Name
               select p; 

foreach (var product in products)
{
    Console.WriteLine(product.Name);
}

Ok, let’s remove the newly added record:

Product someCar = (from p in db.Products
                      where p.Name == "Some car"
                      select p).First();

db.Products.DeleteOnSubmit(someCar);
db.SubmitChanges();

Let’s update something:

Product bentley = (from p in db.Products
                   where p.Name == "Bentley"
                   select p).First();

bentley.Name = "Bentley Continental";
db.SubmitChanges();

Using DbLinq in an ASP.NET application

It was funny enough, but the console applications are not very useful when dealing with the database, let’s write something that is closer to a real world – an ASP.NET application.

Create a C# web site; copy the generated file we used in the previous example.

Don’t forget to add the necessary references.

We are going to add a GridView and bind the data retrieved from the database.

Default.aspx

<asp:GridView ID="gridProducts" runat="server">
</asp:GridView>

Default.aspx.cs 

protected void Page_Load(object sender, EventArgs e)
{
    LinqTest db = new LinqTest(new MySqlConnection("Database=<your database>;Data Source=localhost;User Id=<user>;Password=<password>"));

    gridProducts.DataSource = from p in db.Products
                              orderby p.Name
                              select p;
    gridProducts.DataBind();
}

It works.

What about LinqDataSource, can we use it with DbLinq? Yes, we can, but it’s a bit tricky. If we add a LinqDataSource, we’ll get an error saying that there’s no parametless constructor in our DataContext. That is a thing that hopefully will be fixed in the new versions, but now we have to write a wrapper around a generated DataContext.

App_Code/Products.cs

public class MyDataContext
{
    private LinqTest _db;

    public MyDataContext()
    {
        _db = new LinqTest(new MySqlConnection("Database=<your database>;Data Source=localhost;User Id=<user>;Password=<password>"));
    }

    public Table<Product> Products { get { return _db.GetTable<Product>(); } }
}

Then, we can use LinqDataSource as usual:

<asp:LinqDataSource ID="dsProducts" runat="server" ContextTypeName="MysqlLinqTest.MyDataContext"     TableName="Products"></asp:LinqDataSource> <asp:GridView ID="gridProducts" runat="server" DataSourceID="dsProducts">  </asp:GridView>

Conclusion

As you see you can use LINQ to work with MySQL, unfortunately sometimes it’s not very convenient and we have to wait for the new versions or wait for a LINQ to MySQL provider from MySQL.