Application of Database query in LINQ

Today I saw scottgu publish the third part of LINQ to SQL (Part 3-querying our database) on his blog, and I also loaded it to Visual Studio 2008 beta1, translate Your learning operations according to your actual learning operations.

The northwind database uses the LINQ designer for modeling.

Through previous learning, we have learned how to create a class model using the built-in LINQ to SQL designer of Visual Studio 2008. The following is the created northwind database class model:

Search Product

Once we define the data model class, we can easily query and retrieve data from the database. You can write the LINQ syntax to query the northwinddatacontext class created by the designer. For example, to retrieve and iterate a series of product objects, you can writeCode:

The where clause is used in the preceding query to return a product set of the specified category. In this example, categoryid is used for filtering. However, when querying data, LINQ to SQL has a lot of flexibility. You can use the relational associations defined in modeling to perform richer and more natural query operations. For example, you can modify the preceding query to use categoryname filtering instead of categroy filtering. The Code is as follows:

Note how to use the category attribute of each product object in the preceding query to filter data by the name of the product category. This attribute is automatically created by LINQ to SQL because there is a one-to-many relationship between the category and product classes modeled based on the database. The following is another example of using data model link association for query. Only product sets that appear more than five times in the order are retrieved, the orderdetails set is also automatically created by LINQ based on the relationship association in the data model:

Visual Query of LINQ to SQL In the debugger 

When you execute a query or update an object model, object relationship mappings such as LINQ to SQL will automatically create and execute appropriate SQL code. One of the biggest concerns/concerns of developers who are unfamiliar with orms is "What kind of SQL code does it actually execute ?". The good news is that when you use a debugger to run an applicationProgramYou can easily see the actual SQL code:

The actual SQL code is as follows:

From Visual Studio 2008 beta2, you can use a new LINQ to SQL visualization plug-in to more easily view and test any LINQ Query expressions (unfortunately, Microsoft hasn't released beta2 yet ). The visualization operations shown in the original article are as follows:

Bind the LINQ to SQL query result to the ASP. NET Control

The result set that implements the ieumerable interface is returned by the LINQ query. This is exactly the interface that ASP. NET controls use to bind objects. Therefore, you can bind any LINQ, LINQ to SQL, or LINQ to XML query result set to the ASP. NET control.

For example, you can define a gridview on the aspx page:

Then bind the LINQ to SQL query result set to the gridview in the Code:

The generated page is as follows:

Query Result Configuration

In the above product query, we retrieve the data of all columns mapped in the product object class by default:

However, we often only need to return a subset of each product. In this case, we can use the data configuration features supported by the LINQ, C #, and VB compilers to modify the LINQ to SQL query to return only one subset of the data:

The returned data subset result is as follows:

What's more cool about LINQ to SQL is that when constructing data, we can fully utilize the relational associations of Data Model classes to express truly useful and efficient data queries. For example, the following query retrieves productid and name from the product entity, including the total number of orders for the product and the total price of each product in all orders:

The actual SQL statement executed for the conversion of the LINQ to SQL statement is as follows:

The returned dataset result is as follows:

 

In the preceding example, the object initiator defines an anonymous type to configure and define the result structure. When processing these anonymous result sets, Visual Studio 2008 also provides complete intelligent sensing, compilation verification, and refactoring support:

Query result page

A common requirement for Web applications is to efficiently create a data paging UI. The built-in functions of LINQ provide two extension methods to make this work simple and efficient-the SKIP () and take () methods. The following two methods are used to return only 10 product objects to be displayed by specifying the parameter. If the connected database is SQL Server 2005, LINQ uses the row_number () function for paging operations:

 

Note that skip () and take () are not added in the initialization of the products query definition, but are added when data is bound later. This leads to the question: "Does this mean that the query extracts all the data from the database and then performs paging (bad practice )?". No, because the delay execution model is used in LINQ-the query is actually executed only when the result set is actually used. One of the advantages of the delayed execution model is that it can easily combine query statements across multiple code blocks to improve code readability. Similarly, you can combine query statements other than other query statements to improve the flexibility and reusability of query writing.

After the bindproducts () method is defined, you can pass in the Start row index through querystring. The products result set is displayed on the page in the gridview:

Summary

Key points:

1. Use the relational Association modeling and Data configuration features of LINQ to customize the query results.

2. the query will be executed only when the result is actually used. You can flexibly combine query statements to improve code reuse.

3. The LINQ to SQL visual debugging plug-in Visual Studio 2008 is provided only in beta2 @_@