Click here to Skip to main content
15,867,328 members
Articles / Hosted Services / Serverless
Article

LinqToSQL: Comprehensive Support for SQLite, Microsoft Access, SQServer2000/2005

Rate me:
Please Sign up or sign in to vote.
4.97/5 (19 votes)
12 Feb 2008LGPL35 min read 175.8K   2.7K   111   36
Use LinqToSql to query these popular RDMS products

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:

  1. Comprehensive support for SQLite, SQLServer 2000/2005, Microsoft Access, MySQL(under testing)
  2. Transparently query multiple databases in one expression e.g. a Microsoft Access Database and an SQL Server Database
  3. Translates function calls and property accessors in the String, DateTime and Nullable`1 classes that have SQL equivalents e.g. firstName.Length, firstName.ToUpper(), orderDate.Year, shippedDate.HasValue etc.
  4. Implements all IQueryable methods e.g. GroupBy, Any, All, First, Sum, Average etc.
  5. Correctly and comprehensively translates binary and unary expressions that have valid translations into SQL
  6. Parameterizes queries instead of embedding constants in the SQL transformation
  7. Performs caching of previously translated expression trees
  8. Does not use MARS - Multiple Active Result Sets, an SQL Server 2005 specific feature
  9. Correctly translates calls to SelectMany even when the query sources involve method calls. The SQL Server 2005 specific keyword CROSS APPLY is neither required nor used.

The project file available above for download contains samples that run against the famous Northwind database on SQLite, SQL Server and Microsoft Access.

For implementation details, please see the following articles 1, 2, 3.

A previous article on the usage of LinqToSql can be found here.

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.
  • Google Gears, providing local DB facilities to JavaScript apps.
  • Adobe Photoshop Lightroom, photography management and post-production software, for its photo catalogs.
  • Mozy, online backup software, to store configuration data, backup sets, data state, etc.
  • Avira Antivir - Antivirus software

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:

  1. Northwind has been converted to an SQLite Database and placed in the \bin directory of the project.
  2. 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.

SQL
from c in customers
where (from o in c.Orders
       select o).All(o => o.ShipCity == c.City)
select new { c.CustomerID, c.ContactName };

This will produce the following SQL statement:

SQL
SELECT  t0.CustomerID, t0.ContactName
FROM Customers AS t0
WHERE 
(    SELECT  COUNT(*) 
    FROM Orders AS t1
    WHERE ((t1.CustomerID = t0.CustomerID) AND  NOT  ((t1.ShipCity = t0.City)))
) = 0

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.

The query will produce the following results:

CustomerID=ALFKI        ContactName=Maria Anders
CustomerID=ANATR        ContactName=Ana Trujillo
CustomerID=ANTON        ContactName=Antonio Moreno
CustomerID=BERGS        ContactName=Christina Berglund
CustomerID=BLAUS        ContactName=Hanna Moos
CustomerID=BLONP        ContactName=Frédérique Citeaux
------------------------------------------------------------

ANY

The following query will provide a list of customers who have placed no orders.

SQL
from customer in customers
where !customer.Orders.Any()
select new { customer.CustomerID, customer.ContactName };

This will produce the following SQL statement:

SQL
SELECT  t0.CustomerID, t0.ContactName
FROM Customers AS t0
WHERE  NOT  (
(    SELECT  COUNT(*) 
    FROM Orders AS t1
    WHERE (t1.CustomerID = t0.CustomerID)
) > 0
)

Here, we once again translate the contrapositive of the Any criteria.

The query will produce the following results:

CustomerID=FISSA        ContactName=Diego Roel
CustomerID=PARIS        ContactName=Marie Bertrand
------------------------------------------------------------

UNION

The following query will provide a list of customers and employees who live in London:

SQL
       from c in customers.Where(d => d.City == "London")
       select new { ContactName = c.ContactName })
.Union(from e in employees.Where(f => f.City == "London")
       select new { ContactName = e.LastName })

This will produce the following SQL statement:

SQL
        SELECT  t2.ContactName
        FROM Customers AS t2
        WHERE (t2.City = @p0)

UNION
        SELECT  t2.LastName
        FROM Employees AS t2
        WHERE (t2.City = @p1)

The query will produce the following results:

ContactName=Ann Devon
ContactName=Buchanan
ContactName=Dodsworth
ContactName=Elizabeth Brown
ContactName=Hari Kumar
ContactName=King
------------------------------------------------------------

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.

For example, the following query...

SQL
from order in orders
where order.OrderDate.Value.Year > DateTime.Parse("1/1/1997").Year &&
      order.CustomerID.StartsWith("B")
select new { order.CustomerID, order.OrderID, order.OrderDate };

... will translate to the following statement for SQL server:

SQL
SELECT  t0.CustomerID, t0.OrderID, t0.OrderDate
FROM Orders AS t0
WHERE ((datePart(yyyy, t0.OrderDate) > @p1) AND t0.CustomerID Like (@p0 + '%'))

On SQLite however, the translation will be:

SQL
SELECT  t0.CustomerID, t0.OrderID, t0.OrderDate
FROM Orders AS t0
WHERE ((round(strftime('%Y', t0.OrderDate)) > @p1) _
    AND Like (@p0 || '%', t0.CustomerID))

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

  1. All 60 or so samples in the download will run successfully on SQLite, Microsoft Access and SQL Server with a few exceptions.
  2. 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.
  3. A comprehensive code review is underway and a number of bugs have been fixed.
  4. Comments, suggestions and bug reports would be most appreciated.

History

  • 13th February, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)


Written By
Technical Lead Olivine Technology
Kenya Kenya
Technical Lead, Olivine Technology - Nairobi, Kenya.

"The bane of productivity: confusing the rituals of work (sitting at your desk by 8:00am, wearing a clean and well pressed business costume etc.) with actual work that produces results."

Watch me!

Comments and Discussions

 
QuestionUSeful Information Pin
James Oloo26-Feb-12 5:45
James Oloo26-Feb-12 5:45 
QuestionADO.NET provider for SQLite - linq not supported? Pin
Prathapachandran23-Nov-11 0:49
professionalPrathapachandran23-Nov-11 0:49 
QuestionGood job,go on please! Pin
sly19841-Sep-11 8:41
sly19841-Sep-11 8:41 
AnswerRe: Good job,go on please! Pin
Muigai Mwaura1-Sep-11 22:36
Muigai Mwaura1-Sep-11 22:36 
GeneralRe: Good job,go on please! Pin
sly19842-Sep-11 8:23
sly19842-Sep-11 8:23 
GeneralMy vote of 5 Pin
arisoqq8-Jul-11 4:56
arisoqq8-Jul-11 4:56 
General.Count() does not working Pin
Alex Onezashvili21-Jul-09 15:30
Alex Onezashvili21-Jul-09 15:30 
GeneralRe: .Count() does not working Pin
Muigai Mwaura22-Jul-09 4:35
Muigai Mwaura22-Jul-09 4:35 
GeneralUse ALinq to Linq other database Pin
ansiboy14-Jul-09 1:54
ansiboy14-Jul-09 1:54 
QuestionWow - Nice Work Pin
msweeney31-Jul-08 6:57
msweeney31-Jul-08 6:57 
GeneralCant Compile Pin
UnRusoDeCaracas1-May-08 19:42
UnRusoDeCaracas1-May-08 19:42 
GeneralRe: Cant Compile Pin
Muigai Mwaura6-May-08 4:02
Muigai Mwaura6-May-08 4:02 
GeneralRe: Cant Compile Pin
enerata25-May-08 4:19
enerata25-May-08 4:19 
GeneralRe: Cant Compile Pin
Muigai Mwaura27-May-08 5:08
Muigai Mwaura27-May-08 5:08 
AnswerRe: Cant Compile Pin
MeNot13-Jun-08 5:33
MeNot13-Jun-08 5:33 
GeneralConverting SQL Server and Excel to SQLite Pin
alias4723-Apr-08 13:53
alias4723-Apr-08 13:53 
GeneralRe: Converting SQL Server and Excel to SQLite Pin
Muigai Mwaura24-Apr-08 2:16
Muigai Mwaura24-Apr-08 2:16 
GeneralRe: Converting SQL Server and Excel to SQLite Pin
ldaniel12-Sep-08 4:57
ldaniel12-Sep-08 4:57 
QuestionQ: can same result be achieved in Linq Preview? Pin
jesuscheung23-Apr-08 5:44
jesuscheung23-Apr-08 5:44 
AnswerRe: Q: can same result be achieved in Linq Preview? Pin
Muigai Mwaura24-Apr-08 2:22
Muigai Mwaura24-Apr-08 2:22 
GeneralUsing Access with out of the box LINQ to SQL Pin
Ashley van Gerven23-Apr-08 3:35
Ashley van Gerven23-Apr-08 3:35 
QuestionWill UPDATE, INSERT, DELETE queries work? Pin
Ashley van Gerven19-Apr-08 22:25
Ashley van Gerven19-Apr-08 22:25 
Hi,

Just wondering if changes to the DB when calling SubmitChanges() will work with your library?

Great effort BTW - looks like a fair bit of research went it that.

cheers
Ashley

"For fifty bucks I'd put my face in their soup and blow." - George Costanza

CP article: SmartPager - a Flickr-style pager control with go-to-page popup layer.

AnswerRe: Will UPDATE, INSERT, DELETE queries work? Pin
Muigai Mwaura24-Apr-08 2:12
Muigai Mwaura24-Apr-08 2:12 
GeneralExtremely Good Work!!! Pin
Kavan Shaban3-Apr-08 13:18
Kavan Shaban3-Apr-08 13:18 
GeneralA Quick Question Pin
Kavan Shaban3-Apr-08 17:09
Kavan Shaban3-Apr-08 17:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.