ServiceStack.OrmLite 6学習ノート調べ
5315 ワード
調べる
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 maxAgeUnder 50=db.Scalar (x => Sql.Max(x.Age), x => x.Age < 50);
SELECT Max("Age") FROM "Person"WHERE ("Age"< 50)
countまとめint peopleOver 40=db.Scalar ( db.From ().Select(Sql.Count("*")).Where(q => q.Age > 40));
int peopleUnder50 = db.Count (x => x.Age < 50); --- id Singleを通過しないとtop 1 PersonpersonByAge=dbが加算されます.Single (x => x.Age == 42); --- bool has 42 YearOlds=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)
複雑な辞書Dictionaryresults=db.Lookup (db.From ().Select(x => new { x.Age, x.LastName }).Where(q => q.Age < 50));
シングルトラックトラック=db.Single(q => q.RefId == refId)
ディクショナリの最初の2列Dictionary trackIdNamesMap=db.Dictionary ( "select Id, Name from Track")
Lookup辞書の上位2列はDictionary>DictionaryalbumTrackName=dbを返す.Lookup ( "select AlbumId, Name from Track")
最初のリストtrackName=dbを返します.Column ("select Name from Track")
HashSetの最初の列の値は、HashSet uniqueTrackName=db.ColumnDistinct ("select Name from Track")
Scalarは単一行var trackCount=dbを返す.Scalar ("select count(*) from Track")
デフォルトはand条件var track 3=dbです.Where(new { AlbumName = "Throwing Copper", TrackNo = 3 })
この方式は街属性対応パラメータ名var track 3=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の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)
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 maxAgeUnder 50=db.Scalar (x => Sql.Max(x.Age), x => x.Age < 50);
SELECT Max("Age") FROM "Person"WHERE ("Age"< 50)
countまとめint peopleOver 40=db.Scalar ( db.From ().Select(Sql.Count("*")).Where(q => q.Age > 40));
int peopleUnder50 = db.Count (x => x.Age < 50); --- id Singleを通過しないとtop 1 PersonpersonByAge=dbが加算されます.Single (x => x.Age == 42); --- bool has 42 YearOlds=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
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>()
シングルトラックトラック=db.Single(q => q.RefId == refId)
ディクショナリの最初の2列Dictionary trackIdNamesMap=db.Dictionary ( "select Id, Name from Track")
Lookup辞書の上位2列はDictionary
最初のリストtrackName=dbを返します.Column ("select Name from Track")
HashSetの最初の列の値は、HashSet uniqueTrackName=db.ColumnDistinct ("select Name from Track")
Scalarは単一行var trackCount=dbを返す.Scalar ("select count(*) from Track")
デフォルトはand条件var track 3=dbです.Where(new { AlbumName = "Throwing Copper", TrackNo = 3 })
この方式は街属性対応パラメータ名var track 3=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の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)