New Orcas language Features-query syntax _oracle

"Original Address" New "Orcas" Language feature:query Syntax
"Original published date" Saturday, April 21, 2007 2:12

Last month I started a series of posts to discuss some of the new VB and C # language features released as part of the Orcas version of Visual Studio and the. NET Framework. Here is a link to the top three posts in the series:

• Automatic properties, object initializers, and collection initializers
• Extension methods
• Lambda expression

Today's post will discuss another fundamental new language feature: query Syntax (Syntax).

What is query syntax (Syntax)?

Query syntax is a convenient declarative simplification in the use of standard LINQ query operators to express queries. This syntax can improve readability and simplicity when expressing queries in code, easy to read, and easy for people to write about. Visual Studio provides full IntelliSense and compile-time checking support for query syntax.

At the bottom, C # and VB compilers translate query syntax expressions into explicit method invocation code, which leverages new extension methods and lambda expression language features in Orcas.

Examples of query syntax:

In my previous language series, I demonstrated that you can declare a person class as follows:

Then we can use the following code, use some personal information to generate a list<person> set instance, and then use the query syntax to do a LINQ query on the collection, just remove those who have the first name (last name) of the letter G, Sort by first name (ascending):

The above query syntax is semantically equivalent to the following code that explicitly uses the LINQ extension method and the lambda expression:

The advantage of using the query syntax method is that the result is slightly easier to read and write, especially when the expression becomes more complex.

Query syntax-Understanding from and select clauses:

In C #, the syntax of each query expression begins with the FROM clause and ends with a select or a group clause. The FROM clause indicates what data you want to query. The SELECT clause indicates what data you want to return and what configuration it should return.

For example, let's look at our query for the List<person> collection:

In the code snippet above, "from p in people" means that I'm going to do a LINQ query to the "People" collection, and I'll use the argument "P" to represent every item in the input sequence I'm querying. The fact that we name the argument "P" is irrelevant, and I can easily name it "O", "x", "person" or whatever name I want.

In the code snippet above, the "select P" clause at the end of the statement indicates that, as a result of the query, I want to return a IEnumerable sequence of the person object. This is because the "People" collection contains objects of type person, and parameter p represents the person object in the input sequence. Therefore, the result data type of the query syntactic expression is ienumerable<person>.

If I don't return the person object, I want to return the names of the people in that collection, I can rewrite the query like this:

Notice that I no longer say "select P", but "select P.firstname". This means that I do not want to return a string of person objects, but rather to return a string of strings that are populated by the FirstName property of the Person object, which is a string. Therefore, the result type of the query syntactic expression is ienumerable<string>.

Examples of query syntax for databases

The beauty of LINQ is that I can use exactly the same query syntax for any data type. For example, I can use the new LINQ to SQL Object Relational Mapper support provided by Orcas to model the Northwind database of the SQL Server and generate the following classes (see my video here to learn how to do this):

After defining the class model (and the mapping between it and the database), I can then write a query syntax expression to remove those products with a unit price greater than 99 yuan:

In the code snippet above, I mean I'm going to make a LINQ query to the NorthwindDataContext class's Products table, which is generated by the ORM designer of Visual Studio Orcas. NorthwindDataContext. "Select P" indicates that I want to return a string of Product objects that match my query, so the result data type of the query syntax expression is ienumerable<product>.

As in previous list<person> query syntax examples, the C # compiler translates our declarative query syntax into explicit extension method calls (using lambda expressions as arguments). In the case of the above LINQ to SQL example, these lambda expressions are converted to SQL commands and then run on the SQL Server (so that only the product row rows that match the query criteria are returned to our application). The details of the mechanism that contributed to this lambda->sql transformation are visible in the lambda expression tree section of my lambda expression blog posts.

Query syntax-Understanding WHERE and by clauses:

Between the "from" clause at the beginning of a query syntax expression and the "select" clause at the end, you can use the most common LINQ query operators to filter and transform the data you are querying. The two most commonly used clauses are "where" and "by". These two clauses handle filtering and sorting the result set.

For example, to return a list of sorted names in descending alphabetical order from the Northwind database, the filter condition includes only those categories that contain more than 5 products, and we can write the following query syntax to query our database using LINQ to SQL:

In the expression above, we add the "where C.products.count > 5" clause to indicate that we only have categories that contain more than 5 products. This leverages the Association of LINQ to SQL in the database between products and classifications. In the expression above, I also added an ORDER by C.categoryname descending clause to indicate that I want to arrange the result set in descending sequence by name.

LINQ to SQL and then when you query the database using this expression, the following SQL is generated:

Select [T0]. [CategoryName] from [dbo]. [Categories] As [t0]
Where ((
Select COUNT (*)
from [dbo]. [Products] As [T1]
Where [T1]. [CategoryID] = [T0]. [CategoryID]
)) > 5
Order BY [T0]. [CategoryName] DESC

Note that LINQ to SQL is smart enough to return only the single field (category name) We need, and it does all the filtering and sorting at the database level, making the query very efficient.

Query syntax-use projection (projection) to transform data

One of the main points I pointed out earlier is that the "select" clause represents the data you want to return and what the configuration of the data is .

For example, if you have a "select P" clause like the following, the type of p here is person, and then it returns a string of person objects:

A very powerful feature of LINQ and query syntax is that it allows you to define new types that are separate from the data being queried, and then use the new type to control the shape and structure of the data returned by the query.

