65.9K
CodeProject 正在变化。 阅读更多。
Home

LINQ to SQL 转换：示例和源代码

Muigai Mwaura
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.71/5 (9投票s)

2008年1月23日

LGPL3

3分钟阅读

viewsIcon

69699

downloadIcon

846

LINQ to SQL 转换：IQueryable 的开源实现、示例与源代码

引言

.NET Framework v3.5 版本包含了大量新增和增强的技术。在我看来，LINQ (Language Integrated Query) 是 v3.5 版本中最重要的新技术。微软实现了一组库，用于将 LINQ 表达式树转换为 SQL 语句，并将其命名为 DLINQ。DLINQ 是一个令人印象深刻的作品，但可惜它仅适用于 SQL Server 2000 和 2005。

背景

本文的目标是展示 LinqToSql 库中的功能，该库可以将 LINQ 表达式树转换为 SQL 语句，而这些语句不仅可以针对微软的 SQL Server 产品，还可以针对多个 RDMS 系统执行。LinqToSql 库实现了以下特性和功能：

  1. 正确且全面地转换可以有效转换为 SQL 的二元和一元表达式。
  2. 转换具有 SQL 等效项的函数调用（例如 customer.FirstName.ToUpper()）。
  3. 实现所有 IQueryable 方法，例如 GroupBy, Any, All, Sum, Average 等。
  4. 参数化查询，而不是在 SQL 转换中嵌入常量。
  5. 执行先前翻译过的表达式树的缓存。
  6. 不使用 MARS - Multiple Active Result Sets，这是 SQL Server 2005 的特定功能。

到目前为止，我已经测试了在 SQL Server 2000 和 2005 上的功能。与 Microsoft Access 和其他 RDMS 的兼容性测试正在进行中，因为该项目仍在积极开发中。

上面可供下载的项目文件包含在 SQL Server 2005 和 Microsoft Access 上运行的针对著名的 Northwind 数据库的示例。

有关实现细节，请参阅以下文章：

在本文中，我将重点关注分组和聚合功能。

示例和转换

示例 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);

上述查询将生成一个位于伦敦或巴西的客户列表，以及他们订单实现的总体收入和平均收入。将生成以下 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

结果将如下所示：

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

示例 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);

上述查询将生成一个下过 20 个以上订单的客户列表。将生成以下 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

结果将如下所示：

CustomerID=ERNSH        Name=Roland Mendel      SumFreight=6205.3900
CustomerID=QUICK        Name=Horst Kloss        SumFreight=5605.6300
CustomerID=SAVEA        Name=Jose Pavarotti     SumFreight=6683.7000

示例 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);

此查询将生成一个位于美国、按城市分组的客户列表。将生成以下 SQL 查询来产生结果。

SELECT t0.City, t0.ContactName
FROM Customers AS t0
WHERE (t0.Country = @p0)
ORDER BY t0.City

结果将如下所示：

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

示例 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);

此查询将生成一个客户及其订单、按年份和月份分组的列表。结果将如下所示：

CompanyName=Alfreds Futterkiste         YearGroups=...
  YearGroups: Year=1997   MonthGroups=...
    MonthGroups: Month=8    Orders=...
      Orders: OrderID=10643   CustomerID=ALFKI
        EmployeeID=6    OrderDate=8/25/1997    ....

目前就到这里。在下一篇文章中，我将演示使用诸如 customer.FirstName.ToUpper() 等函数调用以及 join 子句的示例。

注释

  1. 下载中的大部分示例可以在 Microsoft Access 上成功运行，但有些，特别是带有 Cross Join 子句的示例则不能。Access SQL 中不存在 Cross Join，它被称为 Outer Join（我认为）。简单且错误的解决方案是更改源代码，以便发出字符串 Outer Join。正确的方法是消除代码中硬编码常量的使用，并使用诸如这个的依赖注入框架来定制生成的 SQL 语句，以符合正在查询的 RDMS 所需的语法。我将在接下来的几天内做到这一点。
  2. 不使用 MARS，但在查询评估期间可能会打开多个并发连接。我将在另一篇文章中详细介绍何时以及为何会发生这种情况、性能影响以及可能的解决方案。
  3. 大多数基本功能已经涵盖，但仍有一些未被完全处理的边缘情况。此外，函数调用的翻译仍在进行中。
  4. 代码中存在 bug。在我撰写本文时，我甚至发现了一个。我将在接下来的几天内进行全面的代码审查，以找出这些 bug 并提高项目质量。
  5. 非常感谢您的评论、建议和错误报告。

祝好！

历史

  • 2008 年 1 月 23 日：文章发布
© . All rights reserved.