New Orcas language features-query syntax

[Original address] New "Orcas" Language Feature: Query Syntax
[Original article publication date] Saturday, 2017l 21,200

Last month I started a post series to discuss some new VB and C # language features released as part of Visual Studio and. NET Framework Orcas. The links to the first three posts of this series are as follows:

• Automatic attributes, object initializing, and set initializing
• Extension Method
• Lambda expressions

Today's post will discuss another basic new language feature:Query Syntax).

What is Query Syntax )?

The query syntax is a convenient declarative and simplified way of writing a query using the standard LINQ query operator. This syntax can add readability and conciseness when expressing queries in code, which is easy to read and easy to write. Visual Studio provides complete support for intelliisense and compile-time check for query syntaxes.

At the bottom, C # and VB compilers translate the syntax expression of the query into a clear method call code. Such code utilizes the new extension method in Orcas and the Lambda expression language features.

Example of syntax query:

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

 

Then we can use the following code to generate a List <Person> collection instance with some personal information, and then useQuery syntaxTo make a LINQ query for the set. Only those whose first names are G are retrieved and sorted by first name (ascending ):

The syntax expression for the preceding query is equivalent to the Code for explicitly using the LINQ Extension Method and Lambda expression below:

The benefit of using the query syntax method is that the results are slightly easier to read and write, especially when expressions become more complex.

Query syntax-understanding the from and select clauses:

In C #, the syntax of each query expression starts from the from clause and ends with the select or group clause. The from clause indicates the data you want to query. The select clause indicates what data you want to return and what configuration should be returned.

For example, let's take a look at our query of the List <Person> set:

In the code snippet above, "from p in people" indicates that I want to perform a LINQ query on the set "people, I will use the parameter "p" to represent each item in the input sequence that I am querying. It is irrelevant to name the parameter "p". I can easily name it "o", "x ", "person" or any name I want.

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

If the Person object is not returned, I want to return the name of the Person in the set. I can rewrite the query to this:

Note that "select p" is not mentioned above, but "select p. FirstName ". This means that I don't want to return a string of Person objects, but want to return a string that is filled by the FirstName attribute of the Person object (this attribute is a string. Therefore, the result type of the query syntax expression is IEnumerable <string>.

Example of database query syntax

The beauty of LINQ is that I can use the same query syntax for any data type. For example, I can use the new LINQ to SQL Object Relational mappers provided by Orcas to model the SQL server's Northwind database, generate the following classes (watch the video here to learn how to implement it ):

After the class model is defined above (and its ing relationship with the database), I can write a query syntax expression to retrieve products with a unit price greater than 99 RMB:

In the above code snippet, I said that I want to perform a LINQ query on the Products TABLE OF THE NorthwindDataContext class. The NorthwindDataContext class is generated by the ORM designer of Visual Studio orcas. "Select p" indicates that I want to return a string of Product objects that match my query. Therefore, the result data type of the query syntax expression is IEnumerable <Product>.

Just like the previous List <Person> query syntax example, the C # compiler translates our declarative query syntax into explicit extension method calls (using Lambda expressions as parameters ). In the case of the above examples of SQL to LINQ, these Lambda expressions will be converted into SQL commands and then computed on the SQL Server (in this way, only the Product records that match the query conditions will be returned to our application ). The details about the mechanism that facilitates Lambda-> SQL conversion can be seen in the "Lambda Expression Tree" section of my Lambda expression blog post.

Query syntax-understand the where and orderby 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 operator to filter and convert the data you are querying. The two most common clauses are "where" and "orderby ". These two clauses filter and sort the result set.

For example, if you want to return a list of classification names sorted in descending alphabetical order from the Northwind database, the filter condition is that only the categories containing more than five products are included, we can write the following query syntax to query our database using LINQ to SQL:

In the above expression, we add the "where c. Products. Count> 5" clause to indicate that we only need to classify more than five Products. This utilizes the association of the orm ing between the database's middle class and the class to the SQL. In the above expression, I also added the "order by c. CategoryName descending" clause to indicate that I want to sort the result set by name in descending order.

Then, when using this expression to query the database, the following SQL statement 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: It is very clever to use SQL. Only a single field (category name) is returned, and all filtering and sorting are performed at the database layer, the query efficiency is very high.

Query syntax-use Projection to convert data

I previously pointed out that the "select" clause indicates the data to be returned,And what is the data structure?.

For example, if you have a "select p" clause like the following, where the p type is Person, then it will return a string of Person objects:

