ServiceStack.OrmLiteの概要-.NETのマイクロORM

• 仕事の準備。 コードファーストとデータベースファーストのアプローチ
• データベースクエリ
• JOINおよびナビゲーションプロパティ
• 遅延と非同期
• 取引
• OrmLiteとEntity Frameworkのパフォーマンス比較
• おわりに

仕事の準備。 コードファーストとデータベースファーストのアプローチ

インストールパッケージServiceStack.OrmLite.SqlServer

インストールパッケージServiceStack.OrmLite.T4

OrmLite.Core.ttinclude
OrmLite.Poco.tt
OrmLite.SP.tt

[Alias("Order")] [Schema("dbo")] public partial class Order : IHasId<int> { [AutoIncrement] public int Id { get; set; } [Required] public int Number { get; set; } public string Text { get; set; } public int? CustomerId { get; set; } } 

  var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider); using (IDbConnection db = dbFactory.Open()) { //db.AnyMethod... } 

  List<Order> orders = db.Select<Order>(order => order.Number > 50); 

  public override IDbConnection CreateConnection(string connectionString, Dictionary<string, string> options) { var isFullConnectionString = connectionString.Contains(";"); if (!isFullConnectionString) { var filePath = connectionString; var filePathWithExt = filePath.ToLower().EndsWith(".mdf") ? filePath : filePath + ".mdf"; var fileName = Path.GetFileName(filePathWithExt); var dbName = fileName.Substring(0, fileName.Length - ".mdf".Length); connectionString = string.Format( @"Data Source=.\SQLEXPRESS;AttachDbFilename={0};Initial Catalog={1};Integrated Security=True;User Instance=True;", filePathWithExt, dbName); } if (options != null) { foreach (var option in options) { if (option.Key.ToLower() == "read only") { if (option.Value.ToLower() == "true") { connectionString += "Mode = Read Only;"; } continue; } connectionString += option.Key + "=" + option.Value + ";"; } } return new SqlConnection(connectionString); } 

  db.DropAndCreateTable<Order>(); 

  [Schema("dbo")] public partial class Order : IHasId<int> { [AutoIncrement] public int Id { get; set; } [Index(NonClustered = true)] public int Number { get; set; } [CustomField("NVARCHAR(20)")] public string Text { get; set; } [ForeignKey(typeof(Customer))] public int? CustomerId { get; set; } } 

  CREATE TABLE "dbo"."Order" ( "Id" INTEGER PRIMARY KEY IDENTITY(1,1), "Number" INTEGER NOT NULL, "Text" NVARCHAR(20) NULL, "CustomerId" INTEGER NULL, CONSTRAINT "FK_dbo_Order_dbo_Customer_CustomerId" FOREIGN KEY ("CustomerId") REFERENCES "dbo"."Customer" ("Id") ); CREATE NONCLUSTERED INDEX idx_order_number ON "dbo"."Order" ("Number" ASC); 

データベースクエリ

  List<Order> orders = db.Select<Order>(order => order.CustomerId == customerId); 

  List<Order> orders = db.Select(db.From<Order>().Where(order => order.CustomerId == customerId)); 

  List<Order> orders = db.SelectFmt<Order>("CustomerId = {0}", customerId); 

  List<Order> orders = db.SelectFmt<Order>("SELECT * FROM [Order] WHERE CustomerId = {0}", customerId); 

  db.Update(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27}); 

  UPDATE "Person" SET "FirstName" = 'Jimi',"LastName" = 'Hendrix',"Age" = 27 WHERE "Id" = 1 

  db.Insert(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 }); 

  INSERT INTO "Person" ("Id","FirstName","LastName","Age") VALUES (1,'Jimi','Hendrix',27) 

  db.Delete<Person>(p => p.Age == 27); 

  DELETE FROM "Person" WHERE ("Age" = 27) 

