LINQ is one of the latest Microsoft technologies released under the .NET Framework 3.5. It brought native data querying capabilities to Visual C# and Basic. It incorporates seamlessly into the syntax of .NET languages. The entire architecture was designed specifically for the Microsoft SQL Server. In this article we are going to cover providers of LINQ to other databases such as MySQL, Oracle, PostgreSQL, and SQLite.
Before we begin, I’d recommend checking out my book review of “LINQ Quickly.” This book introduces the reader to the world of LINQ and leads through numerous ready-to-apply examples in the real world. The article also gives a sense of what LINQ is about. If you aren’t familiar with LINQ then don’t skimp on getting acquainted now.
Since this article presents a set of LINQ providers to other databases, knowing what the real LINQ is about and how to work with it (i.e. encapsulating data into objects and interpreting the results of queries stored in collections) is important> Otherwise, you can’t really appreciate the similarities of third party providers to other databases. Likewise, you won't notice the differences.
Many of the providers that we are going to cover are currently still under heavy development. Some are freely available, and even open source, where the community is actively looking for developers right now; others are only commercially available. Depending on your needs or your company’s needs, this article will try to give you a couple of the most popular and most proven possibilities. The decision is yours.
Meanwhile, this article will be followed by a sequel, which will present a basic applicable example of the freely available DbLinq provider, namely its LINQ-to-MySQL variation. That article will be published here as well. The programming language will be C# with the Visual Studio 2008 IDE. And the sample application will deal somewhat loosely with hotel management, exemplifying queries and commands.
This being said, we invite you to turn the page (by clicking the link below), and let’s begin.
It should be made clear that no provider will ever match the production quality of Microsoft's LINQ-to-SQL. Their official support for Microsoft SQL Server and Access is flawless, and right now all of the possible providers are either prototype software, in the alpha/beta stages, under development, or provide some sort of abstraction that acts a workaround to allow LINQ support for other databases, as well.
Therefore, don’t expect a perfect provider because there isn't one. It's as simple as that! Most of them are going to suffice for simple queries, basic bulk inserts, and some simple commands, while some functions would certainly be missing, others would appear to be broken...you name it. Our purpose is to play around with them and see how they work, what they are capable of, and how to implement them into our app.
If you want to take the officially-supported route and perhaps get the highest possible production quality level, then you should try the latest Connector/NET version of MySQL that’s available right at the moment. That is, if you want MySQL support for .NET languages. At the time of writing, there are no LINQ additions/patches included in the Connector. If you want to get into deeper solutions, then Entity Framework is for you.
The ADO.NET Entity Framework, developed by Microsoft, supports LINQ-to-Entities. Getting into this even deeper is beyond the article’s scope, but as you can already guess, this is a rock-solid way to work with Entities and Entities-over-WCF (Windows Communication Foundations), while using MySQL as a back-end. This is probably one of the most advanced ways to always being able to query the database with LINQ.
All right, now drifting back to our topic here—let’s talk about LINQ providers. First and foremost, let’s begin with DbLinq. It is open source and under development, but one of the most popular LINQ-to-other database providers. In short, it is an ORM (Object-Relational Mapping) tool and supports the most basic of LINQ queries and commands. At the moment their support extends to MySQL, Oracle, PostgreSQL, and SQLite.
We are going to focus a bit more on DbLinq on the next page, but right now let’s continue our presentation of available providers. Let’s introduce NHibernate. Probably you have already heard of Hibernate. It’s a high-performing, advanced, and reliable ORM for Java. The APIs offer object mapping between Java and database tables, and have support for XML, too. It’s open source and free. NHibernate is the same for .NET.
That’s right, Hibernate has been ported to the .NET languages as well, and that’s why the new framework is being called NHibernate. Native support for .NET is available, along with various mind-blowing functionalities. Right at the moment the latest version is 2.0, but 2.1 is supposed to be out soon. Once it’s out, it will shock the world; it is expected to include an LINQ provider in the framework. That would be amazing.
One of the main contributors to the NHibernate project, Ayende Rahien, has been talking about LINQ support of NHibernate since March 2007. Surely back then everything was uncertain and in the early stages of development, but even so, the examples he has shown on his website were “working code” and passed the tests. Therefore, it is highly likely that the promised NHibernate 2.1 will include a functional LINQ provider.
Moving on, let’s mention LightSpeed. Its name sounds fancy, right? No, we aren’t referring to c, which is the speed of light, whose value is almost 300M m/s. It is a product developed by Mindscape. It offers production quality level abstraction. Basically, LightSpeed is claimed to be blazing fast, and the best ORM framework available, with first-class LINQ support and full integration into Visual Studio 2008.
LightSpeed is a commercial product that requires licensing. However, for those who are working with relatively small databases, the Express Edition could suffice, and is freely available. It is limited to 8 model classes. The Standard License comes for $100 and covers up to 30 model classes, while the Professional version can be had for $300 and it has no limitations. The Enterprise version is $500 and includes the source code.
Now that we have presented a few solutions on the previous page, you can decide which route to follow. On this page we will focus only on DbLinq and explain the basic guidelines for how to work with it. Keep in mind that we will keep it simple, since the sequel to this article will entirely focus on showing a real-world applicable LINQ-to-MySQL implementation (application) based on DbLinq in VC# 3.5.
First and foremost, there are the following requirements for DbLinq. You need to have .NET Framework 3.5 installed. But this is self-explanatory since it comes with Visual Studio 2008. You are going to need this IDE to develop C# or VB applications using the DbLinq provider. However, .NET Framework 3.5 is required on execution of the compiled code as well. So the executable cannot be run without the framework.
Furthermore, you need to download the latest available release of DbLinq. The project is called dblinq2007 and it’s featured on Google Code. You can download the archived (.zip extension) version of each release. The source codes are available via the svn page (Subversion). Let’s assume you don’t want to mess with the source code right now, so you download one of the archived releases. Extract everything.
You will notice that there are numerous folders. Under the srcDbMetal folder you are going to find various similar batch files, such as run_myMetal.bat, run_oraMetal.bat, run_sqliteMetal.bat, run_ingMetal.bat, and so forth. These are the batch files, which are needed to be executed to create the appropriate C# files representing your database. This step is necessary and should be executed after the batch files are edited.
With MySQL, you can see that the run_myMetal.bat contains the following:
You either copy the batch file where DbMetal.exe is located (in the bin) folder and edit the script accordingly or execute the script from the main root folder of DbLinq. As soon as the script finishes, the C# file as requested will be generated. Now there’s one final step: copying the necessary files into your project. In the case of MySQL, these are the following:
DbLinq.MySql.dll
MySql.Data.dll
And the C# source file you generated with the DbMetal batch script.
That is all for now. On the next page you’ll read more about LINQ documentation.
Now that you have arrived to this page, you should be familiar with some LINQ providers and know which one you are going to use. Chances are, if you are like most people you will stick with DbLinq, even if it’s in the development stage and has some limitations. It should suffice right as it is for your everyday development needs, and you can incorporate workarounds to live with.
At the beginning of this article we mentioned that it would be followed by another part, a sort of sequel, covering an example program of a real-world application. We will help you build the application by giving step-by-step tips and lead you through the entire process. The only requirements are having a MySQL database up and running, and perhaps having some database with tables ready.
Since LINQ providers are closely sticking to the official LINQ syntax and workings, it is really critical to invest some time in learning how to work appropriately with it. Your number one resource should be the Microsoft LINQ Project website, these 101 LINQ Examples (in C#, but also in VB), and a collection of available LINQ books. And yes, we can’t forget out own articles published on ASP Free. There are lots of them!
And finally, we can’t really finish without inviting you to join our helpful community at Dev Hardware Forums. We’ve a strong base of resident professionals, enthusiasts, and tech experts. If you want to hear opinions on some service or ask some clarifications regarding some details just shoot us your questions. We’ll do our best to help. And you should also want to pay a visit to the forums of our sister-site at Dev Shed Forums.
MS SQL Server 
/ 10 
LINQ-to-MySQL with DbLinq in C#
