LINQ to SQL Count/sum/min/max/avg Join

public class Linq
{
Mxsicedatacontext Db = new Mxsicedatacontext ();

LINQ to SQL
Count/sum/min/max/avg

Count

public void 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

Poor performance in simple form

var count = Db.MXSMemeber.Count ();

With conditional form

var count1 = Db.MXSMemeber.Count (a = bool). Parse (A.uid));

LongCount
Description: Returns the number of elements in the collection, returning a LONG type; For collections with a large number of elements visible
You can use LongCount to count the number of elements, which returns a long type, which is more accurate. The generated SQL statement is:
SELECT COUNT_BIG (*) from

var longcount = Db.MXSMemeber.LongCount ();

}

SUM
public void SUM ()
{
A simple form, too.
///
var sum = Db.MXSMemeber.Select (A = = A.sex). Sum ();

Mapping form
///
var sum1 = Db.MXSMemeber.Sum (a = decimal. Parse (A.uid));

}

Min
public void Min ()
{
Description: Returns the minimum value of an element in the collection; The generated SQL statement is: SELECT MIN (?) From
Simple Form
var min = Db.MXSMemeber.Select (a = a.uid). Min ();

Mapping form

var min1 = Db.MXSMemeber.Min (a = A.uid);

}

Max
public void Max ()
{
Description: Returns the maximum value of an element in the collection; The generated SQL statement is: SELECT MAX (?) From
Simple Form
var max = Db.MXSMemeber.Select (a = a.uid). Max ();

Mapping form:

var max1 = Db.MXSMemeber.Max (a = A.uid);
}

Average

public void 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; no delay. The generated SQL statement is: SELECT AVG (?) From
Simple Form
var Average = Db.MXSMemeber.Select (a = decimal. Parse (A.uid)). Average ();

Mapping form
var Average1 = Db.MXSMemeber.Average (a = decimal. Parse (A.uid));

}

Join

Application scenario: In our table relationship there is a relationship, a one-to-many relationship, many-to-many relationships, and so on. For each table, the

Relationships, you can use these to implement operations on multiple tables.

Description: In the join operation, join (join query), SelectMany (select-to-Multiple selection) and GroupJoin (sub-

Group Join query).

The extension method inner JOIN operation on elements that match the keys in two sequences

SelectMany

Description: When we write a query statement, we need to meet 2 conditions if translated into SelectMany. 1: Query statement

There are no joins and into,2: EntitySet must appear. There is a one-to-many relationship in our table relationship,

Many-to-many relationships, as described below.

1. One-to-many relationship (1 to many):

var q =

From C in Db.customers

From O in C.orders

where c.city = = "London"

Select O;

Statement Description: Customers is a one-to-many relationship with orders. That is, orders in the Customers class with the EntitySet shape

The type appears. So the second from IS from C. Orders, rather than db.orders, are screened. This example is in the from sub-

Use foreign key navigation in the sentence to select all orders for London customers.

var q =

From P in db.products

where p.supplier.country = = "USA" && P.unitsinstock = = 0

Select P;

Statement Description: This sentence uses the p.supplier.country condition and indirectly associates the Supplier table. This example is in the where

Use foreign key navigation in the clause to filter the products whose suppliers are in the United States and are out of stock. The generated SQL statement is:

SELECT [T0]. [ProductID], [t0]. [ProductName], [t0]. [SupplierID],

[T0]. [Categoryid],[t0]. [Quantityperunit],[t0]. [UnitPrice],

[T0]. [UnitsInStock], [t0]. [Unitsonorder],[t0]. [ReorderLevel],

[T0]. [Discontinued] from [dbo]. [Products] As [t0]

Left OUTER JOIN [dbo]. [Suppliers] As [T1] on

[T1]. [SupplierID] = [T0]. [SupplierID]

WHERE ([t1].[ Country] = @p0) and ([t0].[ UnitsInStock] = @p1)

--@p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]

--@p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

2. Many-to-many relationships (many to many):

var q =

From E in Db.employees

From ET in e.employeeterritories

where e.city = = "Seattle"

Select New

{

E.firstname,

E.lastname,

Et. Territory.territorydescription

};

Description: A Many-to-many relationship typically involves three tables (if one table is self-correlating, it is possible that there are only 2 tables).

This sentence involves employees, employeeterritories, territories three tables. Their relationship is 1:m:1.

Employees and territories do not have a very clear relationship.

Statement Description: This example uses foreign key navigation in the FROM clause to filter the employees in the Seattle, listing their

Area. This generated SQL statement is:

SELECT [T0]. [FirstName], [t0]. [LastName], [T2]. [Territorydescription]

from [dbo]. [Employees] As [t0] cross JOIN [dbo]. [Employeeterritories]

As [T1] INNER JOIN [dbo]. [Territories] As [T2] on

[T2]. [TerritoryID] = [T1]. [TerritoryID]

WHERE ([t0].[ City] = @p0) and ([t1].[ EmployeeID] = [T0]. [EmployeeID])

--@p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Seattle]

3. Self-Join relationship:

var q =

