SQL Server CEVisual Basic 9 (2008)SQL Server 2000Windows VistaWindows 2003ADO.NETWindows 2000LINQArchitectWindows XP.NET 3.5SQL Server 2005C# 3.0IntermediateDevSQL ServerSQLWindows.NETVisual BasicC#
LinqToSQL：在一个 LINQ 表达式中查询多个数据库，以及更多...
4.11/5 (8投票s)
在一个表达式中查询多个数据库，例如 Microsoft Access 数据库和 SQL Server 数据库
引言
本文的目的是演示 LinqToSql 库中的功能，该库可以将 LINQ 表达式树转换为 SQL 语句，这些语句可以针对多个 RDMS 系统执行，而不仅仅是 Microsoft 的 SQL Server 产品。LinqToSql 库实现了以下特性和功能：
- 在一个表达式中查询多个数据库，例如 Microsoft Access 数据库和 SQL Server 数据库
- 转换
String和DateTime类中具有 SQL 等价项的函数调用和属性访问器，例如firstName.Length、firstName.ToUpper()、orderDate.Year等。 - 实现所有
IQueryable方法，例如GroupBy、Any、All、Sum、Average等。 - 正确且全面地转换具有有效 SQL 翻译的二元和一元表达式。
- 参数化查询，而不是在 SQL 转换中嵌入常量。
- 执行先前翻译的表达式树的缓存。
- 不使用 MARS - Multiple Active Result Sets，这是一个 SQL Server 2005 特有功能。
- 正确转换对
SelectMany的调用，即使查询源涉及方法调用。SQL Server 2005 特有的关键字CROSS APPLY既不需要也不使用。
到目前为止，我已经对 SQL Server 2000 和 2005 的功能进行了测试。与 Microsoft Access 和其他 RDMS 的兼容性测试正在进行中，因为该项目仍处于积极开发阶段。
上面可供下载的项目文件包含在 SQL Server 2005 和 Microsoft Access 上的著名 Northwind 数据库中运行的示例。
在本文中，我将重点关注 Join 功能以及 String 和 DateTime 类中函数调用和属性访问器的使用。但首先...
在一个 LINQ 表达式中查询多个数据库
场景
Northwind 公司由于其糟糕的客户关系和低效的订单履行而面临财务危机。为了挽救局面，Northwind 的管理层发出了一份问卷，询问客户他们最喜欢的电影是什么。计划是确定该电影的导演，然后向客户发送该导演的其他电影的 DVD 合集。不幸的是，只有一位客户回复了问卷。
尽管如此，查询仍必须继续...针对以下内容
- SQL Server 上的 Northwind 数据库
- FavouriteDVD.mdb，一个 Microsoft Access 文件
- 一个内存中的数据结构，包含导演和他们执导的电影列表
我们按如下方式设置查询源：
customers = new Query(new SqlQueryProvider(northwindConnection));
// northwindConnection is an SqlConnection to Northwind Db on SQL Server
.............
favouriteDVDs = new Query(new SqlQueryProvider(favouriteConnection));
// favouriteConnection is an OleDbConnection to FavouriteDVD.mdb
..................................................
var directorMovies = new Dictionary>();
directorMovies.Add("Stanley Kubrick", new List()
{ "Paths of Glory", "A Clockwork Orange" });
directorMovies.Add("Alfred Hitchcok", new List()
{ "39 Steps", "The Lady Vanishes" });
directorMovies.Add("John Frankenheimer", new List()
{ "Ronin" });
现在我们已经设置好了数据源，我们可以运行查询了：
var dvdInfo = (from customer in customers
where customer.CustomerID == "ALFKI"
select new {
customer.CustomerID,
customer.ContactName,
FavoriteDVD = (from favouriteDVD in favouriteDVDs
where favouriteDVD.CustomerID == _
customer.CustomerID
select new {
Movie = favouriteDVD.DVDTitle,
favouriteDVD.Director
}).First()
}
).ToList();
这让我们从 Northwind (SQL Server) 获取了客户信息，并从 FavoriteDVD (Microsoft Access) 获取了客户最喜欢的电影。
现在，我们需要一份由同一导演制作的电影列表：
var dvdInfoPlus = from info in dvdInfo
select new {
info.CustomerID,
info.ContactName,
Movies = new {
info.FavoriteDVD,
MoviesBySameDirector = _
from movies in directorMovies[info.FavoriteDVD.Director]
select movies,
}
};
结果如下
CustomerID=ALFKI ContactName=Maria Anders Movies={ }
Movies: FavoriteDVD={ } MoviesBySameDirector=...
FavoriteDVD: Movie=Apocalypse Now Director=Stanley Kubrick
MoviesBySameDirector: Paths of Glory
MoviesBySameDirector: A Clockwork Orange
搞定。下载示例代码并试用。
题外话
我并非有意设计 LinqToSql 来做到这一点。我昨天在运行测试时偶然发现了这个功能。我将在以后详细探讨这类事情。（想想使用简单的 LINQ 语法查询和聚合来自不同数据库、Web 服务、LDAP 目录等的数据。很有趣，不是吗？）
修复一个巨大的漏洞
如果你上周下载了示例代码，并尝试运行如下查询，你将无济于事。
var x = from c in customers
join o in orders on c.CustomerID equals o.CustomerID
let m = c.Phone
orderby c.City
where c.Country == "UK"
where m != "555-555"
select new { c.City, c.ContactName } into customerLite
where customerLite.City == "London"
select customerLite;
var y = x.ToList();
ObjectDumper.Write(y, 3);
查看生成的表达式就会明白为什么：
.Join(, c => c.CustomerID, o => o.CustomerID,
(c, o) => new <>f__AnonymousType3`2(c = c, o = o))
.Select(<>h__TransparentIdentifier46 => new <>f__AnonymousType14`2(
<>h__TransparentIdentifier46 = <>
h__TransparentIdentifier46,
m = <>h__TransparentIdentifier46.c.Phone))
.OrderBy(<>h__TransparentIdentifier47 =>
<>h__TransparentIdentifier47.<>h__TransparentIdentifier46.c.City)
.Where(<>h__TransparentIdentifier47 =>
(<>h__TransparentIdentifier47.<>
h__TransparentIdentifier46.c.Country = "UK"))
.Where(<>h__TransparentIdentifier47 =>
(<>h__TransparentIdentifier47.m != "555-555"))
.Select(<>h__TransparentIdentifier47 => new <>f__AnonymousTypea`2(
City = <>h__TransparentIdentifier47.<>
h__TransparentIdentifier46.c.City,
ContactName = <>h__TransparentIdentifier47.<>
h__TransparentIdentifier46.c.ContactName))
.Where(customerLite => (customerLite.City = "London"))
这至少暴露了以下问题：
- 非常复杂的别名，例如
<>h__TransparentIdentifier47.<>，这是对
h__TransparentIdentifier46.c.ContactNamec.ContactName的引用进行的别名。 - 对
Queryable类中定义的方法进行顺序调用，例如.Where(...).Where(...)。
在周末大量饮酒的间隙 ;) 我设法解决了这些问题。具体如何做到这一点将在以后讨论。
无论如何，这个表达式及其类似表达式现在都可以成功解析。在这种情况下，你会得到一个如下所示的 SQLStatement：
SELECT City, ContactName
FROM
Customers AS t1
INNER JOIN
Orders AS t2
ON t1.CustomerID = t2.CustomerID
WHERE (t1.Country = @p2) AND (t1.Phone <> @p1) AND (t1.City = @p0)
ORDER BY t1.City
这将产生以下结果：
City=London ContactName=Thomas Hardy
.............................................
City=London ContactName=Victoria Ashworth
.............................................
City=London ContactName=Elizabeth Brown
.............................................
City=London ContactName=Ann Devon
.............................................
City=London ContactName=Simon Crowther
.............................................
City=London ContactName=Hari Kumar
.............................................
示例和翻译
示例 1
var cutoffDate = new DateTime(1998, 1, 1);
var x = from c in customers
join o in orders on c.CustomerID equals o.CustomerID
where c.CustomerID.StartsWith("A") && o.OrderDate > cutoffDate
orderby c.ContactName, o.OrderDate descending
select new { Name = c.ContactName, o.OrderDate };
var y = x.ToList();
ObjectDumper.Write(y, 3);
上述查询将生成一个 customers 列表，其 customerID 以字母“A”开头，并在 1/1/1998 之后下达了订单。将生成以下 SQL 查询来产生结果：
SELECT t1.ContactName, t2.OrderDate
FROM
Customers AS t1
INNER JOIN
Orders AS t2
ON t1.CustomerID = t2.CustomerID
WHERE (t1.CustomerID Like (@p1 + '%') AND (t2.OrderDate > @p0))
ORDER BY t1.ContactName , t2.OrderDate Desc
结果将如下所示：
Name=Ana Trujillo OrderDate=3/4/1998
Name=Antonio Moreno OrderDate=1/28/1998
Name=Maria Anders OrderDate=4/9/1998
Name=Maria Anders OrderDate=3/16/1998
Name=Maria Anders OrderDate=1/15/1998
Name=Thomas Hardy OrderDate=4/10/1998
-----------------------------------------------------------------------------------
示例 2
var x = from c in customers
orderby c.City
where c.City.Contains("ri") || c.Country.EndsWith("o")
select new { c.Country, c.City, c.ContactName };
var y = x.ToList();
ObjectDumper.Write(y, 3);
上述查询将生成一个 customers 列表，这些客户居住在城市名称包含字符串“ri”的城市，或者居住在国家名称以字母“o”结尾的国家。
SELECT t0.Country, t0.City, t0.ContactName
FROM Customers AS t0
WHERE (t0.City Like ('%' + @p1 + '%') OR t0.Country Like ('%' + @p0))
ORDER BY t0.City
结果将如下所示：
Country=Venezuela City=I. de Margarita ContactName=Felipe Izquierdo
Country=Spain City=Madrid ContactName=Alejandra Camino
Country=Spain City=Madrid ContactName=Martín Sommer
Country=Spain City=Madrid ContactName=Diego Roel
Country=Mexico City=México D.F. ContactName=Francisco Chang
Country=Mexico City=México D.F. ContactName=Ana Trujillo
示例 3
var x = 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 };
ObjectDumper.Write(x);
此查询将生成一个 customers 列表，这些客户在 1997 之后下达了订单，并且其 customerID 以字母“B”开头。将生成以下 SQL 查询来产生结果：
SELECT t0.CustomerID, t0.OrderID, t0.OrderDate
FROM Orders AS t0
WHERE ((DATEPART(year, t0.OrderDate) > @p1) AND t0.CustomerID Like (@p0 + '%'))
结果将如下所示：
CustomerID=BOTTM OrderID=11048 OrderDate=4/24/1998
CustomerID=BLAUS OrderID=11058 OrderDate=4/29/1998
CustomerID=BONAP OrderID=11076 OrderDate=5/6/1998
示例 4
var x = customers
.SelectMany(
c => orders.Where(o => c.CustomerID == o.CustomerID &&
c.CustomerID.StartsWith("C")),
(c, o) => new { c.ContactName, o.OrderDate }
)
.OrderByDescending(d => d.OrderDate)
.Select(e => new {e.ContactName, e.OrderDate})
.Where(f => f.OrderDate > DateTime.Parse("1/1/1996"));
ObjectDumper.Write(x);
此查询将生成一个 customers 列表，这些客户以字母“C”开头，并在 1/1/1996 之后下达了订单。将生成以下 SQL 查询来产生结果：
SELECT ContactName, OrderDate
FROM
Customers as t1
, as t2
Orders
WHERE ((t1.CustomerID = Orders.CustomerID) AND _
t2.CustomerID Like (@p1 + '%')) AND (t2.OrderDate > @p0)
ORDER BY OrderDate Desc
结果将如下所示：
ContactName=Patricio Simpson OrderDate=4/28/1998
ContactName=Yang Wang OrderDate=4/22/1998
ContactName=Pedro Afonso OrderDate=4/22/1998
ContactName=Yang Wang OrderDate=4/16/1998
ContactName=Pedro Afonso OrderDate=3/23/1998
ContactName=Yang Wang OrderDate=3/20/1998
ContactName=Patricio Simpson OrderDate=3/10/1998
.................................................................................
暂时就到这里。在下一篇文章中，我将研究 LinqToSql 的性能如何与 DLINQ 相媲美。
注释
- 下载中的所有 40 多个示例都可以在 Microsoft Access 和 SQL Server 上成功运行，但那些具有多个参数的示例除外 - 这些在 Microsoft Access 上运行时将不返回结果。我正在调查此行为的原因。
- 为了快速修复使调用
SelectMany的示例能在 Microsoft Access 和 SQL Server 上运行，CrossJoinHandler将不会发出CROSS JOIN关键字，而是用逗号分隔表。这不是最佳行为，并且将在稍后应用前一篇文章中提到的更复杂的修复。 - 不使用 MARS，但在查询评估期间可能会打开多个并发连接。我将在另一篇文章中详细介绍何时以及为何会发生这种情况、性能影响以及可能的解决方案。
- 大多数功能都已涵盖，但仍有一些未被完全处理的边缘情况。
- 正在进行全面的代码审查，并修复了许多错误。
- 非常感谢您的评论、建议和错误报告。
祝好！