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