Part 3: query database (Part 3-querying our database)

This post is only for sharing knowledge. It is more concise (that is, a version without English) and will be published on the blog. I am reviewing this article.
I hope this post will be helpful for your learning of the LINQ to SQL language!

Original post link:
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. 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.

Last month, I started a series of posts on how to explain LINQ to SQL. LINQ to SQL is the implementation of O/RM (Object relationship ing) integrated in. Net framework3.5. It makes it easy for you to use the. NET class to generate a relational database model. Then you can query, update, insert, and delete it using a LINQ expression.

Below are the first two parts of my LINQ to SQL series:
Below is the link to my previous two posts:

• 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.

In my post today, I will explain in more detail how to use the data model generated in Part 2 and how to use it in ASP.. Net project to query data.

Northwind database modeled using LINQ to SQL

The northwind database generated using LINQ to SQL

In part 2 of this series I Have ed 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:

In part 2 of this series, I explained how to use the LINQ to SQL designer in vs2008 to generate the LINQ to SQL class model. The following is a class model generated from the northwind instance database:

Retrieving Products

Query 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.

Once we define the above data model class, we can easily query and retrieve data from the database. You can use the northwinddatacontext class generated by the LINQ to SQL designer to query and search databases.

For example, to retrieve and iterate over a sequence of product objects I cocould write code like below:

For example, you can write the following code to query and query the product Object Sequence cyclically:

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.

In the preceding query, I use the "where" statement in the LINQ statement to limit that only products of a specific type are returned. Here, I use the categoryid of product as the filter condition.

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 cocould modify the query to filter by the product's categoryname instead of its categoryid by writing my LINQ query like so:

One of the better aspects of the above LINQ to SQL is that there is great flexibility in how to query data, in addition, I can use the associations that have been first defined in the LINQ to SQL data class to perform richer and more natural queries on the database. For example, I can change my code as follows to query products by category names instead of category IDs:

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. this property was automatically created for us by LINQ to SQL because we modeled the category and product classes as having a similar to one relationship with each other in the database.

Note how I used the "category" attribute of each product object to filter the product Object List by category name. This attribute is automatically generated for us by LINQ to SQL, because we have a one-to-many relationship between the database category and the product table, which is also generated between the category and product classes.

For another simple example of using our data model's association relationships within queries, We cocould write the below LINQ query to retrieve only those products that have had 5 or more orders placed for them:

The following is another simple example of using the generated Association in the query, I can write the following LINQ query statement to find the list of products with more than 5 order details:

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 define relationship we modeled in the LINQ to SQL designer).

Note that we use the "orderdetails" set, it is the one-to-many relationship that has been generated for each product class by using the LINQ to SQL designer ).

Visualizing LINQ to SQL queries in the debugger

View the LINQ to SQL query in the debugging status

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.

When you query or update the object model generated by object relationship ing such as LINQ to SQL, they are automatically created and executed in a suitable SQL statement.

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 seeExactlyWhat SQL code it is executing when you run your application within the debugger.

What worries developers that they are associated with orms is, "What SQL statements does it actually execute? "What exactly happens to a LINQ to SQL statement is that it can easily check what SQL code it executes when running a program in the debugging state.

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:

Start with vs2008beta2. You can use a new LINQ to SQL visualization plug-in to conveniently query (TEST) Any LINQ to SQL Query expressions. Design a breakpoint, place the mouse in the debugging status to the LINQ to SQL query, and click the amplifier to view the query expression.

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:

Then, this operation will pop up a window showing the complete SQL statement that you use to search product objects using the LINQ to SQL statement:

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:

If you click "execute" in the dialog box, it runs the SQL statement in the debugger and can query the data returned from the database:

This obviusly 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.

Obviously, this makes it very convenient for you to view the precise SQL query logic generated by LINQ to SQL. Note: If you want to change it, you can also choose to override the SQL statements generated by the LINQ to SQL statements for you-although in the case of 98%, I think you will find that the SQL statements executed by the LINQ to SQL statements are very good.

Databinding LINQ to SQL queries to ASP. NET controls

Bind the query of LINQ to SQL with the control of 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.

A collection of ienumerable interfaces is returned for the LINQ query. ienumerable is also an interface that supports ASP. NET Server controls for data binding. This means that you can bind the result set of any query by LINQ, LINQ to SQL or LINQ to XML to any ASP. NET control.

