轉：Subsonic queries查詢協助

    目前開發的一個小項目中嘗試使用Subsonic的常常會使用到Query，把一些常用的方法收集以備查閱。

下面是來自Subsonic官方網站的一些例子，例子基於Northwind資料庫

Simple Select with string columns

 int records = new Select("productID").                 From("Products").GetRecordCount(); Assert.IsTrue(records == 77);

Simple Select with typed columns

int records = new Select(Product.ProductIDColumn, Product.ProductNameColumn).                From<Product>().GetRecordCount();Assert.IsTrue(records == 77);

Returning a Single object(返回簡單實體)

Product p = new Select().From<Product>().              Where("ProductID").IsEqualTo(1).ExecuteSingle<Product>();Assert.IsNotNull(p);

Returning all columns(返回所有列)

int records = new Select().From("Products").GetRecordCount();Assert.IsTrue(records == 77);

Simple Where(簡單Where語句)

int records = new Select().From("Products").                Where("categoryID").IsEqualTo(5).GetRecordCount();Assert.AreEqual(7, records);

Simple Where with And (as Collection)(帶有And的Where語句，返回集合)

ProductCollection products =                DB.Select().From("Products")                    .Where("categoryID").IsEqualTo(5)                    .And("productid").IsGreaterThan(50)                    .ExecuteAsCollection<ProductCollection>();

Simple Inner Join(內聯結)

SubSonic.SqlQuery q = new Select("productid").From(OrderDetail.Schema)                .InnerJoin(Product.Schema)                .Where("CategoryID").IsEqualTo(5);

Simple Join With Table Enum

SubSonic.SqlQuery q = new Select().From(Tables.OrderDetail)                .InnerJoin(Tables.Product)                .Where("CategoryID").IsEqualTo(5);

Multiple Joins As Collection(多級聯結)

CustomerCollection customersByCategory = new Select()                .From(Customer.Schema)                .InnerJoin(Order.Schema)                .InnerJoin(OrderDetail.OrderIDColumn, order.OrderIDColumn)                .InnerJoin(Product.ProductIDColumn, orderDetail.ProductIDColumn)                .Where("CategoryID").IsEqualTo(5)                .ExecuteAsCollection<CustomerCollection>();

Left Outer Join With Generics(左外聯結)

SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))                .From&lt;Customer>()                .LeftOuterJoin<Order>();

Left Outer Join With Schema()

SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))                .From(Customer.Schema)                .LeftOuterJoin(Order.CustomerIDColumn, Customer.CustomerIDColumn);

Left Outer Join With Magic Strings

SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))                .From("Customers")                .LeftOuterJoin("Orders");

Simple Select With Collection Result

ProductCollection p = Select.AllColumnsFrom<Product>()                .ExecuteAsCollection<ProductCollection>();

Simple Select With LIKE

ProductCollection p = DB.Select()                .From(Product.Schema)                .InnerJoin(Category.Schema)                .Where("CategoryName").Like("c%")                .ExecuteAsCollection<ProductCollection>();

Using Nested Where/And/Or

ProductCollection products = Select.AllColumnsFrom<Product>()                .WhereExpression("categoryID").IsEqualTo(5).And("productid").IsGreaterThan(10)                .OrExpression("categoryID").IsEqualTo(2).And("productID").IsBetweenAnd(2, 5)                .ExecuteAsCollection<ProductCollection>();

ProductCollection products = Select.AllColumnsFrom<Product>()                .WhereExpression("categoryID").IsEqualTo(5).And("productid").IsGreaterThan(10)                .Or("categoryID").IsEqualTo(2).AndExpression("productID").IsBetweenAnd(2, 5)                .ExecuteAsCollection<ProductCollection>();

Simple Paged Query(分頁查詢)

SubSonic.SqlQuery q = Select.AllColumnsFrom<Product>().               Paged(1, 20).Where("productid").IsLessThan(100);

Paged Query With Join(帶聯結的分頁查詢)

SubSonic.SqlQuery q = new Select("ProductId", "ProductName", "CategoryName").                From("Products").InnerJoin(Category.Schema).Paged(1, 20);

Paged View

SubSonic.SqlQuery q = new Select().From(Invoice.Schema).Paged(1, 20);

Simple IN Query(in查詢)

int records = new Select().From(Product.Schema)                .Where("productid").In(1, 2, 3, 4, 5)                .GetRecordCount();Assert.IsTrue(records == 5);

Using IN With Nested Select

int records = Select.AllColumnsFrom<Product>()                .Where("productid")                .In(                new Select("productid").From(Product.Schema)                    .Where("categoryid").IsEqualTo(5)                )                .GetRecordCount();(返回記錄數)

Using Multiple INs

SubSonic.SqlQuery query = new Select()                .From(Product.Schema)                .Where(Product.CategoryIDColumn).In(2)                .And(Product.SupplierIDColumn).In(3);

select * from table where column1 = 1 and (column2 = 2 or column2 = 3)轉為Select().From<product>.Where(...).AndExpression(column2).IsEqualTo(2).Or(column2).IsEqualTo(3)想查看更多Subsonic相關的資料可以到http://subsonicproject.com/官方網站。