Active Record Fluent Query

= ActiveRecord Fluent Query Examples = If you're using the ActiveRecord templates then as well SubSonic's Linq implementation you can also make use of Fluent Queries All of the samples below assume the Northwind database.

Simple Select with string columns ==int records = new NorthwindDB.SelectColumns(new string[] { "productID"}) .From().GetRecordCount(); Assert.IsTrue(records == 77); == Simple Select with typed columns

int records = new NorthwindDB.Select( new string[] { ProductTable.ProductIDColumn, Product.ProductNameColumn }) .From().GetRecordCount(); Assert.IsTrue(records

77); == Returning a Single object

Product p = new NorthwindDB.Select .From() .Where(ProductTableProduct.IDColumn).IsEqualTo(1) .ExecuteSingle(); Assert.IsNotNull(p);

Returning all columns

int records = new NorthwindDB.Select .From().GetRecordCount(); Assert.IsTrue(records

77); == Simple Where

int records = new NorthwindDB.Select .From() .Where(ProductTable.CategoryIDColumn).IsEqualTo(5) .GetRecordCount(); Assert.AreEqual(7, records);

Simple Where with And (as List)

List products = new NorthwindDB.Select .From() .Where(ProductTable.CategoryIDColumn).IsEqualTo(5) .And(ProductTable.ProductIDColumn).IsGreaterThan(50) .ExecuteTypedList();

Simple Inner Join

SubSonic.SqlQuery query = NorthwindDB.Select .From() .InnerJoin() .Where(ProductTable.CategoryIdColumn).IsEqualTo(5);

Multiple Joins As List

List customersByCategory = new NorthwindDB.Select .From() .InnerJoin() .InnerJoin(OrderDetailTable.OrderIDColumn, OrderTable.OrderIDColumn) .InnerJoin(ProductTable.ProductIDColumn, OrderDetailTable.ProductIDColumn) .Where(ProductTable.CategoryIDColumn).IsEqualTo(5) .ExecuteTypedList();

Left Outer Join With Generics

SubSonic.SqlQuery query = new NorthwindDB.Select .From() .LeftOuterJoin(); query.Aggregates = new List { new Aggregate(CustomerTable.CustomerNameColumn, AggregateFunction.GroupBy) };

Simple Select With Collection Result

List products = new NorthwindDB.Select .From() .ExecuteTypedList();

Simple Select With LIKE

List products = new NorthwindDB.Select .From() .InnerJoin() .Where(CategoryTable.CategoryNameColumn).Like("c%") .ExecuteTypedList();

Using Nested Where/And/Or

List products = new NorthwindDB.Select .From() .WhereExpression(ProductTable.CategoryIDColumn).IsEqualTo(5) .And(ProductTable.ProductIdColumn).IsGreaterThan(10) .OrExpression(ProductTable.CategoryIdColumn).IsEqualTo(2) .And(ProductTable.ProductIdColumn).IsBetweenAnd(2, 5) .ExecuteTypedList(); List products = new NorthwindDB.Select .From() .WhereExpression(ProductTable.CategoryIDColumn).IsEqualTo(5) .And(ProductTable.ProductIdColumn).IsGreaterThan(10) .Or(ProductTable.CategoryIDColumn).IsEqualTo(2) .AndExpression(ProductTable.ProductIdColumn).IsBetweenAnd(2, 5) .ExecuteTypedList();

Simple Paged Query

SubSonic.SqlQuery query = new NorthwindDB.Select .From() .Paged(1, 20) .Where(ProductTable.ProductIdColumn).IsLessThan(100);

Paged Query With Join

SubSonic.SqlQuery query = new NorthwindDB.SelectColumns(new string[] { "ProductId", "ProductName", "CategoryName" }) .From() .InnerJoin() .Paged(1, 20); Simple IN Query int records = new NorthwindDB.Select() .From() .Where(ProductTable.ProductIdColumn).In(1, 2, 3, 4, 5) .GetRecordCount(); Assert.IsTrue(records

5); == Using IN With Nested Select

int records = new NorthwindDB.Select.From() .Where(ProductTable.ProductIdColumn) .In( new NorthwindDB.SelectColumns(ProductTable.ProductIdColumn) .From() .Where(ProductTable.CategoryIdColumn).IsEqualTo(5)) .GetRecordCount();

Using Multiple INs

SubSonic.SqlQuery query = new NorthwindDB.Select .From() .Where(Product.CategoryIDColumn).In(2, 4) .And(Product.SupplierIDColumn).In(3, 9);