1.5 Query‐Subquery

1. Basic course

1.1 API

/*** Single column ***/
//First: select top 1 id
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)
//max Example 1:
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Max(s => s.Id)
//max Example 2:
SqlFunc.Subqueryable<School>().. Where(s=>s.Id==st.Id).Select(s=>SqlFunc.AggregateMax(s.Id))
//min:
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Min(s => s.Id)
//avg:
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Avg(s => s.Id)
//count:
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Count()
//sum:
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Sum(s => s.num)
// Sum up the quantity after weight removal
SqlFunc.Subqueryable<School>().Where(s => z.Id == st.Id).Select(s=>SqlFunc.AggregateDistinctCount(s.Name))
// Comma separates columns
SqlFunc.Subqueryable<Order>().Where(z=>z.Id==it.Id).SelectStringJoin(z => z.Name, ",")
// Complete use case: See heading 8


/*** Returns a List or entity ***/
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList()
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(it=>new Class(){ Name=it.Name})


/*** Returns a single entity ***/
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).First()
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).First(it=>new Class(){ Name=it.Name})


/***IN, Not in, Exists, Not Exists***/
var getAll7 = db.Queryable<Student>()
.Where(it => SqlFunc.Subqueryable<School>().Where(s =>s.Id==it.Id).Any()).ToList();//Any and NotAny

Used in 1.2 where

var getAll = db.Queryable<Student>().LeftJoin<School>((st, sc) => st.Id==sc.Id)
.Where(st => st.Id == SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id))
.ToList();

/* Generated MYSQL statement, if SqlServer is TOP 1
SELECT `st`.`ID`,`st`.`SchoolId`,`st`.`Name`,`st`.`CreateTime`
FROM `STudent` st Left JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` )
WHERE (' st. 'ID' =(SELECT 'Id' FROM 'School' WHERE (' Id '=' st. 'ID') limit 0,1)
* /

1.3 Used in select

var getAll = db.Queryable<Student>().LeftJoin<School>((st, sc) => st.Id==sc.Id)
.Select(st => new{
name = st.Name,
id = SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)
}).ToList();

2. IN and NOT IN operations

2.1 Exists

The difference between Exists and in is that Exists is more extensible and supports multiple fields (simply speaking, any Exists that can be implemented In can be implemented).

// The same as IN
var getAll7 = db.Queryable<Student>()
.Where(it => SqlFunc.Subqueryable<School>().Where(s =>s.Id==it.Id).Any()).ToList();

/* Generated SQL(equivalent to it.id in(select id from school), but written differently
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it
WHERE (EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) ))
* /


// is the same as NOT IN
var getAll8 = db.Queryable<Student>().Where(it =>
SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).NotAny()).ToList();

/* Generated SQL
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it
WHERE (NOT EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) ))
* /

// Search for exits

2.2 In and Not In (5.0.7.5)

If GroupBy exists, == is automatically converted to in

db.Queryable<Order>()
.Where(it=>it.Id==SqlFunc.Subqueryable<Custom>().GroupBy(z=>z.OrderId).Select(z=>z.OrderId)).ToList()

//Sql
//SELECT [Id],[Name] FROM [Order]
// WHERE  [Id] in ((SELECT [OrderId] FROM [Custom] GROUP BY [OrderId]))

2.3 Paging In

db.Queryable<Order>().In(it=>it.Id,db.Queryable<Order>().Skip(1).Take(100).Select(it=>it.Id)).ToList();

3. Use Sql

Only the latest version supports it

string sql=" (select top 1 id from [Order] ) ";
var list = db.Queryable<Order>()
.Select(it => new
{
name=it.Name,
customName= SqlFunc.MappingColumn<string>(sql) 
}).ToList();
 
//sql
SELECT
[Name] AS [name] ,
(select top 1 id from [Order] ) AS [customName]
FROM [Order]

4. ToList() collection object

The performance of this writing is average, and the performance requirement is higher than that of navigation or ThenMapper

// Please upgrade to 5.1.3.36+
db.Queryable<Order>()
.Select(it => new
{
CustomId = it.CustomId,
OrderId = it.Id,
OrderName = it.Name,
CustomList = SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList()
})
.ToList();
// DTO is also available
SqlFunc.Subqueryable<Custom>()
.Where(c=> c.Id == it.CustomId).
ToList(c=> new Class(){Name= c.ame}) // Convert to DTO
// Note: The c alias in where ToList must be the same
 

// Check only lists <string>
SqlFunc.Subqueryable<Custom>().Where(c => c.Id= = it.CustomId).ToList(c=> C.ame)// Latest version


// Automatic DTO 5.1.4.70
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList<DTO>()
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(it=>new DTO(){},true); // Similar to Queryable