MicroOrm.Net（4） Table.Query() - Where

MicroOrm.Net主页：

http://code.google.com/p/micro-orm-net/

MicroOrm.Net系列随便目录总览：

http://www.cnblogs.com/mapserver/archive/2012/04/07/2436129.html

本文我们来演示MicroOrm.Net的查询，请看Table.Query()的api文档：

// ----------------------第一部分----------------------
Query Where(Expression criteria)
Query Select(params MathE[] maths)
Query Select(params Column[] columns)
Query Join(Table joinTable, Expression on)
Query InnerJoin(Table joinTable, Expression on)
Query LeftJoin(Table joinTable, Expression on)
Query LeftOuterJoin(Table joinTable, Expression on)
Query RightJoin(Table joinTable, Expression on)
Query RightOuterJoin(Table joinTable, Expression on)
Query OrderBy(params Column[] columns)
Query OrderByAsc(params Column[] columns
Query OrderByDesc(params Column[] columns)
Query GroupBy(params Column[] columns)
Query Having(Expression criteria)
Query Skip(int skip)
Query Take(int take)
Query Distinct()
 
// ----------------------第二部分----------------------
dynamic First()
T First<T>()
dynamic Last()
T Last<T>()
IList ToList()
dynamic[] ToArray()
IList<T> ToList<T>()
T[] ToArray<T>()

上面的api分为两部分，第一部分的返回值都为Query对象，第二部分才会向数据库去执行sql语句，返回查询的结果。

Where：

db.OrderDatail.Query()
        .Where(db.OrderDetail.OrderId == "001")
         
db.OrderDatail.Query()
        .Where(db.OrderDetail.OrderId == "001" && db.OrderDetail.Qty > 0)
         
db.OrderDatail.Query()
        .Where(db.OrderDetail.OrderId == "001" && (db.OrderDetail.Qty > 0 || db.OrderDetail.OrderDetailId == "001")

MicroOrm.Net也支持表的别名。即使定义了表的别名，在Where方法的表达式里，可以用表的别名，也可以不用。

Where方法，可以执行多次。

db.OrderDatail.As("od").Query() //table alias
        .Where(db.od.OrderId == "001")
        .Where(db.OrderDetail.Qty > 0)

Where方法的表达式也支持Like、IsNull、IsNotNull

db.OrderDatail.As("od").Query()
        .Where(db.od.ProductId.IsNotNull())
        .Where(db.od.ProductId.Like("CA%") 

下面演示向数据库执行sql语句：

var od = db.OrderDetail.Query()
                .Where(db.OrderDetail.OrderId == "001")
                .First();

产生的sql语句如下：

exec sp_executesql N'SELECT TOP 1 [_#0_].* FROM [dbo].[OrderDetail] [_#0_] WHERE [_#0_].[OrderId] = @p0 ',N'@p0 nvarchar(3)',@p0=N'001'

var od1 = db.OrderDetail.Query()
    .Where(db.OrderDetail.OrderId == "001")
    .First<OrderDetail>();
 
var od2 = db.OrderDetail.Query()
    .Where(db.OrderDetail.OrderId == "001")
    .Last();
 
OrderDetail od3 = db.OrderDetail.Query()
    .Where(db.OrderDetail.OrderId == "001")
    .Last<OrderDetail>();
 
IList ods1 = db.OrderDetail.Query()
        .Where(db.OrderDetail.OrderId == "001")
        .ToList(); 
 
dynamic[] ods2 = db.OrderDetail.Query()
        .Where(db.OrderDetail.OrderId == "001")
        .ToArray();
 
IList<OrderDetail> ods3 = db.OrderDetail.Query()
        .Where(db.OrderDetail.OrderId == "001")
        .ToList<OrderDetail>();
 
OrderDetail[] ods4 = db.OrderDetail.Query()
        .Where(db.OrderDetail.OrderId == "001")
        .ToArray<OrderDetail>(); 