 |
Platforms, Frameworks & Libraries »
LINQ »
General
Intermediate
License: The GNU Lesser General Public License
LinqToSQL: Comprehensive Support for SQLite, Microsoft Access, SQServer2000/2005By JahmaniUse LinqToSql to query these popular RDMS products |
C#, SQL, Windows, .NET, SQL Server, ADO.NET, LINQ, Dev
|
|
Advanced Search Add to IE Search |
|
|
|
||||||||||||||||
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:
String, DateTime and Nullable`1 classes that have SQL equivalents e.g. firstName.Length, firstName.ToUpper(), orderDate.Year, shippedDate.HasValue etc. IQueryable methods e.g. GroupBy, Any, All, First, Sum, Average etc. 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.
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.
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.
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:
The following query will provide a list of customers who have placed orders that have all been shipped to the customers city.
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:
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
------------------------------------------------------------
The following query will provide a list of customers who have placed no orders.
from customer in customers
where !customer.Orders.Any()
select new { customer.CustomerID, customer.ContactName };
This will produce the following SQL statement:
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
------------------------------------------------------------
The following query will provide a list of customers and employees who live in London:
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:
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
------------------------------------------------------------
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...
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:
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:
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!
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 12 Feb 2008 Editor: Deeksha Shenoy |
Copyright 2008 by Jahmani Everything else Copyright © CodeProject, 1999-2009 Web17 | Advertise on the Code Project |
I'll be posting updated sources within the next 48 hours. 
I'm a bit tied up right now. 
.