ASP. NET 3.5 core programming learning notes (21): LINQ query syntax

Essentially, LINQ-to-SQL can map classes and methods to data source objects.

Overview

Most applications are centered on a data warehouse. Over the years, architects have been modeling problem domains through objects while designing applications. These objects include connections used to connect to the data access layer to interact with the database and establish a relational mode through the object model. However, this method is cumbersome for simple applications.

To meet the needs of these simple applications, the emergence of LINQ provides a set of powerful and easy-to-use tools to operate data storage on a higher conceptual layer.

The Programming Language in. Net 3.5 can infer local types. The VaR keyword is introduced for this purpose. Essentially, it allows us to declare a variable without specifying the implementation type. The compiler will deduce its actual type based on the value assigned to the variable. Type inference does not involve operations such as packing and unpacking. All work is done by the compiler, that is, before the code is executed. The compiler recognizes the actual type we use and declares the variable in a specific, type-safe manner in the intermediate language code. Partial type inference is limited to local variables. We cannot declare the member variable type in this way, nor use it in the method prototype.

LINQ-to-SQL is an O/R implementation that can be used to model the SQL Server database through the. NET class. The LINQ-to-SQL framework is for SQL Server and cannot be used to process other relational databases.

General query syntax

You can query and update supported data sources by using a linq expression. All the variables in the LINQ expression are strongly typed.

There are two ways to express the LINQ operation. One is to express the query based on the new language structure (such as from, select, and where), and the other is to call the LAMBDA Method. Note: All Query expressions are mapped to methods during compilation. The two methods have the same function and efficiency. For example:

VaR DATA = from C in dB. MERs
Where C. Country = "Spain"
Select C;
// Equivalent
VaR DATA = dB. MERs. Where (C => C. Country = "Spain ");

In. NET 2.0, the managed language provides a powerful structure, namely, the anonymous method. You do not need to use the naming method in the delegate. You can define an anonymous method at any location and access external variables within the anonymous method. The type of the returned value can be inferred. This method reduces the number of private methods in the class. Lambda expressions are further extensions of anonymous methods and are more refined.

Projection Operator

The select operator is used to project the content in the data source to the memory, which is often used with the from keyword.

The difference between select and selectmany is that the former returns the hierarchical results of the object and the latter sequential results. For example, the relationship between customers and orders is: Customers stores customer information, and orders stores customer order information. What will be returned by the following code?

var data = from c in customers
where c.Country == "Spain"
               select (c.Orders);

For customers that meet the conditions, it will return the corresponding object sequence, each object is an array of order objects. Let's look at the following code:

var data= (from c in customers
where c.Country == "Spain"
               select c).SelectMany(c => c.Orders);

In this case, we will get a sequence, and the customer ID information that establishes a one-to-many relationship will repeat.

Join and group

The join operator can join the two sets using the matching keys:

var data = from c in customers
               join o in orders
                    on c.CustomerID equals o.CustomerID
               select new {c.Name, o.OrderNumber};

Different from the SQL Language, The on Clause of join distinguishes the order of items. The first clause must come from the outer sequence, and the second (right side of the equals operator) must come from the inner sequence. Otherwise, A compilation error occurs.

The join operator traverses the external layer set (in the preceding example, MERS mers) elements and adds new elements to the result set for elements that meet the conditions in the joined set (orders in the preceding example.

This clause also supports other join behaviors, such as outer join. Specifically, in the outer join result table, the outer element will return even if it does not match the inner element.

Sample Code:

var data = from c in customers
           join o in(
                        from orderInJan97 inorders
where orderInJan97.OrderDate.Value.Year == 1997 &&
                              orderInJan97.ValueDate.Value.Month == 1
                        select orderInJan97                    )
           on c.CustomerID equals o.CustomerID into groupOrders
           select new { c.CompanyName, OrderTotal = groupOrders.Count() };

The MERs set is connected to the order subset in January 1997. Orders of each customer group are temporarily stored in the grouporders container. If no matching key is found in a group, the group is empty. Each record in the result table corresponds to a customer, with the total number of orders of the customer in a given period of time.

The Group clause returns the sequence of group objects. The Group object may be empty. However, if the group object contains data items, the key values of these data items match the key values of the group. Therefore, the output of grouping operations cannot be directly bound to table-type data binding controls (such as the gridview ).

var data = from c in customers
               group c.ContactName by
new { City = c.City, Region = c.Region } into g
where g.Count() > 1
               select g

In this example, we group the customer Cities and Regions and add the results to the contact name set. In addition, we only select customers with multiple contacts in each target city/region. Note: To group multiple attributes, you must use the anonymous type (new operator ). Once a group is created, if you want to apply other constraints to the group operation (like the having clause in the T-SQL), you only need to express the corresponding conditions in the by clause. In the following example, only records whose city names contain more than 5 characters are concerned:

var data = from c in customers
           group c.ContactName by
new { City = c.City, Length = c.City.Length > 5 } into g
where g.Count() > 1
           select g;

Note that any statements after the by clause are processed one by one. The above code is completely different from the following code:

var data = from c in customers
           group c.ContactName by
new { City = c.City.Length > 5 } into g
where g.Count() > 1
           select g;

In this case, the condition must be met that the number of characters in the city name is greater than 5, but the matching items in the city name are not checked. Therefore, contacts in Rome will be ignored, and contacts in London and New York will be grouped into one group.

Aggregation

Many operators can aggregate objects and perform pre-defined calculations. LINQ provides common operators (such as Count, sum, average, Min, and Max ).

Sum example:

var data = from od indataContext.Order_Details
where od.OrderID == 10250
           select new{
　　　　　　 　　　　　　　od.OrderID,
　　　　　　　 　　　　　　OrderAmount = Sum(od.Quantity * od.UnitPrice)
                     };

Partition

We can use the take operator to retrieve the connection elements of the specified data from a sequence. As follows:

var data = (from c in customers
            select c).Take(10);

In this example, only the first 10 objects in the MERs set are obtained. The take operator is not a built-in language structure. It is a method located in the class that represents the query projection.

You can also use the Skip operator to skip the continuous element of the specified data starting with a sequence. As follows:

var data = (from c in customers
            select c).Skip(30).Take(10);

This method can be used for paging queries.

Method for Determining a single element

LINQ provides the first and single operators to return each element and a single element that meets the conditions respectively.

First example:

var data = (from c in dataContext.Customers
                 join o in (from t in dataContext.Orders
where t.OrderDate.Value.Year == 1998 &&
                                        t.OrderDate.Value.Month == 5
                               select t)
                on c.CustomerID equals o.CustomerID
                select new { Company = c.CompanyName,
                                  Country = c.Country,
                                  OrderDate = o.OrderDate }
                 ).First(x => x.Country == "USA");

This query limits the result list to the first American customer to place an order within the specified time. If the first query result is null, an exception is thrown. To avoid exceptions, use the firstordefault operator instead. If no matching record is found, null is returned.

Whether there is one or more objects that meet the conditions, the first operator can work normally. The single operator only allows one object that meets the conditions to exist. Otherwise, an exception is thrown. The singleordefault operator ensures that no exception is thrown when the result is null. However, if multiple elements meet the conditions, an invalid operation exception is still thrown.