Select/distinct and Count/sum/min/max/avg of LINQ to SQL statements

The previous article described LINQ, and by the way, the where operation, this begins with the LINQ to SQL statement, which allows you to understand from a statement's perspective that Linq,linq includes LINQ to Objects, LINQ to DataSets, LINQ to SQL, LINQ to Entities, LINQ to XML, but, in contrast, LINQ to SQL is used most in our programs, after all, all the data is running on the database in various operations. So the first to learn LINQ to SQL, the others are almost, then from the Select, this is the most common in writing programs. This article explains in detail the Select and Count/sum/min/max/avg.

Select/distinct operator

Application Scenario : O (∩_∩) o ... Query for Bai.

Description : Unlike the SELECT function in the SQL command, the SELECT and the clauses in the query expression are placed at the end of the expression and the variables in the sentence are returned;

SELECT/DISTINCT operations include 9 forms, namely simple usage, anonymous type form, conditional form, specified type form, filter form, shaping type form, nested type form, local method invocation form, distinct form.

1. Simple usage:

This example returns a sequence that contains only the name of the customer contact person.

var =     from inch db. Customers    select c.contactname;

Note: This statement is just a declaration or a description, and does not really take the data out, only when you need the data, it will execute the statement, which is deferred loading (deferred loading). If, at the time of declaration, the result set returned is the collection of objects. You can use the ToList () or ToArray () method to save the results of the query before querying the collection. Of course, lazy loading (deferred loading) can stitch the query syntax like a stitched SQL statement, and then execute it.

2. Anonymous type form:

Description: The anonymous type is a new feature in c#3.0. The essence of this is that the compiler automatically generates an anonymous class based on our customizations to help us implement the storage of temporary variables. Anonymous types also depend on another feature: support for creating objects based on property. For example, var d = new {name = "s"}; The compiler automatically generates an anonymous class with property called Name, then allocates memory by this type and initializes the object. but var d = new {"S"}; it is not compiled. Because the compiler does not know the name of the property in the anonymous class. For example, string c = "D"; var d = new {C}; It is possible to compile the. The compiler creates a property called the anonymous class with the name C.
For example, the following example: New{c,contactname,c.phone}; Both ContactName and phone define the property that corresponds to the field in the table in the mapping file. When the compiler reads the data and creates the object, an anonymous class is created that has two properties, ContactName and phone, and then initializes the object based on the data. In addition, the compiler can rename the property's name.

var =     from inch db. Customers    Select New {c.contactname, c.phone};

Above statement description: Returns a sequence with only the customer contact name and phone number using SELECT and anonymous types

var =     from inch db. Employees    Select  new    {        =++  e.lastname,         = E.homephone    };

The above statement describes: Use Select and anonymous types to return a sequence that contains only employee names and phone numbers, and merge the FirstName and LastName fields into a field "Name", and also rename the HomePhone field to phone in the resulting sequence.

var =     from inch db. Products    Select  new    {        p.productid,        =/2     };

The above statement describes: Use Select and anonymous types to return the IDs of all products and the sequence of Halfprice (set to the value of the product unit price divided by 2).

3. Condition form:

Description: The generated SQL statement is: case when condition then else.

var =     from inch db. Products    Select  new    {        p.productname,         =          - < 0  ?           of Stock ":" in stock "    };

The above statement describes: use SELECT and conditional statements to return a sequence of product names and product availability status.

4. Specify the type form:

Description: This form returns the set of objects of your custom type.

var =     from inch db. Employees    Select  new Name    {        =  e.firstname,        =  e.lastname    };

The above statement describes: Returns a sequence of employee names using select and known types.

5. Filter form:

Description: Combine where to use, play the role of filtering.

var =     from inch db. Customers    where= =  "London    "select c.contactname;

The above statement describes: use Select and where to return a sequence with only the London customer contact name.

6.shaped form (shaping type):

Description: Its select operation uses an anonymous object, and in this anonymous object, its properties are an anonymous object.

var =     from inch db. Customers    Select  new {        C.customerid,        =  New {c.companyname, C. City, C.country},        =  new {c.contactname, c.contacttitle}    };

Statement Description: Uses select and anonymous types to return an shaping subset of the data about the customer. Check the customer ID and company information (company name, city, country) and contact information (contacts and positions).

7. Nested type form:

Description: Returns each object in the set of objects in the Discountedproducts property, and also contains a collection. That is, each object is also a collection class.

 var  q =  from  o in   db.        Orders  select   new {O.orderid, Discountedproducts  =  from  od Span style= "color: #808080;" >in   O.orderdetails  where  od.            Discount >  0.0  select   OD, freeshippingdiscount  =   O.freight};  

Statement Description: Use a nested query to return the sequence of all orders and their OrderID, the sub-sequences of items in a discounted order, and the amount saved by free shipping.

8. Local method invocation form (Localmethodcall):

This example calls the local method Phonenumberconverter in the query to convert the phone number to an international format.

var =  from inch db. Customers         where= =| | == "USA"          Select New         {             C.customerid,             c.companyname,             =  c.phone,             =               Phonenumberconverter (C.country, C.phone)         };

The Phonenumberconverter method is as follows:

 Publicstring Phonenumberconverter (String country, string phone) {phone=Phone.Replace(" ", "").Replace(")", ")-"); Switch (country) { Case"USA":return"1-"+Phone;  Case"UK":return" --"+Phone; default:            returnPhone; }}

The following also uses this method to convert phone numbers to international format and create XDocument

= New XDocument (    from in  db. Customers              where= =| | == "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 form:

Description: Filters for values that are not the same in the field. Used to query for a result set that is not duplicated. The generated SQL statement is: SELECT DISTINCT [City] from [Customers]

var = (    from in  db.) Customers    Select  c.city)    . Distinct ();

Statement Description: Query the country where the customer is covered.

Count/sum/min/max/avg operator

applicable Scenarios : statistics, such as the number of statistical data, sum, Minimum, maximum, average.

Count

Description : Returns the number of elements in the collection, returns an int type, and no delay. The generated SQL statement is: SELECT COUNT (*) from

1. Simple form:

Get the number of customers in the database:

var = db. Customers. Count ();

2. With conditional form:

Get the number of non-broken products in the database:

var = db. Products. Count = !p.discontinued);

LongCount

Description : Returns the number of elements in the collection, returning a long type; For a collection with a large number of elements, the longcount can be used to count the number of elements, which returns a long type and is more accurate. The generated SQL statement is: SELECT COUNT_BIG (*) from

var = db. Customers.longcount ();

Sum

Description : Returns the sum of the numeric type elements in the collection, which should be of type int, without delay. The generated SQL statement is: SELECT SUM (...) From

1. Simple form:

Get total shipping for all orders:

var = db. Orders. Select = o.freight). Sum ();

2. Mapping form:

Get the total number of orders for all products:

var = db. Products. Sum = p.unitsonorder);

Min

Description : Returns the minimum value of an element in the collection; The generated SQL statement is: SELECT MIN (...) From

1. Simple form:

Find the lowest unit price for any product:

var = db. Products. Select = p.unitprice). Min ();

2. Mapping form:

Find the lowest shipping cost for any order:

var = db. Orders. Min = o.freight);

3. Elements:

Find the product with the lowest unit price in each category:

varCategories=     fromPinchdb. ProductsGroupP byP.categoryid intogSelectNew {CategoryID=G.Key, Cheapestproducts=             fromP2inchgwhereP2. UnitPrice==G.Min(P3=P3. UnitPrice)SelectP2};

Max

Description : Returns the maximum value of an element in the collection; The generated SQL statement is: SELECT MAX (...) From

1. Simple form:

Find the most recent hire date for any employee:

var = db. Employees. Select = e.hiredate). Max ();

2. Mapping form:

Find the maximum amount of inventory for any product:

var = db. Products. Max = p.unitsinstock);

3. Elements:

Find the product with the highest unit price in each category:

varCategories=     fromPinchdb. ProductsGroupP byP.categoryid intogSelectnew {G.Key, Mostexpensiveproducts=             fromP2inchgwhereP2. UnitPrice==G.Max(P3=P3. UnitPrice)SelectP2};

Average

Description : Returns the average of a numeric type element in a collection. The collection should be a collection of numeric types whose return value type is double; The generated SQL statement is: SELECT AVG (...) From

1. Simple form:

Get the average shipping cost for all orders:

var = db. Orders. Select = o.freight). Average ();

2. Mapping form:

Get the average unit price for all products:

var = = p.unitprice);

3. Elements:

Find products in each category that have a unit price higher than the average of that category:

varCategories=     fromPinchdb. ProductsGroupP byP.categoryid intogSelectnew {G.Key, Expensiveproducts=             fromP2inchgwhereP2. UnitPrice>G.average (P3=P3. UnitPrice)SelectP2};

Aggregate

description : Gets the aggregated value according to the input expression, without delay. That is, a seed value is compared with the current element through the specified function to iterate through the elements in the collection, and the eligible elements remain. If you do not specify a seed value, the seed value defaults to the first element of the collection.

Here is a table summarizing the LINQ to SQL statement.

Where	To filter or delay
Select	To select or delay
Distinct	Query a result set that is not duplicated;
Count	Returns the number of elements in the collection, returns an int type;
LongCount	Returns the number of elements in the collection, returning a long type;
Sum	Returns the sum of the numeric type elements in the collection, which should be of type int;
Min	Returns the minimum value of an element in the collection;
Max	Returns the maximum value of an element in the collection;
Average	Returns the average of a numeric type element in a collection. The collection should be a collection of numeric types with a return value type of double;
Aggregate	Gets the aggregated value based on the input expression, without delay

Via:http://www.cnblogs.com/lyj/archive/2008/01/23/1049686.html

Select/distinct and Count/sum/min/max/avg of LINQ to SQL statements