用LINQ to SQL 进行查询

来源：互联网发布：java中do while的用法编辑：程序博客网时间：2024/05/24 03:12

1.基本的带过滤与排序的查询:

使用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 @p0ORDER 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 @p0ORDER 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.1SELECT [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 + @p1ORDER 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》