From E1 in Db.employees

From E2 in E1. Employees

where E1. City = = E2. City

Select New {

FirstName1 = E1. FirstName, LastName1 = E1. LastName,

FirstName2 = E2. FirstName, LastName2 = E2. LastName,

E1. City

};

Statement Description: This example uses foreign key navigation to filter paired employees in the SELECT clause, one employee per pair

To another employee, and two employees are from the same city. The generated SQL statement is:

SELECT [T0]. [FirstName] As [FirstName1], [t0]. [LastName] As

[Lastname1],[t1]. [FirstName] As [FirstName2], [T1]. [LastName] As

[Lastname2],[t0]. [City] from [dbo]. [Employees] As [T0],

[dbo]. [Employees] As [T1] WHERE ([t0].[ City] = [T1]. [City]) and

([T1]. [ReportsTo] = [T0]. [EmployeeID])

GroupJoin

As stated above, without joins and into, translated into SelectMany, with joins and into, it is

Translated into GroupJoin. The idea here is to rename the results.

1. Bidirectional join (Two way join):

This example explicitly joins two tables and projects the results from both tables:

var q =

From C in Db.customers

Join O in Db.orders on C.customerid

equals O.customerid into Orders

Select New

{

C.contactname,

OrderCount = orders. Count ()

};

Note: In a one-to-many relationship, the left side is 1, each record is C (from C in db.customers), the right is many,

Each of its records is called O (Join O in db.orders), each corresponding to the left of a C, there will be a set O, that group O,

is called orders, that is, we name a group of O orders, which is the into use. This is why

In the SELECT statement, orders can call the aggregate function count. In T-SQL, use the nested T-SQL return

Return value as the field value. ：

The generated SQL statement is:

SELECT [T0]. [ContactName], (

SELECT COUNT (*)

from [dbo]. [Orders] As [T1]

WHERE [T0]. [CustomerID] = [T1]. [CustomerID]

) as [OrderCount]

from [dbo]. [Customers] As [t0]

2. Three-to-join (there):

This example explicitly joins three tables and projects the results from each table individually:

var q =

From C in Db.customers

Join O in Db.orders on C.customerid

equals O.customerid into Ords

Join E in Db.employees on c.city

equals e.city into Emps

Select New

{

C.contactname,

ords = ords. Count (),

Emps = Emps. Count ()

};

The generated SQL statement is:

SELECT [T0]. [ContactName], (

SELECT COUNT (*)

from [dbo]. [Orders] As [T1]

WHERE [T0]. [CustomerID] = [T1]. [CustomerID]

) as [Ords], (

SELECT COUNT (*)

from [dbo]. [Employees] As [T2]

WHERE [T0]. [City] = [T2]. [City]

) as [Emps]

from [dbo]. [Customers] As [t0]

3. Left OUTER join (Outer join)

This sample demonstrates how to get a left outer join by using DefaultIfEmpty () by using this example. In

When an employee does not have an order, the DefaultIfEmpty () method returns null:

var q =

From E in Db.employees

Join O in db.orders on e equals o.employee into Ords

From O in Ords. DefaultIfEmpty ()

Select New

{

E.firstname,

E.lastname,

Order = O

};

Description: A null value is populated with the employees left table, the orders right table, and the Orders table as empty. Result of Join

Rename Ords and use the DefaultIfEmpty () function to query it again. There is an Order in the final result, because

From O in Ords. DefaultIfEmpty () is another traversal of the Ords group, so the order in the final result is not

A collection. However, if there is no from O in Ords. DefaultIfEmpty () This sentence, the last SELECT statement is written in select

New {e.firstname, e.lastname, order = ords}, then order is a collection.

4. Let assignment of the projection (projected let assignment):

Description: The Let statement is renamed. Let is located between the first from and select statements.

This example projects a final "let" expression from a join:

var q =

From C in Db.customers

Join O in Db.orders on C.customerid

equals O.customerid into Ords

Let z = c.city + c.country

From O in Ords

Select New

{

C.contactname,

O.orderid,

Z

};

5. Key combination (Composite key):

This example shows a join with a key combination:

var q =

From O in Db.orders

From P in db.products

Join D in Db.orderdetails

On new

{

O.orderid,

P.productid

} equals

New

{

D.orderid,

D.productid

}

into details

From D in details

Select New

{

O.orderid,

P.productid,

D.unitprice

};

Description: Use three tables, and use anonymous classes to illustrate: Use three tables, and use anonymous classes to represent the

Relationship. The relationship between them cannot be described clearly with a key, so an anonymous class is used to represent the key combination. There is also a

Is that two tables are represented by a combination of keys, and no anonymous classes are required.

6. Can be null/a non-nullable key relationship (nullable/nonnullable key Relationship):

This example shows how to construct a join that can be null on one side and non-nullable on the other side

var q =

From O in Db.orders

Join E in Db.employees

On O.employeeid equals

(int?) E.employeeid into Emps

From E in Emps

Select New

{

O.orderid,

E.firstname

};

}

LINQ to SQL Count/sum/min/max/avg Join