LINQ to SQL Transformation: Examples and Source Code

• Download source - 204.34 KB

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:

1. Correctly and comprehensively translates binary and unary expressions that have valid translations into SQL
2. Translates function calls (e.g. customer.FirstName.ToUpper()) that have SQL equivalents
3. Implements all IQueryable methods e.g. GroupBy, Any, All, Sum, Average etc.
4. Parameterizes queries instead of embedding constants in the SQL transformation
5. Performs caching of previously translated expression trees
6. 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:

• How to: LINQ to SQL Transformation
• How to: LINQ to SQL Transformation: Part II
• How to: LINQ to SQL Transformation: Part III

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

1. 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.
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. 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.
4. 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.
5. Comments, suggestions and bug reports would be most appreciated.

Cheers!

History

• 23rd January, 2008: Article posted