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. |