Introduction

The objective of this article is to demonstrate functionality in the LinqToSql library that transforms LINQ expression trees to SQL statements that can be executed against multiple RDMS systems and not just Microsoft's SQL Server offerings. The LinqToSql library implements the following features and functionality:

In this article, I will focus on using SQLite and set operator functionality such as Any, All, Union etc.

Introduction to SQLite

According to the blurb on the site:

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. It is used in countless desktop computer applications as well as consumer electronic devices including cellphones, PDAs, and MP3 players. The source code for SQLite is in the public domain.

Wikipedia states:

SQLite is known to be embedded in:

Adobe Air, a cross-OS runtime environment for building applications that can be deployed to the desktop.

Mozilla Firefox, the leading open-source Web browser, for some databases in the user profile.

Ruby on Rails, default database in Ruby on Rails 2.0 release.

Android, the mobile phone development kit created by Google, to store user data.

Mac OS X, starting with version 10.4 (Tiger), as a persistence layer of the Core Data API

musikCube, a low weight music player, for querying the media library in dynamic playlists.

In my (brief) experience with SQLite, I have found it extremely easy to set up and use and it is therefore the first non-Microsoft RDMS against which LinqToSql will operate.

Note

SQLite is a C application and you will need to download an ADO.NET wrapper around the core SQLite library. SQLite.NET is an excellent choice and is used here. You may also want to download the SQLite Database browser which provides a GUI for SQLite.

Setting Up SQLite

Once you've downloaded and installed the above you'll be ready to run the examples in the download as the following have already been done:

Northwind has been converted to an SQLite Database and placed in the \bin directory of the project.

The provider factory and connection string properties for SQLite have been defined.

Set Operators

ALL

The following query will provide a list of customers who have placed orders that have all been shipped to the customers city.

That's quite a mouthful, but what we are saying essentially is that we want only those customers who have no orders that were shipped to a city other than the customer's i.e. the contrapositive of the All criteria.

Point of Interest

Whereas core functionality in RDMSs exposed through SQL tends to be very similar from database to database, more advanced functionality is often accessed in very different ways depending on which product you use.

The mechanism by which LinqToSql mediates these differences and how you can extend it to produce correct SQL syntax for the RDMS of your choice will be the subject of the next article. I shall also cover mapping of user defined scalar functions to stored procedures / ad-hoc SQL.

That's it for now. Cheers!

Notes

All 60 or so samples in the download will run successfully on SQLite, Microsoft Access and SQL Server with a few exceptions.

MARS is not used but multiple simultaneous connections may be opened during query evaluation. I will detail when and why this happens, performance implications and possible workarounds in another article.

A comprehensive code review is underway and a number of bugs have been fixed.