Subqueries

dbExpression supports subqueries in queries. Subqueries are just like any other select query, simply write a normal SelectOne or SelectMany query and include that query as a comparison argument in a JOIN clause.

The general form of subqueries in a JOIN clause:

IEnumerable<dynamic> vips = db.SelectMany(
	dbo.Person.Id, 
	dbo.Person.FirstName, 
	dbo.Person.LastName,
	dbex.Alias("t0", "TotalPurchase")
	)
.From(dbo.Person)
.InnerJoin(
	db.SelectMany(
	    dbo.Purchase.PersonId,
	    db.fx.Sum(dbo.Purchase.TotalPurchaseAmount).As("TotalPurchase")
	)
	.Top(100)
	.From(dbo.Purchase)
	.GroupBy(dbo.Purchase.PersonId)
	.Having(db.fx.Sum(dbo.Purchase.TotalPurchaseAmount) > 25)
	.OrderBy(db.fx.Sum(dbo.Purchase.TotalPurchaseAmount).Desc())
).As("t0")
.On(dbex.Alias("t0", "PersonId") == dbo.Person.Id)
.Execute();

Subqueries are discussed in more detail in Subquery Aliasing.