Subsonic queries查询帮助

目前开发的一个小项目中尝试使用Subsonic的常常会使用到Query，把一些常用的方法收集以备查阅。

下面是来自Subsonic官方网站的一些例子，例子基于Northwind数据库

Simple Select with string columns

?

1 2 3 4

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

Simple Select with typed columns

?

1 2 3

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

Returning a Single object(返回简单实体)

?

1 2 3

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

Returning all columns(返回所有列)

?

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

Simple Where(简单Where语句)

?

1 2 3

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

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

?

1 2 3 4 5

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

Simple Inner Join(内联接)

?

1 2 3

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

Simple Join With Table Enum

?

1 2 3

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

Multiple Joins As Collection(多级联接)

?

1 2 3 4 5 6 7

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(左外联接)

?

1 2 3

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

Left Outer Join With Schema()

?

1 2 3

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

Left Outer Join With Magic Strings

?

1 2 3

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

Simple Select With Collection Result

?

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

Simple Select With LIKE

?

1 2 3 4 5

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

Using Nested Where/And/Or

?

1 2 3 4

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

?

1 2 3 4

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(分页查询)

?

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

Paged Query With Join(带联接的分页查询)

?

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

Paged View

?

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

Simple IN Query(in查询)

?

1 2 3 4

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

?

1 2 3 4 5 6 7

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

Using Multiple INs

?

1 2 3 4

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/官方网站。