學習simple.data之進階篇

一、結果排序

-OrderBy(升序)

-OrderByDescending(降序)

db.PRoduct.All().OrderByFactoryName();db.Product.All().OrderByFactoryNameDescending();

或

db.Product.All().OrderBy(db.Product.FactoryName);db.Product.All().OrderByDescending(db.Product.FactoryName);

二、表連接

1 、無外鍵約束

1.1 自然連接

-Join

-Left Join

-Outer Join

var q = _db.Order.Query().Join(_db.OrderDetail, ).Where(_db.OrderDetail.UnitPrice > 15000);List<dynamic> employees = q.Select(_db.Order.OrderCode, q.OrderDetail.CategoryName).ToList();

1.2、級聯查詢

-With

-WithOne

-WithMany

dynamic OrderDetailAlias;var OrderDetails = db.Order.All().With(db.Order.OrderDetail.As("OrderDetailInfo"), out OrderDetailAlias) .Select( db.Order.OrderCode, BandAlias.Name);

2、外鍵約束

2.1、單個數據集返回

var orders = _db.Order.FindAllByCustomerId(10).Select(_db.Order.OrderCode, q.Order.OrderDetail.CategoryName);

(利用數據庫外鍵)

var orders = _db.Order.FindAllByCustomerId(10).Select(_db.Order.OrderCode, _db.OrderDetail.CategoryName).Join(_db.OrderDetail).On(OrderId: _db.Order.PKID);

(替代外鍵)

var orders = _db.Order.FindAllByCustomerId(10).Select(_db["Order"]["OrderCode"], _db["OrderDetail"]["CategoryName"]).Join(_db["OrderDetail"]).On(OrderId: _db["Order"]["PKID"]);

(替代外鍵-索引寫法)

三、聚合函數

1、Having

var orderDetails= db.OrderDetail.All().Select(db.OrderDetail.Order.OrderCode,         db.OrderDetail.PKID.Count().As("OrderDetailCount")).Having(db.OrderDetail.PKID.Count() > 2);

2、Max、Min、Sum、Count、Avg

var orderDetails=db.OrderDetail.All().Having(db.OrderDetail.UnitPrice.Max()>100);

var orderDetails=db.OrderDetail.All().Having(db.OrderDetail.UnitPrice.Min()>100);

3、Count、CountDistinct

var orderDetails=db.OrderDetail.All().Select(db.OrderDetail.OrderId.Count());var orderDetails=db.OrderDetail.All().Select(db.OrderDetail.OrderId.CountDistinct());去重數量

四、幫助函數

1、Length(字段長度)

var codeLength=db.Order.Select(db.Order.OrderCode.Length().As("CodeLength"));

2、轉換為標量值

2.1、ToScalar、ToScalar<T>從查詢結果中返回一個屬性作為標量值

int id=db.Order.Get(1).ToScalar();int id=db.Order.Select(db.Order.Id).Get(1).ToScalar();

2.2、ToScalarOrDefault、ToScalarOrDefault<T>從查詢結果中返回一個屬性作為標量值，這個值作為它的默認值。如果屬性值為NULL，ToScalarOrDefault<T>返回default<T>

string orderCode=db.Order.FindAllById(1).Select(db.Order.OrderCode).ToScalarOrDefault();

2.3、ToScalarList、ToScalarList<T>從一系列查詢結果中返回某個列表作為標量值

List<string> orderDetails=db.OrderDetail.FindAllByOrderId(1).Select(db.OrderDetail.CategoryName).ToScalarList<string>();

2.4、ToScalarArray、ToScalarArray<T>從一系列查詢結果中返回某個數組作為標量值

string[] orderDetails=db.OrderDetail.FindAllByOrderId(1).Select(db.OrderDetail.CategoryName).ToScalarArray<string>();

待續...