For example, we cocould declare an <asp: gridview> Control in A. ASPX page like so:

For example, on the. ASPX page, I can declare a <asp: gridview> control like this:

I cocould then databind the result of the LINQ to SQL query we wrote before to the gridview like so:

Then, I can write a query by using the LINQ to SQL statement as follows to bind the query result to the gridview:

This will then generate a page that looks like below:

Then it generates the following page:

Shaping our query results

Format 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.

Next, when we want to evaluate our product query, by default we will find all columns that exist in the product entity class.

For example, this query to retrieve products:

For example, you can use the following query to search products:

Results In all of this data being returned:

All returned data result sets:

 

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:

Generally, we only want to return a subset of all the property sets of each product. In this way, we can use the C # and VB compilers and the new data shaping features supported by LINQ to declare that we only want to return a subset of the attribute set, modify the Code as follows:

This will result in only this data subset being returned from our database (as seen via our debug visualizer ):

This causes only this subset to be returned from the database (as shown in the debugger ):

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:

What's cool about writing data into a SQL table is that when formatting data, I can take full advantage of the relationship between my data models. This allows me to express data queries that are truly useful (and very efficient. For example, the following query retrieves the ID and name of the product entity, the total number of orders for the product, and then adds the profit of the order for each product.

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.

In the above expression, the attribute on the right of the revenue is the extension method provided by LINQ ). It uses the lambda expression (lambda expression) that returns the order fraud of each product as the parameter ).

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 is very intelligent and can convert the above LINQ expressions into the following SQL statements when evaluating values:

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 ):

The preceding SQL statements allow all order calculation and product profit calculation to be completed in SQL Server, and generate data with only the following result set returned from the database (which makes it faster to run):

We can then databind the result sequence against our gridview control to generate pretty UI:

Then we can bind the result sequence to our gridview control to generate a beautiful UI:

BTW-in case you were wondering, you do get full intelliisense within vs 2008 when writing these types of LINQ shaping queries:

By the way -- in order to prevent you from being confused, in vs2008, there will be a complete smart prompt when writing the query in these LINQ formats:

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 intelliisense, compilation checking, and refactoring support when working against these anonymous result sequences as well:

In the above example, I declare an anonymous type that uses object initialization to format and define the result structure. What's really cool is that vs2008 provides complete intelligent sensing, compile-time check, and support when refactoring this anonymous result sequence:

 

Paging our query results

Pagination of 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.

During web development, the most common requirement is to efficiently create a data paging UI. LINQ provides two built-in extension methods, which make paging simple and efficient -- skip () and take () methods.

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:

We can use the following SKIP () and take () Methods to declare that we only want to return 10 product objects from the database-starting from the initial product record (Here we use a parameter)

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.

Note Why I didn't use SKIP () and take () in the initial product query statement () operator -- it is added to the query later (when the data is bound to the datasource of the gridview ). People often ask me, "but this does not mean that the query first queries all records from the database and then goes through pages in the middle layer (this is not good )?", The answer is no. The reason is that LINQ uses a "delayed execution" Execution Mode-it means that it will not be executed until you try to traverse the result set.

One of the benefits of this deferred execution model is that it enables you to nicely compose queries into SS 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:

This will then show us a product page that only displays more than five order products and dynamically calculated product data. In addition, it can be paged using the querystring parameter:

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:

Note: When SQL 2005 is used, the SQL function row_number () is used in the database to execute paging logic. This ensures that when we execute the above Code, only 10 pieces of data that we need to display on the current page will be returned from the database:

This makes it efficient and easy to page over large data sequences.

This makes Paging for large data tables efficient and easy.

Summary

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:

I hope the above Code will provide you with an overview of some of the cool data queries provided by LINQ to SQL. To learn more about the LINQ expressions and the C # and VB compiler syntaxes supported by vs2005, read some of my previous posts:

• 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.

In the next post in this series, I will show you how to add a clear verification logic to our data model and how to use it to reduce each update, business verification is performed when data is inserted or deleted. At that time, I will explain more about latency and real-time loading, and how to use the <asp: linadatasource> Control to support the display binding ASP.. Net Control to optimize the solution for concurrent conflicts and other knowledge.

Hope this helps,
I hope this will help you.

Scott