关联查询

目前开发的一个小项目中尝试使用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<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/官方网站。