Database Component hxj. Data (14th) (Joint query)

The joint query has already appeared in the previous example, but it is not described in detail.

Let's take an example.

DbSession.Default.From<Customers>()                .InnerJoin<Orders>(Customers._.CustomerID == Orders._.CustomerID)                .ToDataTable();

Generated SQL

Text: 
SELECT * FROM [Customers] INNER JOIN [Orders] ON ([Customers].[CustomerID] = [Orders].[CustomerID]) 

It is a very simple operation.

 

Method	Joint query (SQL)
Innerjoin	Inner join
Leftjoin	Left join
Rightjoin	Right join
Crossjoin	Cross join
Fulljoin	Full join
Union	Union
Unionall	Union all

 

The above example is refreshing.

DbSession.Default.From<Customers>()                .LeftJoin<Orders>(Customers._.CustomerID == Orders._.CustomerID)                .Where(Customers._.Country == "USA")                .ToDataTable();

Generated SQL

Text: 
SELECT * FROM [Customers] 
LEFT OUTER JOIN [Orders] 
ON ([Customers].[CustomerID] = [Orders].[CustomerID]) 
WHERE [Customers].[Country] = @e3c66f9aa65c498abfd76908621b567a 

Parameters: 
@e3c66f9aa65c498abfd76908621b567a[String] = USA 

The innerjoin, leftjoin, rightjoin, crossjoin, and fulljoin operations are similar.

For example

DbSession.Default.From<Customers>()                .InnerJoin<Orders>(Customers._.CustomerID == Orders._.CustomerID)                .InnerJoin<Order_Details>(Order_Details._.OrderID == Orders._.OrderID)                .Where(Customers._.Country == "USA")                .ToDataTable();

Generated SQL:

Text: 
SELECT * FROM [Customers] 
INNER JOIN [Orders] ON ([Customers].[CustomerID] = [Orders].[CustomerID]) 
INNER JOIN [Order Details] ON ([Order Details].[OrderID] = [Orders].[OrderID]) 
WHERE [Customers].[Country] = @5901349776db425492724e16c3c03a7a 

Parameters: 
@5901349776db425492724e16c3c03a7a[String] = USA 

It's easy to query the association.

 

The following describes union and Union all.

These two are a collection of two results. Union will exclude the same result, and Union all will merge the result set directly.

DbSession.Default.From<Customers>().Where(Customers._.Country == "USA")                .UnionAll(DbSession.Default.From<Customers>().Where(Customers._.Country == "UK"))                .ToList();

The generated SQL statement is as follows:

Text: 
SELECT * FROM 
(( SELECT * FROM [Customers] WHERE [Customers].[Country] = @f5b865bb20b64387b2c1be466c6d0980) 
UNION ALL 
( SELECT * FROM [Customers] WHERE [Customers].[Country] = @b7de905de065418dab5820094c764e1b)) 
AS Customers 

Parameters: 
@f5b865bb20b64387b2c1be466c6d0980[String] = USA @b7de905de065418dab5820094c764e1b[String] = UK 

A bug was found during writing, so please download the new version again. The usage of union and Union all is the same, so we will not give an example. The next section describes sorting and grouping.