Select/Distinct/Count/Sum/Min/Max/Avg

Select/Distinct Operator

Description: Similar to the select statement in SQL commands, the select statement and the clause in the query expression are placed at the end of the expression, and the variables in the clause, that is, the results are returned. delay.

Select/Distinct operations include nine forms, they are simple form, anonymous type form, conditional form, specified type form, filter type form, shaped type form, nested type form, LocalMethodCall form, and Distinct form.

1. Simple Form:

var
 q =
  from
 c in
 db.Customers
  select
 c.ContactName;

Note: This statement is only a declaration or description and does not really extract the data. It will only execute this statement when you need the data, this is deferred loading ). If the returned result set is a set of objects during declaration. You can use the ToList () or ToArray () method to save the query results first and then query the set. Of course, the delayed loading (deferred loading) can splice the query syntax as the concatenated SQL statement, and then execute it.

2. Anonymous type:

Note: The anonymous type is a new feature in C #3.0. The essence is that the compiler automatically generates an anonymous Class Based on our customization to help us store temporary variables. The anonymous type also depends on another feature: objects can be created based on the property. For example, var d = new {Name = "400 phone"}; the compiler automatically generates an anonymous class with a property called Name, allocates the memory according to this type, and initializes the object. But var d = new {"400 phone"}; is not compiled. The compiler does not know the name of the property in the anonymous class. For example, string c = "http://www.my400800.cn"; var d = new {c}; can be compiled. The compiler will create a property called Anonymous class with c.
For example, in the following example: new {c, ContactName, c. Phone}; both ContactName and Phone define the property corresponding to the field in the table in the ing file. When the compiler reads data and creates an object, it creates an anonymous class which has two attributes: ContactName and Phone, and then initializes the object based on the data. In addition, the compiler can rename the property name.

var
 q =
      from
 c in
 db.Customers
      select
new
 {c.ContactName, c.Phone};

Statement Description: query the contact person and phone number of the customer.

var
 q =
      from
 e in
 db.Employees
      select
new
 {Name = e.FirstName + " "
 + e.LastName, Phone = e.HomePhone};

Statement Description: query the employee's name and home phone number.

var
 q =
      from
 p in
 db.Products
      select
new
 {p.ProductID, HalfPrice = p.UnitPrice / 2};

3. Conditional form:

Note: The SQL statement is case when condition then else.

var
 q =
      from
 p in
 db.Products
      select
new
 {p.ProductName, Availability = p.UnitsInStock - p.UnitsOnOrder < 0 ? "Out Of Stock"
: "In Stock"
};

4. specify the type format:

Description: This form returns your custom object set.

var
 q =
      from
 e in
 db.Employees
      select
new
Name
 {FirstName = e.FirstName, LastName = e.LastName};

5. Filter Type format:

Note: It can be used in combination with where to filter data.

var
 q =
      from
 c in
 db.Customers
      where
 c.City == "London"
 
      select
 c.ContactName;

6. shaped Type format:

Note: The select Operation uses anonymous objects, and the attributes of this anonymous object are also anonymous objects.

var
 q =
      from
 c in
 db.Customers
      select
new
 {
        c.CustomerID,
        CompanyInfo = new
 {c.CompanyName, c.City, c.Country},
        ContactInfo = new
 {c.ContactName, c.ContactTitle}
    };

Statement Description: query the customer's ID and company information (company name, city, country), and contact information (contacts and positions ).

7. nested Type format:

Note: Each object in the returned object set contains a set of DiscountedProducts attributes. That is, each object is also a collection class.

var
 q =
      from
 o in
 db.Orders
      select
new
 {
        o.OrderID,
        DiscountedProducts =
          from
 od in
 o.OrderDetails
          where
 od.Discount > 0.0
          select
 od,
        FreeShippingDiscount = o.Freight
    };

8. LocalMethodCall format:

var
 q = from
 c in
 db.Customers
      where
 c.Country == "UK"
 || c.Country == "USA"
 
      select
new
 { c.CustomerID, c.CompanyName, Phone = c.Phone, InternationalPhone =
PhoneNumberConverter(c.Country, c.Phone) };

 

XDocument
 doc = new
XDocument
(
      new
XElement
("Customers"
, from
 c in
 db.Customers
                where
 c.Country == "UK"
 || c.Country == "USA"
 
                select
 (new
XElement
("Customer"
,
                  new
XAttribute
("CustomerID"
, c.CustomerID),
                  new
XAttribute
("CompanyName"
, c.CompanyName),
                  new
XAttribute
("InterationalPhone"
, PhoneNumberConverter(c.Country, c.Phone))
                  ))));

9. Distinct format:

Note: The filter fields have different values. Used to query non-repeated result sets. The SQL statement is: SELECT DISTINCT [City] FROM [MERs]

var
 q = (
      from
 c in
 db.Customers
      select
 c.City )
      .Distinct();

Statement Description: query the countries covered by customers.

Count/Sum/Min/Max/Avg Operator

 

Applicable scenarios: Statistical data, such as the number, sum, minimum, maximum, and average of some data.

Count

Description: Returns the number of elements in the Set, INT type; no delay. The SQL statement is: SELECT COUNT (*) FROM

1. Simple Form:

var
 q = db.Customers.Count();

2. Conditional form:

var
 q = db.Products.Count(p => !p.Discontinued);

LongCount

Description: Returns the number of elements in the set. The return value belongs to the LONG type. No delay is required. LongCount can be used to count the number of elements in a set with a large number of elements. It returns the long type and is accurate. The SQL statement is: SELECT COUNT_BIG (*) FROM

var
 q = db.Customers.LongCount();

Sum

Description: Returns the sum of numeric elements in the set. The set must be an INT type set without delay. The SQL statement is: SELECT SUM (...) FROM

1. Simple Form:

var
 q = db.Orders.Select(o => o.Freight).Sum();

2. ing format:

var
 q = db.Products.Sum(p => p.UnitsOnOrder);

Min

Description: Returns the minimum value of an element in a set without delay. The SQL statement is: SELECT MIN (...) FROM

1. Simple Form:

var
 q = db.Products.Select(p => p.UnitPrice).Min();

2. ing format:

var
 q = db.Orders.Min(o => o.Freight);

3. Principle:

var
 categories =
      from
 p in
 db.Products
      group
 p by
 p.CategoryID into
 g
      select
new
 {
           CategoryID = g.Key,
           CheapestProducts =
               from
 p2 in
 g
               where
 p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
               select
 p2
      };

 

 

Max

Description: Returns the maximum number of elements in a set without delay. The SQL statement is: SELECT MAX (...) FROM

1. Simple Form:

var
 q = db.Employees.Select(e => e.HireDate).Max();

2. ing format:

var
 q = db.Products.Max(p => p.UnitsInStock);

3. Principle:

var
 categories =
      from
 p in
 db.Products
      group
 p by
 p.CategoryID into
 g
      select
new
 {
           g.Key,
           MostExpensiveProducts =
               from
 p2 in
 g
               where
 p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
               select
 p2
      };

Average

Description: Returns the average value of the value type element in the set. The set should be a set of numeric types, and its return value type is double; no delay. The SQL statement is: SELECT AVG (...) FROM

1. Simple Form:

var
 q = db.Orders.Select(o => o.Freight).Average();

2. ing format:

var
 q = db.Products.Average(p => p.UnitPrice);

3. Principle:

var
 categories =
      from
 p in
 db.Products
      group
 p by
 p.CategoryID into
 g
      select
new
 {
           g.Key,
           ExpensiveProducts =
               from
 p2 in
 g
               where
 p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
               select
 p2
      };

Aggregate

Description: Obtain the aggregate value based on the input expression without delay. That is to say, a seed value is used to compare with the current element through the specified function to traverse the elements in the set, and the elements that meet the conditions are retained. If no seed value is specified, the seed value is the first element of the set by default.

The following is a summary of the mentioned LINQ statement in a table.

 

Where	Filtering; latency
Select	Select; latency
Distinct	Query Result sets that are not repeated; Delay
Count	Returns the number of elements in the Set, INT type; no delay
LongCount	Returns the number of elements in the Set and the LONG type. No delay is required.
Sum	Returns the sum of numeric elements in the set. The set must be an INT type set without delay.
Min	Returns the minimum value of an element in a set without delay.
Max	Returns the maximum number of elements in a set without delay.
Average	Returns the average value of the value type element in the set. The set should be a set of numeric types, and its return value type is double; no delay
Aggregate	Obtains the aggregate value based on the input expression without delay.