Translation LINT to SQL Introduction (database query)-part.3

Source: Linq to SQL (part.3–querying our database)

List of terms

Built-in: Built-in
Clause: Clause
Debugger: Debugger
Object Relational Mapper: Objects relational Mapper
ORM (Object Relation Mapping): Object Relational Mapping
Visualizer: Viewer
Plug-in: Plugin Program
Breakpoint: Breakpoint
Shape: Construction
Object initialization: Objects initialization
Deferred execution model: deferred execution models
Sequences: Sequence
Object Initializer: Objects initializer
Collection Initializers: Collection initializer

Last month, I started publishing a series of essays about LINQ to SQL. LINQ to SQL is a built-in. NET Framework 3.5 version of the O/RM (Object Relational Mapping) framework, which makes it easy for you to use. NET class to model a relational database. You can use LINQ expressions to query, add, edit, and delete databases.

Here are the top two essays in my series:

L Part 1:linq to SQL introduction

L Part 2: Define our data Model classes

In today's essay, I will continue to show you in detail how to use the data model we created in the second essay to demonstrate how to use an ASP. NET project to query the data.

Northwind database modeled with LINQ to SQL

In the second part of this series of essays, I explain how to use the lint to SQL designer built into VS2008 to create a LINQ to SQL class model. Here is the class model we created for the Northwind sample database.

Get Products

Once we have defined the data model classes above, we can easily query and retrieve data from our database. LINQ to SQL is done by writing LINQ query statements to the NorthwindDataContext class created using the LINQ to SQL Designer.

For example, if you want to get a series of products objects, I can write code like this:

In the preceding statement, I used a "where" clause in a LINQ query statement to return products belonging to a particular category. I use CategoryID to filter.

One of the advantages of LINQ to SQL is the flexibility to define how data is queried, and I can use the relationships I establish when I build LINQ to SQL data classes to make a richer and more common query on the database. For example, I can modify this query to be based on the CategoryName of the product, rather than the CategoryID as above, as in the following.

Notice how I use the "category" property of product to filter products, which belong to the category with a specific CategoryName. This property is created automatically for us by LINQ to SQL because we have a one-to-many relationship in the database when we model the category and product classes.

To give an example of a relationship that uses our data model in a query, we can use the following LINQ query syntax to get products that have 5 or more orders.

Notice how we use LINQ to SQL for the "OrderDetails" collection we created on the product class (this is because we have a one-to-many relationship when we use the LINQ to SQL Designer).

Display the actual query code for LINQ to SQL when debugging

When you query or update your objects, the LINQ to SQL Object Relational Mapper automatically creates and executes the appropriate SQL code operations.

One of the biggest concerns or fears that developers have about this new ORM is "What SQL code is actually executing?" ”。 When you are debugging your application, a very good feature of LINQ is that it makes it very easy to see exactly what SQL code is actually executing.

From the BETA2 version of Visual Studio 2008, you can use the new Plug-in program LINQ to SQL Viewer to easily view (and test) any LINQ to SQL query expressions. Simply set a breakpoint on a LINQ to SQL query and click the Magnify icon to open the debugger's expression Viewer.

By doing this, you will see a dialog box that shows the actual SQL code that LINQ to SQL does when it executes the Get Product object operation:

If you click on the "Execute" button on this dialog, you will execute the SQL code directly with the debugger and see the actual result set returned from the database.

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

To bind a LINQ to SQL query to an ASP.

The LINQ query returns the result of implementing the IEnumerable interface, which is also an interface that the ASP. NET server control supports object binding. This means that you can bind any linq,linq to SQL, or the result of a LINQ to XML query to any ASP.

For example, you can declare a <asp:gridview> control in an. aspx page as follows,

Then, you can bind the results of the LINQ to SQL query that we have written to the GridView as follows:

This will result in a page like this:

Construct our query results

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

For example, this query gets the products:

As a result, all data is returned.

In general, we just 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 indicate that we only want a subset of the data, which can be done by modifying our LINQ to SQL query as follows.

This will only return this data from our database (as shown by our debug viewer).

The cool thing about LINQ to SQL is that when you construct data, you can take full advantage of the data model classes ' connections. This allows me to write very useful (and very efficient) data queries. For example, the following query gets the total number of orders from the product entity's ID and name, this product, and then settles the sum of the taxes for each product's order.

The expression to the right of the "Revenue" property 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 you can convert the above LINQ expression to the following SQL syntax when evaluated (if our debug viewer shows it).

The above SQL will evaluate all numorders and revenue values on SQL Server and fetch only the data shown below from the database (which makes it run very fast).

We can bind the results of the query to the GridView control to produce a nice user interface.

By the way, lest you keep guessing, when you write these LINQ construct queries, you can get full smart hints in VS2008.

In the example above, I declare an anonymous type that uses object initialization to construct and define a result structure. The real cool thing is that when dealing with these anonymous result sequences, VS2008 also provides full smart hints, compile detection, and refactoring support.

Paging the results of a query

One of the most common requirements in a Web environment is the efficient paging of user interface layer data. LINQ provides built-in support for two easy and efficient paging methods, both Skip () and take ().

We can use the following skip () and take () methods to indicate that we want to return only 10 product objects – starting with the initial product line we define as parameters.

Note above that I did not add skip () and take () to the original products query declaration, but rather later on the query (when binding to the GridView data source). Often people ask me, "This is not to say that the query first returns all the data from the database, and then in the (bad) middle tier then paging?" "Not at all." The reason is that LINQ uses a deferred execution model – which means that the query is not actually executed until you attempt to traverse the results.

One of the benefits of this deferred execution model is that it allows you to make a very good combination of queries between code statements (which improves code readability). It also allows you to combine queries from other queries – making it possible to combine and reuse some very flexible queries.

Once I have defined the Bindproduct () method above, I can write the following code in the page to get the starting index from QueryString and let the products in the GridView display.

This will present us with a product page that filters out products that have 5 or more orders, dynamically displays the computed data, and can be paginated using the QueryString parameter.

Note: When developing with SQL 2005, LINQ to SQL uses the Row_number () function to make all the paging logic in the database. This ensures that when the above code is executed, only 10 rows of the currently displayed page are returned from the database for the data we need.

This makes it more efficient and easy to page through large data series.

Summarize

For some of the cool data query methods provided by LINQ to SQL, hopefully these steps will provide a good overview. To learn more LINQ expressions and new language grammars supported by the C # and VB compilers in VS2008, read the following essays I published earlier:

L Automatic Properties, object initializers, and collection initializers

L extension method

L-Lamabada expression

L query syntax

L Anonymous Type

In my next essay in the LINQ to SQL series, I'll show how we can clearly add validation logic to our data model classes and demonstrate how we use it to encapsulate business-layer logic that runs every time we update, insert, or delete data. Then I'll explain more advanced lazy and eager load query scenarios (lazy and eager loading query scenarios), how to use the new <asp:LINQDataSource> Control to support declarative data binding for ASP, optimistic concurrency error handling, and much more.

Translation LINT to SQL Introduction (database query)-part.3