[Original] Notes for learning from LINQ to SQL (3) -- aggregate function (1)

Document directory

• (1) No parameter form
• (2) Parameters
• (1) No parameter form
• (2) Parameters
• (1) No parameter form
• (2) Parameters
• (1) No parameter form
• (2) Parameters
• (1) No parameter form
• (2) Parameters

This article describes the use of the five Aggregate functions, namely Count, Sum, Min, Max, and Avg. These functions are used in the same scenario as SQL commands, are used for statistics, Count, sum, minimum, maximum, and average.

1. Count Function

The Count function is used to obtain the number of elements in the set. The return value type is int. The SQL statement is as follows:

SELECT COUNT(*) FROM TABLENAME

The Count function can also be used in two ways: No parameter form and no parameter form.

(1) No parameter form

We can obtain the number of all objects in the set without parameters, for example:

int count = db.Students.Count();

The above statement will obtain the total number of all students.

(2) Parameters

The parameter form is equivalent to performing an additional Filter Based on the parameter conditions, and then returning the number of objects in the filtered result set. For example:

int count = db.Students.Count(stu=>stu.Age>20);

The preceding statement returns the total number of students older than 20.

Supplement:

In addition to using the Count function to obtain the number of elements in the set, we can also use the LongCount function, which differs from Count in that its return value type is long, used when the number of elements in a collection is relatively large.

2. Sum Function

The Sum function is used to obtain the Sum of numeric elements in a set. The corresponding SQL statement is as follows:

SELECT SUM(COLNAME,...) FROM TABLENAME

The Sum function can be used in two ways: No parameter form and no parameter form.

(1) No parameter form

To use the form without parameters, you must first determine the column for summation calculation, and then call the Sum function. For example:

var sum = db.Students.Select(stu=>stu.Age).Sum();

The preceding statement gets the total age of all students (this example has no practical significance in actual application ).

(2) Parameters

You can use the parameter form to determine the column to be summed in the parameter. For example:

var sum = db.Students.Sum(stu=>stu.Age);

The preceding statement can also obtain the total age of all students.

3. Min Functions

The Min function is used to obtain the minimum value of elements in a set, similar to an SQL statement:

SELECT MIN(COLNAME...) FROM TABLENAME

Min functions are also used in two ways: No parameter form and no parameter form.

(1) No parameter form

To use the parameter-free form, you must first determine the column for minimum value and then call the Min function. For example:

var min = db.Students.Select(stu=>stu.Age).Min();

The preceding statement obtains the minimum age of all students.

(2) Parameters

You can use the parameter form to determine the column for minimum value in the parameter. For example:

var min = db.Students.Min(stu=>stu.Age);

The preceding statement can also obtain the minimum age of all students.

4. Max Functions

The Max function is used to obtain the maximum value of elements in a set, similar to an SQL statement:

SELECT MAX(COLNAME...) FROM TABLENAME

There are two methods to use the Max function: No parameter form and no parameter form.

(1) No parameter form

To use the parameter-free form, you must first determine the column to calculate the maximum value, and then call the Max function. For example:

var max = db.Students.Select(stu=>stu.Age).Max();

The above statement gets the maximum age of all students.

(2) Parameters

You can use the parameter form to determine the column for maximum value calculation in the parameter. For example:

var max = db.Students.Max(stu=>stu.Age);

The above statement can also obtain the maximum age of all students.

5. Avg Functions

The Avg function is used to obtain the average value of elements in a set. The return value type is double, similar to the SQL statement:

SELECT AVG(COLNAME...) FROM TABLENAME

Avg functions can also be used in two ways: No parameter form and no parameter form.

(1) No parameter form

You must first determine the column for average calculation and then call the Avg function. For example:

var avg = db.Students.Select(stu=>stu.Age).Avg();

The above statement gets the average age of all students.

(2) Parameters

You can use the parameter form to determine the columns to calculate the average value in the parameter. For example:

var avg = db.Students.Avg(stu=>stu.Age);

The preceding statement can also obtain the average age of all students.

 

To sum up, this article mainly introduces the use of five Aggregate functions, and finds that they are basically the same in use form and are very similar to SQL commands and easy to use.