ServiceStack.OrmLite Review - micro-ORM for .NET

OrmLite is a friendly micro-ORM open source and commercial license ( free for small projects with a limit of 10 tables). Included in the famous ServiceStack framework (and has high performance - take a look at the benchmark from the developers of Dapper). In this article we will cover the basics of working with OrmLite in conjunction with SQL Server. If you compare OrmLite and the Entity Framework, then immediately striking the lack of context and change tracking (change tracking). And these are not the only differences.

Article layout:

• Preparation for work. Code-first and database-first approaches
• Queries to the database
• JOIN and navigation properties
• Lazy and async
• Transactions
• OrmLite and Entity Framework Performance Comparison
• Conclusion

Interested I invite under kat.

Preparation for work. Code-first and database-first approaches

Install OrmLite in our project:

Install-Package ServiceStack.OrmLite.SqlServer

OrmLite - first of all, code-first ORM. However, it is possible to generate POCO classes based on the existing database. We will start with this generation by installing additional T4 templates:

Install-Package ServiceStack.OrmLite.T4

If everything went well, 3 files will be added to the project:

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

Add a connection string to app / web.config, fill in the ConnectionStringName in the OrmLite.Poco.tt file (optional for a single string in app.config), click on the Run Custom Tool file and get the generated POCO classes, for example:

[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; } } 

OK, the model is ready. Let's make a test query to the database. The OrmLite functionality is accessed via an instance of the OrmLiteConnection class that implements IDbConnection:

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

Let's remember this pattern, then it is implied when referring to the db object.
')
Select all records from the Order table with a Number value greater than 50:

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

Simply!


Let's move on to the code-first approach. Consistently execute DROP and CREATE for our table like this:

  db.DropAndCreateTable<Order>(); 

It should be noted that the classes previously generated using T4 POCO have lost some of the information about the database tables (the length of the string data, foreign keys, etc.). OrmLite provides everything you need to add such information to our POCO (code-first oriented is the same!). The following example creates a nonclustered index, specifies the type nvarchar (20), and creates a foreign key for the Number, Text, and CustomerId fields, respectively:

  [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; } } 

As a result, when db.CreateTable is called, the following SQL query will be executed:

  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); 

Queries to the database

In OrmLite, there are 2 main ways to build queries to the database: lambda expressions and parameterized SQL. The following code demonstrates how to get all the records from the Order table with the specified CustomerId in various ways:

1) lambda expressions and SqlExpression:

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

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

2) parameterized SQL:

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

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

Building simple insert / update / delete queries should also not cause difficulties. Under the spoiler a few examples from the official documentation.

In more detail we will consider more interesting cases.

JOIN and navigation properties

Add the Customer table to the already known Order table:

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

For their internal connection (INNER JOIN) it is enough to execute the code:

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

SQL:

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

Accordingly, for the LEFT JOIN, the q.LeftJoin method is used, etc. To retrieve data from several tables at the same time, method # 1 is to perform the mapping of the resulting sample to the following class OrderInfo:

  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>()); 

SQL:

  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") 

The only prerequisite for the OrderInfo class is that its properties should be named by the pattern {TableName} {FieldName}.
Method number 2 in the style of EF - use the navigation properties (in the terminology of OrmLite, they are referred to as "references").
To do this, add the following property to the Order class:

  [Reference] Customer Customer { get; set; } 

This property will be ignored in any requests of the form db.Select, which is very convenient. To load related entities, use the db.LoadSelect method:

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

SQL:

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

In a similar way, we can initialize the customer.Orders set.

Note: in the examples given, the names of foreign keys in the related tables followed the {Parent} Id pattern, which allowed OrmLite to automatically select the columns to be connected by, thereby simplifying the code. Alternatively, mark foreign keys with the attribute:

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

and explicitly set the table columns for the connection:

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

Lazy and async

Deferred SELECT queries are implemented via IEnumerable. For * Lazy methods, concise queries using lambda expressions are not supported. So SelectLazy is supposed ONLY to use parameterized SQL:

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

that bypassing the enumeration is similar to the following call:

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

For ColumnLazy (returns a list of values ​​in a table column) is additionally supported by SqlExpression:

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

Unlike lazy queries, most of the OrmLite API has async versions:

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

Transactions

Supported:

  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); 

Under the hood of db.OpenTransaction is a call to SqlConnection.BeginTransaction , so we will not dwell on the topic of transactions.

Row group operations OrmLite and Entity Framework Performance Comparison

In addition to the different variations of the execution of SELECT queries, the OrmLite API offers 3 methods for modifying a group of rows in a database:

InsertAll (IEnumerable)
UpdateAll (IEnumerable)
DeleteAll (IEnumerable)

The behavior of OrmLite in this case is no different from the behavior of "adult" ORM, first of all the Entity Framework - we get one INSERT / UPDATE instruction per line in the database. Although it would be interesting to look at the solution for INSERT using the Row Constructor , but not fate. Obviously, the difference in execution speed is formed mainly due to the architectural features of the frameworks themselves. Is there such a big difference?
Below are the measurements of the run-time sampling, insertion and modification of 10 ³ rows from the Order table using the Entity Framework and OrmLite. The iteration is repeated 10 ³ times, and the table shows the total execution time (in seconds). At each iteration, a new set of random data is generated and the table is cleared. The code is available on github .


Runtime in seconds:

	Select	Insert	Update
EF	4.0	282	220
Ormlite	7.3	94	88

OrmLite, are you serious? Select slower than EF? After these results, it was decided to write an additional test that measures the speed of reading 1 line by Id.

Runtime in seconds:

	Select single by id
EF	1.9
Ormlite	1.0

At this time, OrmLite almost double the odds, and that's not bad. I don’t dare to talk about the reasons for the drop in performance when unloading a large number of rows from the database. In most scenarios, OrmLite is still faster than EF, as has been shown, by a factor of 2-3.

Conclusion

At the end of the article I would like to tell about my (certainly, subjective) impressions of working with OrmLite, summarize the advantages and disadvantages of this micro-ORM.

Pros:

• lightweight, easy to configure and deploy;
• simple CRUD requests are really easy to write;

Minuses:

• the variability of query building (either lambda, or parameterized SQL, or SqlExpression) for different methods. There is no uniform universal syntax supported by any method from the API;
• poor documentation: there are no XML comments to methods, official documentation is poorly structured and is located on a single web page;
• unclear API. Try to guess right away what is the difference between db.Select, db.LoadSelect, db.Where calls? Or db.Insert and db.Save? Will navigation properties come from the database when db.Join is called?

These items increase the threshold of entry into the technology. In part, this deprives OrmLite of one of the main advantages of the micro-ORM - simplicity and ease of use compared with the “older” ORM. In general, I had a neutral impression. OrmLite is certainly usable, but more was expected from a commercial product.