一、结果排序
-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 > );
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()
.Select(_db.Order.OrderCode, q.Order.OrderDetail.CategoryName);
(利用数据库外键)
var orders = _db.Order.FindAllByCustomerId()
.Select(_db.Order.OrderCode, _db.OrderDetail.CategoryName)
.Join(_db.OrderDetail).On(OrderId: _db.Order.PKID);
(替代外键)
var orders = _db.Order.FindAllByCustomerId()
.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、Max、Min、Sum、Count、Avg
var orderDetails=db.OrderDetail.All().Having(db.OrderDetail.UnitPrice.Max()>);
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().ToScalar();
int id=db.Order.Select(db.Order.Id).Get().ToScalar();
2.2、ToScalarOrDefault、ToScalarOrDefault<T>从查询结果中返回一个属性作为标量值，这个值作为它的默认值。如果属性值为NULL，ToScalarOrDefault<T>返回default<T>
string orderCode=db.Order.FindAllById().Select(db.Order.OrderCode).ToScalarOrDefault();
2.3、ToScalarList、ToScalarList<T>从一系列查询结果中返回某个列表作为标量值
List<string> orderDetails=db.OrderDetail.FindAllByOrderId().Select(db.OrderDetail.CategoryName).ToScalarList<string>();
2.4、ToScalarArray、ToScalarArray<T>从一系列查询结果中返回某个数组作为标量值
string[] orderDetails=db.OrderDetail.FindAllByOrderId().Select(db.OrderDetail.CategoryName).ToScalarArray<string>();
待续...