JOINおよびナビゲーションプロパティ

  class Customer { [AutoIncrement] public int Id { get; set; } public string Name { get; set; } } 

  List<Order> orders = db.Select<Order>(q => q.Join<Customer>()); 

  SELECT "Order"."Id", "Order"."Details", "Order"."CustomerId" FROM "Order" INNER JOIN "Customer" ON ("Customer"."Id" = "Order"."CustomerId") 

  class OrderInfo { public int OrderId { get; set; } public string OrderDetails { get; set; } public int? CustomerId { get; set; } public string CustomerName { get; set; } } 

  List<OrderInfo> info = db.Select<OrderInfo>(db.From<Order>().Join<Customer>()); 

  SELECT "Order"."Id" as "OrderId", "Order"."Details" as "OrderDetails", "Order"."CustomerId", "Customer"."Name" as "CustomerName" FROM "Order" INNER JOIN "Customer" ON ("Customer"."Id" = "Order"."CustomerId") 

  [Reference] Customer Customer { get; set; } 

  List<Order> orders = db.LoadSelect<Order>(); Assert.True(orders.All(order => order.Customer != null)); 

  SELECT "Id", "Details", "CustomerId" FROM "Order" SELECT "Id", "Name" FROM "Customer" WHERE "Id" IN (SELECT "CustomerId" FROM "Order") 

  [References(typeof(Parent))] public int? CustomerId { get; set; } 

  SqlExpression<Order> expression = db .From<Order>() .Join<Order, Customer>((order, customer) => order.CustomerId == customer.Id); List<Order> orders = db.Select(expression); 

遅延と非同期

  IEnumerable<Product> lazyQuery = db.SelectLazy<Product>("UnitPrice > @UnitPrice", new { UnitPrice = 10 }); 

  db.Select<Product>(q => q.UnitPrice > 10); 

  IEnumerable<string> lazyQuery = db.ColumnLazy<string>(db.From<Product>().Where(x => x.UnitPrice > 10)); 

  List<Employee> employees = await db.SelectAsync<Employee>(employee => employee.City == "London"); 

取引

  db.DropAndCreateTable<Employee>(); Assert.IsTrue(db.Count<Employee>() == 0); using (IDbTransaction transaction = db.OpenTransaction()) { db.Insert(new Employee {Name = "First"}); transaction.Commit(); } Assert.IsTrue(db.Count<Employee>() == 1); using (IDbTransaction transaction = db.OpenTransaction()) { db.Insert(new Employee { Name = "Second" }); Assert.IsTrue(db.Count<Employee>() == 2); transaction.Rollback(); } Assert.IsTrue(db.Count<Employee>() == 1); 

文字列のグループに対する操作。 OrmLiteとEntity Frameworkのパフォーマンス比較

InsertAll（IEnumerable）
UpdateAll（IEnumerable）
DeleteAll（IEnumerable）

  //select context.Orders.AsNoTracking().ToList(); //insert context.Orders.AddRange(orders); context.SaveChanges(); //update context.SaveChanges(); 

  //select db.Select<Order>(); //insert db.InsertAll(orders); //update db.UpdateAll(orders); 

  context.Orders.AsNoTracking().FirstOrDefault(order => order.Id == id); 

  db.SingleById<Order>(id); 

おわりに

• 軽量で、構成と展開が簡単です。
• 単純なCRUDリクエストは非常に簡単に記述できます。

• さまざまなメソッドのクエリ構築の可変性（ラムダ、パラメーター化されたSQL、SqlExpressionのいずれか）。 APIのメソッドでサポートされる単一の汎用構文はありません。
• 不十分なドキュメント：メソッドに関するXMLコメントはありません。公式のドキュメントは構造が不十分で、単一のWebページにあります。
• あいまいなAPI。 db.Select、db.LoadSelect、db.Whereの呼び出しの違いをすぐに推測してみてください。 またはdb.Insertとdb.Save？ ナビゲーションプロパティはdb.Join呼び出しから取得されますか？