使用LINQ to SQL类的基本查询看起来很整洁,可读性也很强。另外LINQ to SQL类只取得你要求的数据。下面的代码查询了一组company name包含”Restaurant”的customers,并按照postal code对结果进行排序。
private void mnuBasicQuery_Click(object sender, RoutedEventArgs e)
{
var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = from c in ctx.Customers
where c.CompanyName.Contains("Restaurant")
orderby c.PostalCode
select c;
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());
}
LINQ to SQL 发送到SQL Server的查询为
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CompanyName] LIKE @p0
ORDER BY [t0].[PostalCode]
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [%Restaurant%]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
2.投射(Projections):
前面用的那个查询一个重要问题是Customers表中所有的列都被返回了,但是你可能只需要CustomerID,CompanyName和PostalCode。你可以使用投射来限制SQL Server返回的列。请看下面的这段代码。
private void mnuProjection_Click(object sender, RoutedEventArgs e)
{
var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = from c in ctx.Customers
where c.CompanyName.Contains("Restaurant")
orderby c.PostalCode
select new
{
c.CustomerID,
c.CompanyName,
c.PostalCode
};
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());
}
LINQ to SQL 发送到SQL Server的查询为
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[PostalCode]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CompanyName] LIKE @p0
ORDER BY [t0].[PostalCode]
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [%Restaurant%]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
3.Inner Joins:
private void mnuInnerJoin1_Click(object sender, RoutedEventArgs e)
{
var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = ctx.Customers.Join(
ctx.Orders,
c => c.CustomerID,
o => o.CustomerID,
(c, o) => new
{
c.CustomerID,
c.CompanyName,
o.OrderID,
o.OrderDate
}).OrderBy(r => r.CustomerID).ThenBy((r => r.OrderID));
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());
}
SELECT [t0].[CustomerID], [t0].[CompanyName], [t1].[OrderID], [t1].[OrderDate]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
上面的例子使用的是extension的方法来完成一个干净的join查询。这个查询可以用LINQ来写吗?当然,下面的代码就是用LINQ来完成的。
private void mnuInnerJoin2_Click(object sender, RoutedEventArgs e)
{
var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = from c in ctx.Customers
join o in ctx.Orders
on c.CustomerID equals o.CustomerID
orderby c.CustomerID, o.OrderID
select new
{
c.CustomerID,
c.CompanyName,
o.OrderID,
o.OrderDate
};
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());
}
SELECT [t0].[CustomerID], [t0].[CompanyName], [t1].[OrderID], [t1].[OrderDate]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
4.Outer Joins:
你可以使用GroupJoin extension方法。
private void mnuOuterJoin1_Click(object sender, RoutedEventArgs e)
{
var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = ctx.Customers.GroupJoin(
ctx.Orders,
c => c.CustomerID,
o => o.CustomerID,
(c, o) => new
{
c.CustomerID,
c.CompanyName,
Orders = o
})
.SelectMany(t => t.Orders.DefaultIfEmpty().Select(ord =>
new
{
t.CompanyName,
t.CustomerID,
OrderID = (int?)ord.OrderID,
OrderDate = (DateTime?)ord.OrderDate
}))
.OrderBy(r => r.CustomerID).ThenBy((r => r.OrderID));
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());
}
SELECT [t2].[CompanyName], [t2].[CustomerID], [t2].[value] AS [OrderID2], [t2].[value2] AS [OrderDate]
FROM (
SELECT [t1].[OrderID] AS [value], [t1].[OrderDate] AS [value2], [t0].[CompanyName], [t0].[CustomerID]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
) AS [t2]
ORDER BY [t2].[CustomerID], [t2].[value]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
在这段代码中,尽管使用了一个left outer join,但它是在子查询中的。其实你也可以使用LINQ的into关键字来完成这个查询。
private void mnuOuterJoin2_Click(object sender, RoutedEventArgs e)
{
var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = from c in ctx.Customers
join o in ctx.Orders
on c.CustomerID equals o.CustomerID into inJoin
from outJoin in inJoin.DefaultIfEmpty()
orderby c.CustomerID, outJoin.OrderID
select new
{
c.CustomerID,
c.CompanyName,
OrderID = (int?)outJoin.OrderID,
OrderDate = (DateTime?)outJoin.OrderDate
};
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());
}
SELECT [t0].[CustomerID], [t0].[CompanyName], [t1].[OrderID] AS [OrderID2], [t1].[OrderDate] AS [OrderDate]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
LINQ查询更加的整洁,而且发送的SQL查询也是一个干净的left outer join.
5.组与聚合:
LINQ to SQL也提供了分组操作来取得聚合结果。比如你想要获得所有order的总价格。
private void mnuAggregates_Click(object sender, RoutedEventArgs e)
{
var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var orders = from o in ctx.Order_Details
group o by o.OrderID
into grouped
select new
{
OrderID = grouped.Key,
Total = grouped.Sum(
line => line.Quantity * line.UnitPrice *
(1 - (decimal)line.Discount))
};
dg.ItemsSource = orders;
MessageBox.Show(sw.GetStringBuilder().ToString());
}
SELECT SUM([t1].[value]) AS [Total], [t1].[OrderID]
FROM (
SELECT (CONVERT(Decimal(29,4),[t0].[Quantity])) * [t0].[UnitPrice] * (@p0 - (CONVERT(Decimal(33,4),[t0].[Discount]))) AS [value], [t0].[OrderID]
FROM [dbo].[Order Details] AS [t0]
) AS [t1]
GROUP BY [t1].[OrderID]
-- @p0: Input Decimal (Size = -1; Prec = 33; Scale = 4) [1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
6.分页:
当编写一个需要查询百万千万的数据的应用时,你经常会遇到这样的问题,就是你取得的数据太多,已经超出你能显示它们的能力了。比如,你可能查询一些名字以A开头的customers,但是你并没有意识到这可能返回上千条的数据。分页是一个很减少从查询返回的数据的有效的方法。你可以 先看一部分的数据并决定你是否想看更多其他数据。要执行分页,你必须在你的LINQ查询中使用Skip和Take这两个extension方法。下面的例子中在WPF form上使用了一个滚动条。当你拖动滚动条的时候,Scroll事件就被触发了,然后你就可以看到上一页或下一页的customers。
private const int pageSize = 25;
private int pageCount;
private int customerCount;
private IQueryable<Tuple<string, string>> customers;
StringWriter sw = new StringWriter();
private void mnuPaging_Click(object sender, RoutedEventArgs e)
{
var ctx = new NorthwindDataContext();
ctx.Log = sw;
customers = from c in ctx.Customers
orderby c.CompanyName
select
new Tuple<string, string>(c.CustomerID, c.CompanyName);
customerCount = customers.Count();
pageCount = customerCount / pageSize;
if (pageCount * pageSize < customerCount) pageCount++;
scrData.Minimum = 0;
scrData.Maximum = pageCount;
scrData.Visibility = Visibility.Visible;
scrData.SmallChange = 1;
scrData_Scroll(null, null);
}
private void scrData_Scroll(object sender, System.Windows.Controls.Primitives.ScrollEventArgs e)
{
var customersDisplay = from c in customers
select new { ID = c.Item1, Name = c.Item2 };
dg.ItemsSource = customersDisplay.Skip((int)scrData.Value * pageSize).Take(pageSize);
}
SELECT COUNT(*) AS [value]
FROM [dbo].[Customers] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
SELECT [t1].[CustomerID] AS [item1], [t1].[CompanyName] AS [item2]
FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY [t0].[CompanyName]) AS [ROW_NUMBER],
[t0].[CustomerID], [t0].[CompanyName]
FROM [dbo].[Customers] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [25]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
前面几个执行的查询中,第一个是取得customers的总个数。当调用customers.Count()的时候该查询被执行。下一个查询是来取得第一页的customers。在这个查询中使用了两个参数@p0--当前页,和@p1--页面大小。这两个参数被分别设为0和25。查询本身使用了SQL Server 2005及以后的版本才有的ROW_NUMBER函数。这也就是为什么LINQ to SQL在SQL server 2000上有很多限制的原因了。当你向上或者向下翻页时,第二个查询就开始执行,但是是使用了当前页面作为@p0的值。
7. 小结:
(1)你可以使用DataContext对象的table属性来执行查询。
(2)如果你使用了where,LINQ to SQL 会创建一个包含where 子句的SQL 查询来限制返回的数据行数。
(3)如果你在select中使用了投射来限制你选择的列,LINQ to SQL也将会在查询中只选择你选择的那几列。
(4)LINQ to SQL支持inner join 和outer join。
(5)LINQ to SQL 支持分组(grouping) 和聚合(aggregation)。
(6)你可以使用Skip和Take 这两个extension方法来对数据进行分页。
原文出自《Access Data with Microsoft .NET Framework 4》