查
根據id
var result = db.SingleById<Poco>(1);
根據字段
var customer = db.Single<Customer>(new { customer.Email });
動态結果
db.Select<List<object>>(db.From<Poco>().Select("COUNT(*), MIN(Id), MAX(Id)"))[0];
// List<object>:[ 10, 1, 10]
同上
db.Select<Dictionary<string,object>>(db.From<Poco>().Select("COUNT(*) Total, MIN(Id) MinId, MAX(Id) MaxId"))[0]
單行
db.Single<List<object>>(db.From<Poco>().Select("COUNT(*) Total, MIN(Id) MinId, MAX(Id) MaxId"))
硬來 首行首列
object result = db.Scalar<object>(db.From<Poco>().Select(x => x.Id));
in操作 Order表的CustomerId等于Customer表記錄(Country等于USA)的id
sql:select * from Order where CustomerId in ( select id from Customer where Country = "USA" )
var usaCustomerIds = db.From<Customer>(c => c.Country == "USA").Select(c => c.Id);
var usaCustomerOrders = db.Select(db.From<Order>()
.Where(q => Sql.In(q.CustomerId, usaCustomerIds)));
db.Select<Author>(q => q.Birthday >= new DateTime(agesAgo, 1, 1)
&& q.Birthday <= new DateTime(agesAgo, 12, 31));
In
db.Select(q => Sql.In(q.City, "London", "Madrid", "Berlin"));
sql: WHERE "JobCity" In ('London', 'Madrid', 'Berlin')
like a開頭
db.Select(q => q.Name.StartsWith("A"));
WHERE upper("Name") like 'A%'
like GARZON結尾
db.Select(q => q.Name.EndsWith("garzon"));
WHERE upper("Name") like '%GARZON'
like 包含
db.Select(q => q.Name.Contains("Benedict"));
WHERE upper("Name") like '%BENEDICT%'
and
db.Select(q => q.Rate == 10 && q.City == "Mexico");
WHERE (("Rate" = 10) AND ("JobCity" = 'Mexico'))
最大
int maxAgeUnder50 = db.Scalar<Person, int>(x => Sql.Max(x.Age), x => x.Age < 50);
SELECT Max("Age") FROM "Person" WHERE ("Age" < 50)
count 彙總
int peopleOver40 = db.Scalar( db.From
().Select(Sql.Count("*")).Where(q => q.Age > 40));
int peopleUnder50 = db.Count
(x => x.Age < 50);
不通過id Single會加top 1
Person personByAge = db.Single
(x => x.Age == 42);
是否存在
bool has42YearOlds = db.Exists
(new { Age = 42 });
傳回固定字段
List results = db.Column(db.From
().Select(x => x.LastName).Where(q => q.Age == 27));
SELECT "LastName" FROM "Person" WHERE ("Age" = 27)
---
對結果去重
HashSet<int> results = db.ColumnDistinct<int>(db.From<Person>().Select(x => x.Age) .Where(q => q.Age < 50));
SELECT "Age" FROM "Person" WHERE ("Age" < 50)
字典結果
Dictionary<int,string> results = db.Dictionary<int, string>( db.From<Person>().Select(x => new { x.Id, x.LastName }).Where(x => x.Age < 50));
SELECT "Id","LastName" FROM "Person" WHERE ("Age" < 50)
複雜的字典
Dictionary<int, List> results = db.Lookup<int, string>(db.From
().Select(x => new { x.Age, x.LastName }).Where(q => q.Age < 50));
SELECT "Age","LastName" FROM "Person" WHERE ("Age" < 50)
SelectFmt 參數格式化
var tracks = db.SelectFmt<Track>("Artist = {0} AND Album = {1}", "Nirvana", "Heart Shaped Box");
等同于
var tracks = db.SelectFmt<Track>("SELECT * FROM track WHERE Artist={0} AND Album={1}","Nirvana", "Heart Shaped Box");
多條記錄
List<Track> tracks = db.Select<Track>()
單條記錄
Track track = db.Single(q => q.RefId == refId)
字典 前2列
Dictionary<int, string> trackIdNamesMap = db.Dictionary<int, string>( "select Id, Name from Track")
Lookup 字典 前2列 傳回的是Dictionary < K, List < V > >
Dictionary<int, List> albumTrackNames = db.Lookup<int, string>( "select AlbumId, Name from Track")
傳回首列
List trackNames = db.Column("select Name from Track")
HashSet首列值去重
HashSet uniqueTrackNames = db.ColumnDistinct("select Name from Track")
Scalar傳回單行單列
var trackCount = db.Scalar("select count(*) from Track")
預設為and條件
var track3 = db.Where(new { AlbumName = "Throwing Copper", TrackNo = 3 })
這種方式被用爛了大街 屬性對應參數名
var track3 = db.Select(
"select * from Track Where AlbumName = @album and TrackNo = @trackNo", new { album = "Throwing Copper", trackNo = 3 })
通過id 傳回強類型
var track = db.SingleById(1); id等于1的一條
var tracks = db.SelectByIds(new[]{ 1,2,3 }); id等于 1 2 3的三條
延遲加載 SelectLazy這種以Lazy結尾
var lazyQuery = db.SelectLazy
("Age > @age", new { age = 40 });
// Iterate over a lazy sequence
foreach (var person in lazyQuery) {
//...
}
其他類同 如下
var topVIPs = db.WhereLazy
(new { Age = 27 }).Where(p => IsVip(p)).Take(5)
var topVIPs = db.SelectLazyFmt
("Age > {0}", 40).Where(p => IsVip(p)).Take(5)
作者:
過錯出處:http://www.cnblogs.com/wang2650/
關于作者:net開發做的久而已。十餘年時光虛度!
本文版權歸作者和部落格園共有,歡迎轉載,但未經作者同意必須保留此段聲明,且在文章頁面明顯位置給出原文連接配接。如有問題,可以郵件:[email protected]
聯系我,非常感謝。