Introduction
The v3.5 release of the .NET Framework includes a significant number of new and enhanced technologies. LINQ (Language Integrated Query) is in my opinion the most significant new technology in the v3.5 release. Microsoft has implemented a set of libraries to transform LINQ expression trees to SQL statements and dubbed these DLINQ. DLINQ is a very impressive piece of work, but unfortunately it is available only for SQL Server 2000 and 2005.
Background
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:
- Correctly and comprehensively translates binary and unary expressions that have valid translations into SQL
- Translates function calls (e.g.
customer.FirstName.ToUpper()) that have SQL equivalents - Implements all
IQueryable methods e.g. GroupBy, Any, All, Sum, Average etc. - Parameterizes queries instead of embedding constants in the SQL transformation
- Performs caching of previously translated expression trees
- Does not use MARS - Multiple Active Result Sets, an SQL Server 2005 specific feature
So far I have tested the functionality on SQL Server 2000 and 2005. Testing for compatibility with Microsoft Access and other RDMS's is underway as the project is still under active development.
The project file available above for download contains samples that run against the famous Northwind database on both SQL Server 2005 and Microsoft Access.
For implementation details, please see the following articles:
In this article, I will focus on Grouping and Aggregation functionality.
Samples and Translations
Example 1
var city = "London";
var country = "Brazil";
var x = customers.Select(c => new
{
Name = c.ContactName,
Location = new
{
City = c.City,
Country = c.Country,
Revenues = new
{
TotalRevenue = (from o in orders
where o.CustomerID == c.CustomerID
select o.OrderDetails.Sum(od => _
od.UnitPrice * od.Quantity)).Sum(),
AverageRevenue = (from o in orders
where o.CustomerID == c.CustomerID
select o.OrderDetails.Average(od => _
od.UnitPrice * od.Quantity)).Average()
}
}
})
.Where(v => v.Location.City == city || v.Location.Country == country)
.OrderBy(w => w.Name);
ObjectDumper.Write(x, 3);
The above query will produce a list of customers who are located in London or Brazil and the total and average revenues realised from their orders. The following SQL queries will be generated to produce the result.
SELECT t0.ContactName, t0.City, t0.Country, t0.CustomerID
FROM Customers AS t0
WHERE ((t0.City = @p1) OR (t0.Country = @p0))
ORDER BY t0.ContactName
// Customer Details
SELECT
(
SELECT Sum((t2.UnitPrice * t2.Quantity))
FROM [OrderDetails] AS t2
WHERE (t2.OrderID = t0.OrderID)
)
FROM Orders AS t0
WHERE (t0.CustomerID = @p0)
// Total Revenue
SELECT
(
SELECT Avg((t2.UnitPrice * t2.Quantity))
FROM [OrderDetails] AS t2
WHERE (t2.OrderID = t0.OrderID)
)
FROM Orders AS t0
WHERE (t0.CustomerID = @p0)
// Average Revenue
The results will look like so:
Name=Anabela Domingues Location={ }
Location: City=São Paulo Country=Brazil Revenues={ }
Revenues: TotalRevenue=9174.0200
AverageRevenue=658.00142857142857142857142857
Name=André Fonseca Location={ }
Location: City=Campinas Country=Brazil Revenues={ }
Revenues: TotalRevenue=8702.2300
AverageRevenue=401.63406666666666666666666667
----------------------------------------------------------------------------------------
Example 2
int orderCutoff = 20;
var x = from c in customers
where c.Orders.Count() > orderCutoff
orderby c.CustomerID
select new
{
c.CustomerID,
Name = c.ContactName
SumFreight = c.Orders.Sum(o => o.Freight),
};
var y = x.ToList();
ObjectDumper.Write(y, 3);
The above query will produce a list of customers who have placed more than 20 orders. The following SQL query will be generated to produce the result.
SELECT t0.CustomerID, t0.ContactName,
(
SELECT Sum(t2.Freight)
FROM Orders AS t2
WHERE (t2.CustomerID = t0.CustomerID)
)
FROM Customers AS t0
WHERE (
(
SELECT Count(*)
FROM Orders AS t2
WHERE (t2.CustomerID = t0.CustomerID)
)
> @p0)
ORDER BY t0.CustomerID
The results will look like so:
CustomerID=ERNSH Name=Roland Mendel SumFreight=6205.3900
CustomerID=QUICK Name=Horst Kloss SumFreight=5605.6300
CustomerID=SAVEA Name=Jose Pavarotti SumFreight=6683.7000
Example 3
var x = from c in customers
orderby c.City
where c.Country == "USA"
select new { c.City, c.ContactName } into customerLite
group customerLite by customerLite.City;
var y = x.ToList();
ObjectDumper.Write(y, 3);
This query will produce a list of customers located in the USA grouped by city. The following SQL query will be generated to produce the result.
SELECT t0.City, t0.ContactName
FROM Customers AS t0
WHERE (t0.Country = @p0)
ORDER BY t0.City
The results will look like so:
...
City=Albuquerque ContactName=Paula Wilson
...
City=Anchorage ContactName=Rene Phillips
...
City=Boise ContactName=Jose Pavarotti
...
City=Butte ContactName=Liu Wong
...
City=Elgin ContactName=Yoshi Latimer
...
City=Eugene ContactName=Howard Snyder
...
City=Kirkland ContactName=Helvetius Nagy
...
City=Lander ContactName=Art Braunschweiger
...
City=Portland ContactName=Liz Nixon
City=Portland ContactName=Fran Wilson
...
City=San Francisco ContactName=Jaime Yorres
...
City=Seattle ContactName=Karl Jablonski
...
City=Walla Walla ContactName=John Steel
Example 4
var customerList = (from c in customers
select new
{
c.CustomerID,
c.CompanyName,
Orders = from o in orders
where o.CustomerID == c.CustomerID
select o
}).ToArray();
var customerOrderGroups =
from c in customerList
select
new
{
c.CompanyName,
YearGroups =
from o in orders
where o.CustomerID == c.CustomerID
group o by ((DateTime)o.OrderDate).Year into yg
select
new
{
Year = yg.Key,
MonthGroups =
from o in yg
group o by ((DateTime)o.OrderDate).Month into mg
select new { Month = mg.Key, Orders = mg }
}
}
;
ObjectDumper.Write(customerOrderGroups, 3);
This query will produce a list of customers and their orders grouped by year and month. The results will look like so:
CompanyName=Alfreds Futterkiste YearGroups=...
YearGroups: Year=1997 MonthGroups=...
MonthGroups: Month=8 Orders=...
Orders: OrderID=10643 CustomerID=ALFKI
EmployeeID=6 OrderDate=8/25/1997 ....
That's it for now. In the next article I will demonstrate samples that use function calls such as customer.FirstName.ToUpper() and the use of join clauses.
Notes
- Most of the samples in the download will run successfully on Microsoft Access, but some, particularly those with a
Cross Join clause will not. There is no such thing as Cross Join in Access SQL, it is known as Outer Join instead (I think). The simple and wrong solution would be to change the source code so that the sting Outer Join is emitted. The right way is to eliminate the use of hard coded constants in the code and use a dependency injection Framework such as this one to customize the SQL statements generated to conform to the syntax required by the RDMS being queried. I will do this in the coming days. - 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.
- Most basic functionality has been covered, but there are corner cases that haven't been cornered as yet. In addition, translation of function calls is still a work in progress.
- There are bugs in the code. I even found one as I was writing the article. I will be doing a comprehensive code review in the coming days to root them out and improve the quality of the project.
- Comments, suggestions and bug reports would be most appreciated.
Cheers!
History
- 23rd January, 2008: Article posted
