[Translation] Introduction to lint to SQL (database query)-part.3

PDF browse: http://files.cnblogs.com/JimmyZhang/Linq-To-Sql-Part_3-Querying-our-Database.pdf

Source:LINQ to SQL (part.3-querying our database)

Glossary

Built-in: built-in
Clause: Clause
Debugger: Debugger
Object Relational er Er: object link er
ORM (object relation mapping): object relationship ing
Visualizer: Viewer
Plug-in: plug-in program
Breakpoint: breakpoint
Shape: Construction
Object initialization: object initialization
Deferred execution model: delayed execution model
Sequences: Sequence
Object initializer: Object initializer
Collection initializers: Set initializer

Last month, I began to publish a series of articles about LINQ to SQL. LINQ to SQL is an O/RM (Object link ing) framework built in. NET Framework 3.5. It allows you to easily use the. NET class to model relational databases. You can query, add, edit, and delete a database using a LINQ expression.

The first two articles in my series are as follows:

L Part 1: Introduction to LINQ to SQL

L Part 2: define our data model class

In this article, I will continue to detail how to use the data model we created in the second article to demonstrate how to use the ASP. NET project to query data.

The northwind database modeled by using LINQ to SQL

In the second article of this series, I explained step by step how to use the lint to SQL designer built in vs2008 to create a LINQ to SQL class model. The following is the class model we created for the northwind sample database.

Get Products

Once we define the above data model class, we can easily query and retrieve data from our database. By writing a LINQ query statement for the northwinddatacontext class created using the LINQ to SQL designer.

For example, if you want to obtain a series of products objects, you can write the code as follows:

In the preceding statement, I used a "where" clause in the LINQ query statement to return the products of a specific category. I use categoryid for filtering.

One of the advantages of LINQ to SQL is its great flexibility in defining how to query data, in addition, I can also use the relationships established when I set up a LINQ to SQL data class to perform more abundant and common queries on the database. For example, I can modify this query to implement it according to the categoryname of the product, rather than according to the categoryid as shown above, as shown below.

Note how I used the "category" attribute of product to filter products. These products belong to the category with a specific categoryname. This attribute is automatically created for us by using LINQ to SQL, because we have a one-to-many relationship in the database when modeling the category and product classes.

To give an example of using the association relationship of our data model in a query, we can use the following LINQ query syntax to obtain the products with five or more orders.

Note that, how do we use the "orderdetails" set created on the product class by using LINQ to SQL (this is because we have a one-to-many relationship when using the LINQ to SQL designer ).

Display the actual query code of the LINQ to SQL statement during debugging.

When you query or update your objects, the LINQ er automatically creates and executes appropriate SQL code.

One of the developers' biggest concerns or fears about this New ORM is "What SQL code is actually executed ?". When debugging your application, a good feature of LINQ is that it can easily view what SQL code is actually executed.

After Visual Studio 2008's beta2 version, you can use the new plug-in program, the LINQ to SQL viewer, to easily view (and test) Any LINQ to SQL Query expressions. You can simply set a breakpoint for a LINQ to SQL query, and click the zoom in icon to enable the expression viewer of the debugger.

In this way, you will see a dialog box that displays the actual SQL code of the LINQ to SQL statement when executing the get product object operation:

If you click the "execute" button in this dialog box, you will use the debugger to directly execute this SQL code and see the actual result set returned from the database.

Obviously, it is very easy to know exactly what SQL statements are executed for you by using LINQ to SQL. It is worth noting that when you need to make some changes, you can choose to overwrite the SQL statement executed by this LINQ to SQL-although in 98%, I think you will find that the SQL code to be executed by LINQ to SQL is very, very good.

Bind LINQ to SQL to query ASP. NET controls

The result of implementing the ienumerable interface returned by the LINQ query is also an interface that supports object binding for ASP. NET Server controls. This means that you can bind any query results of LINQ, LINQ to SQL, or LINQ to XML to any ASP. NET control.

For example, you can declare a <asp: gridview> control on the. ASPX page as follows,

Then, you can bind the result of the written LINQ to SQL query to the gridview as follows:

This will generate a page like the following:

Construct Our query results

Now when we run our product query, by default, we will get the data of all required columns to fill the product entity class.

For example, this query obtains products:

The result is that all data is returned.

Generally, we only want to return a subset of each product data. We can use the data construction features supported by the new, LINQ, and new C #/vB compilers to specify that we only want a subset of data, this can be done by modifying our LINQ to SQL query as follows.

In this way, only the data is returned from our database (as shown in our debug viewer ).

What's cool about LINQ to SQL is that when constructing data, you can fully utilize the connection of the data model class. This allows me to write very useful (and very efficient) data queries. For example, the following query gets the ID and name from the product entity, the total number of orders under this product, and then settles the total tax amount of the orders for each product.

The expression on the right of the "revenue" attribute above is an example of using the "sum" extension method provided by LINQ. It uses a Lambada expression that returns the value of each product order item as a parameter.

LINQ to SQL is very intelligent, and can convert the above LINQ expression into the following SQL syntax when evaluating (if shown in our debug viewer ).

The preceding SQL statement calculates the values of all numorders and revenue on SQL Server, and obtains only the data shown below from the database (which makes it run very fast ).

We can bind the query results to the gridview control to generate a good user interface.

By the way, you can get a complete smart prompt in vs2008 when writing these LINQ structure queries.

In the above example, I declare an anonymous type that uses object initialization to construct and define the result structure. What's really cool is that vs2008 also provides complete smart prompts, compilation detection, and refactoring support when dealing with these anonymous result sequences.

Paging query results

One of the most common requirements in Web environments is to efficiently paging user interface layer data. LINQ provides built-in support for two simple and efficient paging extension methods, namely SKIP () and take ().

We can use the following SKIP () and take () Methods to specify that we only want to return 10 product objects-starting with the initial product line we defined as the parameter.

Note that I did not add SKIP () and take () to the initial products query declaration, but added them to the query (when bound to the gridview data source ). I am often asked, "Isn't that the query first returns all the data from the database, and then paging in the (bad) Middle Layer ?" No. The reason is that LINQ uses a delayed execution model-this means that the query is not actually executed until you try to traverse the results.

One of the advantages of this delayed execution model is that it allows you to make a good combination of queries between code statements (which improves code readability ). It also allows you to combine queries from other queries-This makes it possible to combine and reuse queries flexibly.

Once I have defined the above bindproduct () method, I can write the following code on the page to obtain the start index from querystring and display the products in the gridview by page.

In this way, we will present a Products Page, filter out the products with five or more orders, dynamically display the calculated product data, and use the querystring parameter for paging.

Note: When SQL 2005 is used for development, LINQ to SQL uses the row_number () function to perform all paging logic in the database. This ensures that only 10 rows of data are returned from the database when the above code is executed.

This makes paging of large data sequences more efficient and easy.

Summary

For some of the cool data query methods provided by LINQ to SQL, we hope these steps can provide a good overview. If you want to learn more LINQ expressions and new language syntaxes supported by C # and VB compilers in vs2008, read the notes I posted earlier:

L automatic attributes, object initializer and set Initiator

L Extension Method

L lamabada expression

L query syntax

L anonymous type

In my next article in The LINQ to SQL series, I will introduce how to clearly add verification logic to our data model class and demonstrate how to use it to encapsulate the business layer logic, these business logics run every time we update, insert, or delete data. Then I will explain more advanced lazy and eager loading query scenarios (lazy and eager loading query scenarios), and how to use the new <asp: linqdatasource> Control to support ASP. net Control declarative data binding, optimistic concurrent error handling, and more.