For example, suppose we define a new Alternateperson class that contains a FullName attribute instead of the separate FirstName and LastName attributes in our original Person class:

Then I can use the LINQ query syntax below to query my original list<person> collection and convert the result into a string of Alternateperson objects using the following query syntax:

Notice how we create a new Alternateperson instance with its properties in the "select" clause in the expression above, using the new object initializer syntax discussed in the first post of my language series. Also notice how I connect the FirstName and LastName attributes of our original person class and assign it to the FullName property.

Use query syntax projection for database

This projection feature becomes incredibly useful when manipulating data retrieved from a remote data provider such as a database, because it provides us with an elegant way to show what data fields our ORM should actually retrieve from the database.

For example, suppose I use a LINQ to SQL ORM Provider to model the Northwind database, generating the following classes:

By writing the following LINQ query, I told LINQ to SQL I want to return a string of product objects:

All the fields required to populate the product class are returned from the database as part of the above query, and raw SQL executed by LINQ to SQL Orm looks like this:

Select [T0]. [ProductID], [t0]. [ProductName], [t0]. [SupplierID], [t0]. [CategoryID],
[T0]. [QuantityPerUnit], [t0]. [UnitPrice], [t0]. [UnitsInStock],
[T0]. [UnitsOnOrder], [t0]. [ReorderLevel], [t0]. [Discontinued]
from [dbo]. [Products] As [t0]
Where [T0]. [UnitPrice] > 99

In some scenarios, I don't need or use all these fields, I can define a new MyProduct class like the one below, owning only some of the attributes that the product class has, and an extra attribute that the product class does not have, totalrevenue (note: For those unfamiliar with C # , the Decimal syntax indicates that our UnitPrice attribute is a nullable value):

Then I can use this query to construct the shape of the data I want to return from the database using the query syntax projection function:

This shows that instead of returning a bunch of product objects, I'm going to myproduct the object, as long as three of these attributes are assigned, LINQ to SQL will intelligently adjust the raw SQL statement to be executed, returning only the three required product fields from the database:

Select [T0]. [ProductID], [t0]. [ProductName], [t0]. [UnitPrice]
from [dbo]. [Products] As [t0]
Where [T0]. [UnitPrice] > 99

For the sake of ostentation, I can also populate the fourth attribute of the MyProduct class, the Totalrevenue property. I want this value equal to the total amount of our product's current sales. This value does not exist as a predefined field in the Northwind database. Instead, you need to make a connection between the Products table and the Order Details table, and then calculate the total number of all order Detail lines corresponding to a given product.

Very cool, I can compute this value by using the LINQ Sum extension method on the OrderDetails Association of the product class, and writing a multiplication lambda expression that is part of my query syntax projection:

LINQ to SQL will be very clever to use the following SQL in the SQL database operations:

Select [T0]. [ProductID], [t0]. [ProductName], [t0]. [UnitPrice], (
Select SUM ([t2].[ Value])
From (
Select [T1]. [UnitPrice] * (CONVERT (Decimal (29,4), [t1].[ Quantity]) as [value], [T1]. [ProductID]
from [dbo]. [Order Details] As [T1]
) as [T2]
Where [T2]. [ProductID] = [T0]. [ProductID]
) as [value]
from [dbo]. [Products] As [t0]
Where [T0]. [UnitPrice] > 99

Query syntax-Understanding deferred Execution (Deferred Execution) and using ToList () and ToArray ()

By default, the type of the result of the query syntax expression is ienumerable<t>. In the above example, you will notice that all the query syntax assignments are given to ienumerable<product>, Ienumerable<string>, Ienumerable<person> Ienumerable<alternateperson>, and ienumerable<myproduct> variables.

A good feature of the Ienumerable<t> interface is that the objects that implement them can delay the actual query operation to the first attempt by the developer to iterate the return value (this is achieved by using the yield construct first introduced in the VS 2005 C # 2.0) . LINQ and query syntax expressions take advantage of this feature to delay the actual operation of the query until you loop the return value for the first time. If you never iterate over the results of a ienumerable<t>, the query will not be executed at all.

For example, consider the following LINQ to SQL example:

rather than querying the syntactic expression declaration, we are accessing the database and fetching the values needed to populate the Category object when we first attempt to loop the result (where the red Arrow flag is above).

The behavioral result of this delay operation becomes very useful because it facilitates a powerful combination of multiple LINQ queries and expressions chained together. For example, we can feed the result of one expression to another, and then, by delaying operations, allow ORM like LINQ to SQL to optimize raw SQL based on the entire expression tree. I will be in a blog post in the future to do a demonstration of such a scene.

How to perform an operation on query syntax expression immediately

If you do not delay query operations, but rather perform operations on them immediately, you can use the built-in ToList () and toarray () operators to return a list<t> or array that includes the result set.

For example, to return a generic list<t> set:

To return an array:

In both cases, the database is immediately accessed and the Category object is populated.

Conclusion

Query syntax provides a very convenient declarative simplification when using standard LINQ query operators to express queries. It provides syntax readability that can be queried for any type of data (in-memory collections, arrays, XML content, and remote data providers such as databases, Web services, and so on). Once you are familiar with this syntax, you can apply this knowledge anywhere.

In the near future, I will end the last part of the language series, which will discuss the new anonymous type attribute. Then I'll turn to some very practical examples of using all of these language features in practical applications (especially for databases and XML files using LINQ).

I hope this article is of some help to you,

Scott