LINQ to SQL statements select/distinct and COUNT/SUM/MIN/MAX/AVG (GO)

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 q =
    from c in 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 q =
    from c in 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 q =
    from e in db.Employees
    select new
    {
        Name = e.FirstName + " " + e.LastName,
        Phone = 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 q =
    from p in db.Products
    select new
    {
        p.ProductID,
        Half

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 q =
    from p in db.Products
    select new
    {
        p.ProductName,
        Availability =
        p.UnitsInStock - p.UnitsOnOrder < 0 ? 
        "Out 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 q =
    from e in db.Employees
    select new Name
    {
        FirstName = e.FirstName,
        LastName = 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 q =
    from c in db.Customers
    where c.City == "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 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: 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 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 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)
         };

The Phonenumberconverter method is as follows:

 
public string PhoneNumberConverter(string Country, string Phone)
{
    Phone = Phone.Replace(" ", "").Replace(")", ")-");
    switch (Country)
    {
        case "USA":
            return "1-" + Phone;
        case "UK":
            return "44-" + Phone;
        default:
            return Phone;
    }
}

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

 
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 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 q = (
    from c 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 q = db. Customers.count ();

2. With conditional form:

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

var q = db. Products.count (p =!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 q = 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 q = db. Orders.select (o = o.freight). Sum ();

2. Mapping form:

Get the total number of orders for all products:

var q = db. Products.sum (p = 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 q = db. Products.select (p = p.unitprice). Min ();

2. Mapping form:

Find the lowest shipping cost for any order:

var q = db. Orders.min (o = o.freight);

3. Elements:

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

 
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 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 q = db. Employees.select (e = e.hiredate). Max ();

2. Mapping form:

Find the maximum amount of inventory for any product:

var q = db. Products.max (p = p.unitsinstock);

3. Elements:

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

 
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 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 q = db. Orders.select (o = o.freight). Average ();

2. Mapping form:

Get the average unit price for all products:

var q = db. Products.average (p = p.unitprice);

3. Elements:

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

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

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

LINQ to SQL statements select/distinct and COUNT/SUM/MIN/MAX/AVG (GO)