A very powerful function provided by LINQ and query syntax is to allow you to define new types separated from the queried data, then, a new type is used to control the shape and structure of the returned data.

For example, suppose we have defined a new AlternatePerson class, which contains a FullName attribute instead of the separated FirstName and LastName attributes in our original Person class:

Then I can use the following LINQ query syntax to query my original List <Person> set, and use the following query syntax to convert the result into a string of AlternatePerson objects:

Note: How do we use the "select" clause in the above expression, use the new object initializer syntax discussed in the first post of my language series to create a new AlternatePerson instance and set its attributes. Also note how I connect the FirstName and LastName attributes of our original Person class, and then assign them to the FullName attribute.

Use query syntax projection for Databases

This projection feature is incredibly useful when operating on the data retrieved from a remote data provider such as a database, because it provides us with an elegant way, which data fields should be retrieved from the database by our ORM.

For example, assume that I have used the ORM provider from LINQ to SQL to model the Northwind database and generate the following classes:

By writing the following LINQ query, I told the SQL to return a series of Product objects:

All fields required to fill the Product class will be returned from the database as part of the above query, and the raw SQL executed from LINQ to SQL orM looks like the following:

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 do not need or need to use all these fields. I can define a new MyProduct class as follows, and only have some attributes of the Product class, and an additional attribute that is not included in a Product class, TotalRevenue (NOTE: For those who are not familiar with C #, Decimal? Syntax indicates that our UnitPrice attribute is an nullable value ):

 

Then I can use the following query and use the Projection Function of the query syntax to construct the shape of the data I want to return from the database:

This indicates that, instead of returning a series of Product objects, I want MyProduct objects. as long as three of the attributes are assigned a value, the raw SQL statements to be executed are intelligently adjusted in the form of SQL, only the three required product fields are returned from the database:

Select [t0]. [ProductID], [t0]. [ProductName], [t0]. [UnitPrice]
FROM [dbo]. [Products] AS [t0]
Where [t0]. [UnitPrice]> 99

To show off, I can also fill in the fourth attribute of the MyProduct class, that is, the TotalRevenue attribute. I want this value to be equal to the total current sales of our products. This value does not exist in the Northwind database as a pre-calculated field. Instead, you need to associate the Products table with the Order Details table and calculate the total number of Order Detail rows corresponding to a given product.

It is really cool that I can use the Sum Extension Method of LINQ ON THE OrderDetails Association of the Product class to compile a multiplication Lambda expression that is part of my query syntax projection, to calculate the value:

The following SQL statement is used to perform operations in the SQL database by using the following method:

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-Deferred Execution and ToList () and ToArray ()

By default, the result type of the query syntax expression is IEnumerable <T>. In the preceding example, you will notice that all query syntax assignments are to IEnumerable <Product>, IEnumerable <string>, IEnumerable <Person>, IEnumerable <AlternatePerson>, and IEnumerable <MyProduct> variable.

A good feature of the IEnumerable <T> interface is that, the objects that implement them can delay the actual query operation to the first time that the developer attempts to iterate on the returned value (this is achieved by using the yield structure first introduced in C #2005 in VS 2.0.). Using this feature, the query syntax expressions and LINQ delay the actual query operation until the first time you loop the return value. If you do not iterate the results of IEnumerable <T>, the query will not be executed at all.

For example, consider the following example of writing data to SQL:

NoWhen we query the declaration of a syntax expression, we will access the database and retrieve the values required to fill the Category object only when we first try to loop the results (where the red arrow marks above.

The behavior results of this delayed operation become very useful because it facilitates a strong combination of multiple LINQ queries and expressions. For example, we can feed the result of an expression to another expression, and then use the delay operation to allow ORM like LINQ to SQL to optimize raw SQL based on the entire Expression Tree. I will demonstrate this scenario in a later blog post.

How to perform operations on the query syntax expression immediately

If you do not need to delay the query operations, but want to immediately execute the operations on them, you can use the built-in ToList () and ToArray () returns a List <T> or array containing the result set.

For example, to return a fan-type List <T> set:

To return an array:

In the preceding two cases, the database is accessed immediately and the Category object is filled.

Conclusion

The query syntax provides a convenient declarative and simplified syntax for expressing a query using the standard LINQ query operator. It provides high syntax readability and can be used for any type of data (in-memory collections, arrays, XML content, remote data providers such as databases, web services, etc). 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 new anonymous type features. Then I will discuss some very useful examples of using all these language features in practical applications (especially the examples of using LINQ for databases and XML files ).

I hope this article will help you,

Scott