Count/sum/min/max/avg of LINQ to SQL statements (3)

Source: Internet
Author: User

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.

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.