Click here to Skip to main content
13,248,766 members (61,228 online)
Click here to Skip to main content
Add your own
alternative version


49 bookmarked
Posted 23 Jan 2008

LINQ to SQL Transformation: Examples and Source Code

, 23 Jan 2008
Rate this:
Please Sign up or sign in to vote.
LINQ to SQL Transformation: Open Source implementation of IQueryable, examples and source code


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.


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

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 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 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
Name=André Fonseca      Location={ }
  Location: City=Campinas         Country=Brazil  Revenues={ }
    Revenues: TotalRevenue=8702.2300

Example 2

int orderCutoff = 20;
var x = from c in customers
        where c.Orders.Count() > orderCutoff
        orderby c.CustomerID
        select new
            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
        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
                       Orders = from o in orders
                                where o.CustomerID == c.CustomerID
                                select o

var customerOrderGroups =
    from c in customerList
            YearGroups =
                from o in orders
                where o.CustomerID == c.CustomerID
                group o by ((DateTime)o.OrderDate).Year into yg
                        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.


  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.



  • 23rd January, 2008: Article posted


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


About the Author

Muigai Mwaura
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!

You may also be interested in...


Comments and Discussions

GeneralLink for updated source is broken Pin
Svideo_9831-Mar-08 5:36
memberSvideo_9831-Mar-08 5:36 
GeneralRe: Link for updated source is broken Pin
Jahmani5-Apr-08 0:36
memberJahmani5-Apr-08 0:36 
GeneralRe: Link for updated source is broken Pin
Jahmani10-Apr-08 2:43
memberJahmani10-Apr-08 2:43 
NewsUpdate Pin
Jahmani25-Feb-08 1:09
memberJahmani25-Feb-08 1:09 
Questioniterative Sum Pin
kazim bhai11-Feb-08 2:50
memberkazim bhai11-Feb-08 2:50 
I have a table having float values
ID Duration
1 5.0
2 5.0
3 5.0

but i want an iterative summation like shown in table while selecting above data

and so on

Please Help
Thanks in advance

It is Kazim

GeneralRe: iterative Sum Pin
Jahmani25-Feb-08 0:50
memberJahmani25-Feb-08 0:50 
AnswerRe: iterative Sum Pin
Mohd Wasif12-Oct-10 21:16
memberMohd Wasif12-Oct-10 21:16 
GeneralOptimization Pin
Jahmani25-Jan-08 2:03
memberJahmani25-Jan-08 2:03 
GeneralGreat!!!!! Pin
seesharper23-Jan-08 4:19
memberseesharper23-Jan-08 4:19 
GeneralRe: SourceCode? Pin
Jahmani23-Jan-08 4:24
memberJahmani23-Jan-08 4:24 
GeneralRe: SourceCode? Pin
Dewey23-Jan-08 23:24
memberDewey23-Jan-08 23: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171114.1 | Last Updated 23 Jan 2008
Article Copyright 2008 by Muigai Mwaura
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid