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

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.