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

LinqToSQL：对 SQLite、Microsoft Access、SQServer2000/2005 的全面支持

Muigai Mwaura
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.97/5 (17投票s)

2008 年 2 月 13 日

LGPL3

5分钟阅读

viewsIcon

183748

downloadIcon

2682

使用LinqToSql查询这些流行的RDMS产品

引言

本文的目的是演示 LinqToSql 库中的功能，该库可以将 LINQ 表达式树转换为 SQL 语句，以便在多个 RDMS 系统（而不仅仅是 Microsoft 的 SQL Server 产品）上执行。LinqToSql 库实现了以下特性和功能：

  1. 全面支持 SQLite、SQLServer 2000/2005、Microsoft Access、MySQL（正在测试中）
  2. 在单个表达式中透明地查询多个数据库，例如：Microsoft Access 数据库和 SQL Server 数据库。
  3. 转换在 String、DateTime 和 Nullable`1 类中具有 SQL 等效项的函数调用和属性访问器，例如：firstName.Length、firstName.ToUpper()、orderDate.Year、shippedDate.HasValue 等。
  4. 实现所有 IQueryable 方法，例如：GroupBy、Any、All、First、Sum、Average 等。
  5. 正确且全面地转换具有有效 SQL 翻译的二元和一元表达式。
  6. 参数化查询，而不是在 SQL 转换中嵌入常量。
  7. 缓存先前翻译的表达式树。
  8. 不使用 MARS - Multiple Active Result Sets，这是 SQL Server 2005 的特定功能。
  9. 正确翻译对 SelectMany 的调用，即使查询源涉及方法调用。SQL Server 2005 的特定关键字 CROSS APPLY 既不是必需的，也不是使用的。

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

有关实现细节，请参阅以下文章：1、2、3。

关于 LinqToSql 用法的先前文章可以在这里找到。

在本文中，我将重点介绍使用 SQLite 和集合运算符功能，如 Any、All、Union 等。

SQLite 简介

根据该网站的简介

SQLite 是一个软件库，它实现了自包含、无服务器、零配置、事务性 SQL 数据库引擎。SQLite 是全球部署最广泛的 SQL 数据库引擎。它被用于无数的桌面应用程序以及包括手机、PDA 和 MP3 播放器在内的消费电子设备。SQLite 的源代码属于公共领域。

维基百科表示：

SQLite 已知嵌入到：
  • Adobe Air，一个跨操作系统运行时环境，用于构建可以部署到桌面的应用程序。
  • Mozilla Firefox，领先的开源 Web 浏览器，用于用户配置文件中的某些数据库。
  • Ruby on Rails，Ruby on Rails 2.0 版本中的默认数据库。
  • Android，由 Google 创建的手机开发工具包，用于存储用户数据。
  • Mac OS X，自 10.4 (Tiger) 版本起，作为 Core Data API 的持久化层。
  • musikCube，一个轻量级音乐播放器，用于在动态播放列表中查询媒体库。
  • Google Gears，为 JavaScript 应用程序提供本地数据库功能。
  • Adobe Photoshop Lightroom，摄影管理和后期制作软件，用于其照片目录。
  • Mozy，在线备份软件，用于存储配置数据、备份集、数据状态等。
  • Avira Antivir - 杀毒软件

在我（短暂）使用 SQLite 的经验中，我发现它非常易于设置和使用，因此它是 LinqToSql 将运行的第一个非 Microsoft RDMS。

注意

SQLite 是一个 C 应用程序，您需要下载一个围绕核心 SQLite 库的 ADO.NET 包装器。 SQLite.NET 是一个绝佳的选择，这里使用了它。您可能还想下载 SQLite Database browser，它提供了 SQLite 的 GUI。

设置 SQLite

下载并安装上述软件后，您就可以运行下载中的示例了，因为以下工作已经完成：

  1. Northwind 已转换为 SQLite 数据库并放置在项目的 \bin 目录中。
  2. 已定义 SQLite 的提供程序工厂和连接字符串属性。

集合运算符

ALL

以下查询将提供一个列表，其中包含下了 订单 的 客户，这些 订单 全部 已运送到 客户所在的城市。

from c in customers
where (from o in c.Orders
       select o).All(o => o.ShipCity == c.City)
select new { c.CustomerID, c.ContactName };

这将产生以下 SQL 语句：

SELECT  t0.CustomerID, t0.ContactName
FROM Customers AS t0
WHERE 
(    SELECT  COUNT(*) 
    FROM Orders AS t1
    WHERE ((t1.CustomerID = t0.CustomerID) AND  NOT  ((t1.ShipCity = t0.City)))
) = 0

这听起来有点复杂，但我们本质上是在说，我们只想要那些没有 订单 运送到 客户所在城市 以外的 城市 的 客户，也就是 All 条件的逆否命题。

查询将产生以下结果：

CustomerID=ALFKI        ContactName=Maria Anders
CustomerID=ANATR        ContactName=Ana Trujillo
CustomerID=ANTON        ContactName=Antonio Moreno
CustomerID=BERGS        ContactName=Christina Berglund
CustomerID=BLAUS        ContactName=Hanna Moos
CustomerID=BLONP        ContactName=Frédérique Citeaux
------------------------------------------------------------

ANY (任意)

以下查询将提供一个列表，其中包含 客户，他们没有下过 订单。

from customer in customers
where !customer.Orders.Any()
select new { customer.CustomerID, customer.ContactName };

这将产生以下 SQL 语句：

SELECT  t0.CustomerID, t0.ContactName
FROM Customers AS t0
WHERE  NOT  (
(    SELECT  COUNT(*) 
    FROM Orders AS t1
    WHERE (t1.CustomerID = t0.CustomerID)
) > 0
)

在这里，我们再次翻译 Any 条件的逆否命题。

查询将产生以下结果：

CustomerID=FISSA        ContactName=Diego Roel
CustomerID=PARIS        ContactName=Marie Bertrand
------------------------------------------------------------

UNION

以下查询将提供一个居住在 伦敦 的 客户 和 员工 的列表。

       from c in customers.Where(d => d.City == "London")
       select new { ContactName = c.ContactName })
.Union(from e in employees.Where(f => f.City == "London")
       select new { ContactName = e.LastName })

这将产生以下 SQL 语句：

        SELECT  t2.ContactName
        FROM Customers AS t2
        WHERE (t2.City = @p0)

UNION
        SELECT  t2.LastName
        FROM Employees AS t2
        WHERE (t2.City = @p1)

查询将产生以下结果：

ContactName=Ann Devon
ContactName=Buchanan
ContactName=Dodsworth
ContactName=Elizabeth Brown
ContactName=Hari Kumar
ContactName=King
------------------------------------------------------------

看点

虽然 RDMS 中通过 SQL 公开的核心功能通常在不同数据库之间非常相似，但更高级的功能访问方式却因您使用的产品不同而大相径庭。

例如，以下查询...

from order in orders
where order.OrderDate.Value.Year > DateTime.Parse("1/1/1997").Year &&
      order.CustomerID.StartsWith("B")
select new { order.CustomerID, order.OrderID, order.OrderDate };

...将转换为以下 SQL Server 语句：

SELECT  t0.CustomerID, t0.OrderID, t0.OrderDate
FROM Orders AS t0
WHERE ((datePart(yyyy, t0.OrderDate) > @p1) AND t0.CustomerID Like (@p0 + '%'))

然而，在 SQLite 上，转换将是：

SELECT  t0.CustomerID, t0.OrderID, t0.OrderDate
FROM Orders AS t0
WHERE ((round(strftime('%Y', t0.OrderDate)) > @p1) _
    AND Like (@p0 || '%', t0.CustomerID))

LinqToSql 协调这些差异的机制以及如何扩展它以生成您选择的 RDMS 的正确 SQL 语法，将是下一篇文章的主题。我还将介绍用户定义的标量函数到存储过程/即席 SQL 的映射。

这次就到这里。再见！

注释

  1. 下载中的所有 60 多个示例都可以在 SQLite、Microsoft Access 和 SQL Server 上成功运行，但有少数例外。
  2. 不使用 MARS，但在查询评估期间可能会打开多个并发连接。我将在另一篇文章中详细介绍何时以及为何会发生这种情况、性能影响以及可能的解决方案。
  3. 一项全面的代码审查正在进行中，并且已经修复了许多错误。
  4. 非常感谢您的评论、建议和错误报告。

历史

  • 2008 年 2 月 13 日：初稿
© . All rights reserved.