ScottGu之博客翻译－LINQ to SQL第三部分，查询数据库 (Part 3 - Querying our Database)
希望本貼能對您的LINQ to SQL語言的學習有一定的幫助！
原貼鏈接：
http://weblogs.asp.net/scottgu/archive/2007/06/29/linq-to-sql-part-3-querying-our-database.aspx
?
Last month I started a blog post series covering LINQ to SQL.? LINQ to SQL is a built-in O/RM (object relational mapping) framework that ships in the .NET Framework 3.5 release, and which enables you to easily model relational databases using .NET classes.? You can then use LINQ expressions to query the database with them, as well as update/insert/delete data from it.
上個月我開始了一個講解LINQ to SQL的帖子系列。LINQ to SQL是集成在.NET Framework3.5中的O/RM（對象關系映射）的實現，它讓你非常容易地用.NET類來生成關系型數據庫的模型。然后你可以用LINQ 表達式對它來進行查詢，更新，插入刪除。
Below are the first two parts of my LINQ to SQL series:
下邊是我的前兩篇的帖子的鏈接：
- Part 1: Introduction to LINQ to SQL
- Part 2: Defining our Data Model Classes
In today's blog post I'll be going into more detail on how to use the data model we created in the Part 2 post, and show how to use it to query data within an ASP.NET project.
在今天的帖子中，我將會更詳細地講解一下如何使用我們在第2部分（Part 2 ）中生成數據模型，并且講解一下如何在ASP.NET項目中使用它來查詢數據。
Northwind Database Modeled using LINQ to SQL
用LINQ to SQL生成的Northwind數據庫
In Part 2 of this series I walked through how to create a LINQ to SQL class model using the LINQ to SQL designer that is built-into VS 2008.? Below is the class model that we created for the Northwind sample database:
在本系列的第2部分（?Part 2）中，我講解了如何用VS2008中的LINQ to SQL設計器來生成LINQ to SQL類模型。下面是從Northwind事例數據庫生成的類模型：
Retrieving Products
查詢產品
Once we have defined our data model classes above, we can easily query and retrieve data from our database.? LINQ to SQL enables you to do this by writing LINQ syntax queries against the NorthwindDataContext class that we created using the LINQ to SQL designer above.
一旦我們定義了如上的數據模型類，我們就可以非常方便地從數據庫中查詢并且檢索數據。LINQ to SQL允許你通過對上邊的用LINQ to SQL設計器生成的NorthwindDataContext類寫LINQ語句來對數據庫進行查詢和檢索。
For example, to retrieve and iterate over a sequence of Product objects I could write code like below:
例如，我可以通過寫如下的代碼來查過和對產品對象序列進行循環查詢：
In the query above I have?used a "where" clause in my LINQ syntax query to only return those products within a specific category.? I am using the CategoryID of the Product to perform the filter.
在上邊的查詢中，我在LINQ 語句中用"where"語句來限定僅返回屬于某種特定類型的產品。這里我是用Product的CategoryID作為過濾條件。
One of the nice things above LINQ to SQL is that I have a lot of flexibility in how I query my data, and I can take advantage of the associations I've setup when modeling my LINQ to SQL data classes to perform richer and more natural queries against the database.? For example, I could modify the query to filter by the?product's CategoryName instead of its CategoryID by writing my LINQ query like so:
上邊的LINQ to SQL的其中一個比較好的地方是，在如何查詢數據這個問題上有很大的靈活性，并且我可以用已經先定義在LINQ to SQL 數據類中的關聯來對數據庫進行更豐富和更自然的查詢。例如，我可以像如下這樣更改我的代碼以實現對產品用類別名而不是用類別ID進行查詢：
Notice above how I'm using the "Category" property that is on each of the Product objects to filter by the CategoryName of the Category that the Product belongs to.? This property was automatically created for us by LINQ to SQL because we modeled the Category and Product classes as having a many to one relationship with each other in the database.
注意上邊我是如何用每個Product對象的”Category"屬性來來對產品對象列表通過類別名稱進行過濾。這個屬性是LINQ to SQL自動為我們生成的，因為我們將數據庫的Category和Product表有一對多的關系也生成在了Category和Product類之間了。
For another simple example of using our data model's association relationships within queries, we could write the below LINQ query to retrieve only?those products that have had 5 or more orders placed for them:
下面是另外一個簡單的在查詢中使用生成的關聯關系進行查詢的小例子，我可以通過寫如下的LINQ查詢語句來查出超過5條訂單詳細信息的產品列表：
Notice above how we are using the?"OrderDetails" collection that LINQ to SQL has created for us on each Product class (because of the 1 to many relationship we modeled in the LINQ to SQL designer).
注意我們用的"OrderDetails"集合，它是LINQ to SQL已經為我們在每個Product類上生成好的（因為我們用LINQ to SQL設計器生成的有一對多的關系）。
Visualizing LINQ to SQL Queries in the Debugger
在調試狀態下查看LINQ to SQL查詢
Object relational mappers like LINQ to SQL handle automatically creating and executing the appropriate SQL code for you when you perform?a query or update against their object model.?
當你對像LINQ to SQL這樣的對象關系映射生成的對象模型進行查詢或者更新時，它們會自動地處理生成和執行適合的SQL語句。
One of the biggest concerns/fears that developers new to ORMs have is "but what SQL code is it actually executing?"? One of the really nice things about LINQ to SQL is that it makes it super?easy to see exactly what SQL code it is executing when you run your application within the debugger.
和開發者對ORMs關聯并且困擾著他們的是“它實際上執行的是什么SQL語句？“，LINQ to SQL真正好的一處是它可以在調試狀態下運行程序時，很方便地看它執行了什么SQL代碼。
Starting with Beta2 of Visual Studio 2008 you can use a new LINQ to SQL visualizer plug-in to easily see (and test out) any LINQ to SQL query expression.? Simply set a breakpoint and then hover over a LINQ to SQL query and click the magnify glass to pull up its expression visualizer within the debugger:
用VS2008Beta2作為開始，你可以用一個新的LINQ to SQL可視化插件來方便地查詢（測試出）任何的LINQ to SQL查詢表達式。簡單地設計一個斷點，然后在調試狀態下將鼠標放到LINQ to SQL查詢，點擊放大器來將查看查詢表達式。
This will then bring up a dialog that shows you the exact SQL that LINQ to SQL will use when executing the query to retrieve the Product objects:
然后，這個操作就會彈出一個窗口，該窗口中展示的就是你在用該LINQ to SQL檢索產品對象時用到的完整的SQL語句：
If you press the "Execute" button within this dialog it will allow you to evaluate the SQL directly within the debugger and see the exact data results returned from the database:
如果在該對話框中你點擊“Execute”，它會直接在調試器中執行SQL語句并且能夠查詢從數據庫中返回的數據：
This obviously makes it super easy to see precisely what SQL query logic LINQ to SQL is doing for you.? Note that you can optionally override the raw SQL that LINQ to SQL executes in cases where you want to change it - although in 98% of scenarios I think you'll find that the SQL code that LINQ to SQL executes is really, really good.
很明顯，這使得你查看LINQ to SQL為你生成的精確的SQL查詢邏輯變得非常地方便。注意，如果想改變它，你也可以選擇重寫LINQ to SQL為你生成的SQL語句－－雖然在98%的情況下我想你會發現LINQ to SQL執行的SQL語句非常非常地好。
Databinding LINQ to SQL Queries to ASP.NET Controls
將LINQ to SQL的查詢和ASP.NET 的控件綁定
LINQ queries return results that implement the IEnumerable interface - which is also an interface that ASP.NET server controls support to databind object.? What this means is that you can databind the results of any LINQ, LINQ to SQL, or LINQ to XML query to any ASP.NET control.
LINQ 查詢返回一個實現了IEnumerable接口的集合－－IEnumerable也是支持數據綁定的ASP.NET服務器控件實現的接口。說這個的意思就是你可以將任何的LINQ,LINQ to SQL或LINQ to XML查詢的結果集綁定到任何的ASP.NET控件。
For example, we could declare an <asp:gridview> control in a .aspx page like so:
例如，我可以在.aspx頁面像這樣聲明一個<asp:gridview>控件：
I could then databind the result of the LINQ to SQL query we wrote before to the GridView like so:
然后我可以像下面這樣寫LINQ to SQL的查詢來綁定查詢結果到gridview上：
This will then generate a page that looks like below:
然后它就就會生成一個如下的頁面：
Shaping our Query Results
格式化查詢結果
Right now when we are evaluating our product query, we are retrieving by default all of the column data needed to populate the Product entity classes.?
下面，當我們想評估我們的產品查詢，默認情況下我們查出所有的在Product實體類中存在的列。
For example, this query to retrieve products:
例如用下面這個查詢來檢索產品：
Results in all of this data being returned:
返回的所有數據結果集：
?
Often we only want to return a subset of the data about each product.? We can use the new data shaping features that LINQ and the new C# and VB compilers support to indicate that we only want a subset of the data by modifying our LINQ to SQL query?like so:
通常我們只想返回每個產品的所有屬性集合的一個子集。這樣的話，我們就可以用C#和VB編譯器和LINQ支持的新的數據格式化特性（data shaping features）來聲明我們只想返回屬性集合的一個子集，像下面這樣來修改代碼：
This will result in only this data subset being returned from our database (as seen via our debug visualizer):
這將使得僅僅從數據庫中返回這個子集（如在調試器中所示）：
What is cool about LINQ to SQL is that I can take full advantage of my data model class associations?when shaping my data.? This enables me to express really useful (and very efficient) data queries.? For example, the below query retrieves the ID and Name from the Product entity, the total number of orders that have been made for the Product, and then sums up the total revenue value of each of the Product's orders:
LINQ to SQL酷的是，在格式化數據時，我可以充分利用我的數據模型的關系。這使得我可以表達真正有用（而且非常高效）的數據查詢。例如，如下的這個查詢檢索Product實體的ID和Name，該產品的訂單總數，然后將每個產品的訂單的利潤加了起來。
The expression to the right of the "Revenue" property above is an example of using the "Sum" extension method provided by LINQ.? It takes a Lambda expression that returns the value of each product order item as an argument.?
上面這個表達式中，Revenue右邊的屬性是使用LINQ 提供的"Sum"擴展方法（?extension method）。它使用了返回了每個產品的訂單欺項作為參數的Lambda表達式（Lambda expression）。
LINQ to SQL is smart and is able to convert the above LINQ expression to the below SQL when it is evaluated (as seen via our debug visualizer):
LINQ to SQL非常智能，并且能夠在求值時將以上的LINQ 表達式轉換為如下的SQL語句：
The above SQL causes all of the NumOrders and Revenue value computations to be done inside the SQL server, and results in only the below data being retrieved from the database (making it really fast):
以上的SQL語句使得所有的訂單計算和產品利潤計算在SQL server中完成，并且生成了從數據庫中返回僅有如下結果集的數據（這使得它運行更加快）：
We can then databind the result sequence against our GridView control to generate pretty UI:
然后我們可以將結果序列綁定到我們的GridView控件來生成一個漂亮的UI：
BTW - in case you were wondering, you do get full intellisense within VS 2008 when writing these types of LINQ shaping queries:
順便提一下－－為了防止你疑惑，在Vs2008中，寫這些LINQ 格式的查詢時會有完全的智能提示：
In the example above I'm declaring an anonymous type that uses object initialization to shape and define the result structure.? What is really cool is that VS 2008 provides full intellisense, compilation checking, and refactoring support?when working against these anonymous result sequences as well:
在上面的例子中，我聲明了一個使用了對象初始化（object initialization ）的匿名類型（anonymous type）來格式化和定義結果結構。真正酷的是，Vs2008提供了完全的智能感知，編譯時檢查，和當對這個匿名結果序列進行重構時的支持：
?
Paging our Query Results
對查詢結果分頁
One of the common needs in web scenarios is to be able to efficiently build data paging UI.? LINQ provides built-in support for two extension methods that make this both easy and?efficient?- the Skip() and Take() methods.
在Web開發時，最常見的一個需求就是能夠高效地建立數據分頁的UI。LINQ 提供了兩個內置的擴展方法，它使得分頁不但簡便，而且高效－－Skip()和Take()方法。
We can use the Skip() and Take() methods below to indicate that we only want to return 10 product objects - starting at an initial product row that we specify as a parameter argument:
我們可以用下面的Skip()和Take()方法來聲明我們只想從數據庫中返回10條產品對象－－從初始的產品記錄開始（此處我們作為了一個參數）
Note above how I?did not?add the Skip() and Take() operator on the initial products query declaration - but instead added it later to the query (when binding it to my GridView datasource).? People often ask me "but doesn't this mean that the query first grabs all the data from the database and then does the paging in the middle tier (which is bad)?"? No.? The reason is that LINQ uses a deferred execution model - which means that the query doesn't actually execute until you try and iterate over the results.?
注意上面我為何沒在初始的產品查詢聲明時用Skip()和Take()操作符－－而是稍后將它加到了查詢中（當將數據綁定到GridView的DataSource時）。人們經常問我，“但是這不意味著該查詢首先從數據庫中查詢出所有記錄然后在中層層進行分頁（這是不好的）？”，答案是否定的。原因就是LINQ用的是一個“延遲執行”的執行模式－－它意味著直到當你試圖遍歷結果集時它才會執行。
One of the benefits of this deferred execution model is that it enables you to nicely compose queries across multiple code?statements (which improves code readability).? It also enables you to compose queries out of other queries - which enables some very flexible query composition and re-use scenarios.
Once I have the BindProduct() method defined above, I can write the code below in my page to retrieve the starting index from the querystring and cause the products to be paged and displayed in the gridview:
This will then give us a products page, filtered to list only those products with more than 5 orders, showing dynamically computed product data,?and which is pageable via a querystring argument:
這然后就會展示給我們一個產品頁，僅僅顯示了超過5條訂單產品，顯示動態計算出來的產品數據，而且它是可以通過Querystring參數進行分頁：
Note: When working against SQL 2005, LINQ to SQL will use the ROW_NUMBER() SQL function to perform all of the data paging logic in the database.? This ensures that only the 10 rows of data we want in the current page view are returned from the database when we execute the above code:
注意：當使用SQL 2005時，LINQ to SQL將會在數據庫中使用ROW_NUMBER()這個SQL函數來執行分頁邏輯。這保證了當我們執行以上的代碼時，從數據庫中僅返回10條在當前頁面上顯示的我們需要顯示的的數據：
This makes it efficient and easy to page over large data sequences.
這使得對大的數據表進行分頁變得高效和容易。
Summary
總結
Hopefully the above walkthrough provides a good overview of some of the cool data query opportunities that LINQ to SQL provides.? To learn more about LINQ expressions and the new language syntax supported by the C# and VB compilers with VS 2008, please read these earlier posts of mine:
希望以上的代碼給你提供了一些LINQ to SQL提供的很酷的數據查詢的概述。若想更多地了解LINQ 表達式和VS2005中C#及VB編譯器支持的語法，請閱讀我前期的一些帖子：
- Automatic Properties, Object Initializer and Collection Initializers
- Extension Methods
- Lambda Expressions
- Query Syntax
- Anonymous Types
In my next post in this LINQ to SQL series I'll cover how?we can?cleanly add validation logic to our data model classes, and demonstrate how we can use it to encapsulate business logic that executes every time we update, insert, or delete our data.? I'll then cover more advanced lazy and eager loading query scenarios, how to use the new <asp:LINQDataSource> control to support declarative databinding of ASP.NET controls, optimistic concurrency error resolution, and more.
在本系列的下一篇帖子中，我將講述一下如何向我們的數據模型中添加一個清晰的驗證邏輯，并且講述如何用它來減少每次我們進行更新，插入或刪除數據時都會執行的業務驗證。屆時我會更深入地講解延遲和即時加載的情景，如何使用<asp:LINADataSource>控件來支持顯示的綁定ASP.NEt控件，優化并發沖突錯誤的解決方案，以及其他的一些知識。
Hope this helps,
希望這些對你有所幫助。
Scott
?
轉載于:https://www.cnblogs.com/hanxianlong/archive/2007/11/18/962692.html
總結
以上是生活随笔為你收集整理的ScottGu之博客翻译－LINQ to SQL第三部分，查询数据库 (Part 3 - Querying our Database)的全部內容，希望文章能夠幫你解決所遇到的問題。
