Database Component hxj. Data (13) (subquery)

The value of the query condition comes from another query.

For example, sqlserver SQL:

Select * from products where categoryid = (select top 1 categoryid from categories where categoryname = 'produce ')

The implementation code of this SQL statement is as follows:

DbSession.Default.From<Products>()                .Where(Products._.CategoryID                .SubQueryEqual(DbSession.Default.From<Categories>().Where(Categories._.CategoryName == "Produce").Select(Categories._.CategoryID).Top(1)))                .ToList();

Compare the SQL statements generated by the component

Text: 
SELECT * FROM [Products] 
WHERE [Products].[CategoryID] = ( SELECT TOP 1 [Categories].[CategoryID] FROM [Categories] WHERE [Categories].[CategoryName] = @174b5c8999e2480594cdc08ab4d8e5bd) 

Parameters: 
@174b5c8999e2480594cdc08ab4d8e5bd[String] = Produce 

 

The SQL table corresponding to the subquery method is as follows:

Method Name	SQL
Subqueryequal	=
Subquerynotequal	<>
Subqueryless	<
Subquerylessorequal	<=
Subquerygreater	>
Subquerygreaterorequal	> =
Subqueryin	In
Subquerynotin	Not in

 

Write another example

DbSession.Default.From<Products>()                .Where(Products._.CategoryID                .SubQueryNotIn(DbSession.Default.From<Categories>().Where(Categories._.CategoryName == "Produce").Select(Categories._.CategoryID)))                .ToList();

The generated SQL statement is as follows:

Text: 
SELECT * FROM [Products] 
WHERE [Products].[CategoryID] 
NOT IN ( SELECT [Categories].[CategoryID] FROM [Categories] WHERE [Categories].[CategoryName] = @32365a219b864e5fbeb7959a6071d4c8) 

Parameters: 
@32365a219b864e5fbeb7959a6071d4c8[String] = Produce 

Isn't subquery quite easy.

 

The next section describes joint queries.