Database component Hxj.data (13) (subquery)

The value of a query condition comes from another query.

Like SQL Server sql:

SELECT * from the products where categoryid= (select top 1 CategoryID from categories where categoryname= ' produce ')

The implementation code for this SQL 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 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 subquery method corresponds to the SQL table as follows:

" /tr>

method name	sql
=
<>
subqueryless	<
subquerylessorequal	&L t;=
subquerygreater	>
>=
i N
not in

Write an example again

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

The generated SQL 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

The subquery is also changed very simple.

The following section describes the federated query.