Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

LINQ to SQL Transformation: Examples and Source Code

Rate me:
Please Sign up or sign in to vote.
4.71/5 (9 votes)
23 Jan 2008LGPL33 min read 66.8K   825   50   11
LINQ to SQL Transformation: Open Source implementation of IQueryable, examples and source code

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:

In this article, I will focus on Grouping and Aggregation functionality.

Samples and Translations

Example 1

SQL
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.

SQL
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

SQL
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.

SQL
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

SQL
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.

SQL
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

SQL
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

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

 
GeneralLink for updated source is broken Pin
Svideo_9831-Mar-08 4:36
Svideo_9831-Mar-08 4:36 
GeneralRe: Link for updated source is broken Pin
Muigai Mwaura4-Apr-08 23:36
Muigai Mwaura4-Apr-08 23:36 
GeneralRe: Link for updated source is broken Pin
Muigai Mwaura10-Apr-08 1:43
Muigai Mwaura10-Apr-08 1:43 
NewsUpdate Pin
Muigai Mwaura25-Feb-08 0:09
Muigai Mwaura25-Feb-08 0:09 
Questioniterative Sum Pin
kazim bhai11-Feb-08 1:50
kazim bhai11-Feb-08 1:50 
GeneralRe: iterative Sum Pin
Muigai Mwaura24-Feb-08 23:50
Muigai Mwaura24-Feb-08 23:50 
AnswerRe: iterative Sum Pin
Mohd Wasif12-Oct-10 20:16
Mohd Wasif12-Oct-10 20:16 
GeneralOptimization Pin
Muigai Mwaura25-Jan-08 1:03
Muigai Mwaura25-Jan-08 1:03 
GeneralGreat!!!!! Pin
seesharper23-Jan-08 3:19
seesharper23-Jan-08 3:19 
GeneralRe: SourceCode? Pin
Muigai Mwaura23-Jan-08 3:24
Muigai Mwaura23-Jan-08 3:24 
GeneralRe: SourceCode? Pin
Dewey23-Jan-08 22:24
Dewey23-Jan-08 22:24 

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.