ServiceStack.OrmLite Overview - micro-ORM for .NET

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

The outline of the article:

• Preparing for work. Code-first and database-first approaches
• Database Queries
• JOIN and navigation properties
• Lazy and Async
• Transactions
• OrmLite and Entity Framework Performance Comparison
• Conclusion

Interested persons I invite under kat.

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

Install OrmLite in our project:

Install-Package ServiceStack.OrmLite.SqlServer

OrmLite is primarily a code-first ORM. However, it is possible to generate POCO classes based on an existing database. With this generation, we will start by additionally installing 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 ConnectionStringName in the OrmLite.Poco.tt file (optional for a single line 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
	{
		[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 through 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 the records from the Order table with a Number value greater than 50:

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

Simply!


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

      db.DropAndCreateTable();

It should be noted that the classes previously generated using T4 POCO have lost some of the information about the database tables (lengths of string data, foreign keys, etc.). OrmLite provides everything you need to add such information to our POCOs (code-first oriented!). The following example creates a non-clustered 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
    {
        [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); 

Database Queries

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

1) lambda expressions and SqlExpression:

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

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

2) parameterized SQL:

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

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

Building simple insert / update / delete queries should also be straightforward. Under the spoiler are some examples from the official documentation.

In more detail we will consider more interesting cases.

JOIN and navigation properties

Add to the already known Order table the associated Customer 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 orders = db.Select(q => q.Join());

SQL:

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

Accordingly, for LEFT JOIN, the q.LeftJoin method, etc. is used. To obtain data from several tables at the same time, method No. 1 is to map the resulting selection to the following OrderInfo class:

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

    List info = db.Select(db.From().Join());

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 must be named after the {TableName} {FieldName} template.
Method number 2 in the EF style is to use navigation properties (in OrmLite terminology 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 for any queries like db.Select, which is very convenient. To load related entities, you must use the db.LoadSelect method:

    List orders = db.LoadSelect();
    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 set of customer.Orders.

Note: in the above examples, the names of foreign keys in the linked tables followed the {Parent} Id pattern, which allowed OrmLite to automatically select the columns by which the connection is made, thereby simplifying the code. An alternative is to mark foreign keys with an attribute:

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

and explicitly set the table columns for the join:

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

Lazy and Async

Pending SELECT queries are implemented through IEnumerable. For * Lazy methods, laconic queries using lambda expressions are not supported. So SelectLazy assumes ONLY using parameterized SQL:

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

that bypassing the enumeration is similar to the following call:

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

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

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

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

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

Transactions

The following are supported:

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

Under the hood, db.OpenTransaction has a call to SqlConnection.BeginTransaction , so we won’t dwell on the topic of transactions.

Operations on a group of strings. OrmLite and Entity Framework Performance Comparison

In addition to various variations of SELECT query execution, 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" ORMs, primarily the Entity Framework - we get one INSERT / UPDATE statement per row 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 this difference so great?
Below are measurements of the execution time of the selection, insertion, and modification of 10 ³ rows from the Order table using Entity Framework and OrmLite. The iteration is repeated 10 ³ times, and the totalexecution 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 .


Execution time in seconds:

	Select	Insert	Update
Ef	4.0	282	220
Ormite	7.3	94	88

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

Execution time in seconds:

	Select single by id
Ef	1.9
Ormite	1,0

This time, OrmLite has an almost twofold advantage, which is not bad. So far, I do not presume to discuss the reasons for the performance drop when unloading a large number of rows from the database. In most scenarios, OrmLite is still faster than EF, as shown - 2-3 times.

Conclusion

At the end of the article, I would like to talk about my (certainly subjective) impressions of working with OrmLite, to 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:

• variability of query construction (either lambdas, then parameterized SQL, then SqlExpression) for various methods. There is no single universal syntax supported by any method from the API;
• poor documentation: there are no XML comments on methods, official documentation is poorly structured and is located on a single web page;
• obscure API. Try to guess right away how the db.Select, db.LoadSelect, db.Where calls differ? Or db.Insert and db.Save? Will navigation properties come from the db.Join call?

These items increase the "threshold" for entering the technology. In part, this deprives OrmLite of one of the main advantages of micro-ORM - simplicity and ease of use compared to "adult" ORM. In general, I had a neutral impression. OrmLite is certainly usable, but more was expected from a commercial product.