Micro ORMs, alternatives to Entity Framework ? Part 9

 

<< Previous article (Part 8)

What conclusion(s) can we make ?

Let’s summarize first principals features we described for each Micro ORM before in a user friendly table here:

Legends :

 Disappointing

 Good

 Excellent

 Yes

 No

 Yes, but not verified

Note that they :

  • All support the execution of stored procedures, views, functions
  • Are all protected from SQL injection (queries parameterization)

What we liked ?

  • NPoco for simplicity of its syntax and performance
  • Dapper for its outstanding performance and its amazing community
  • OrmLite for its double LINQ-like and SQL syntax, and for its rich functionalities and performance

Finally….

Even if we love some Micro ORMs and they are more performant than Entity Framework, they don’t provide all Entity Framework’s functionalities, and they don’t support unit tests as Entity Framework does (by mocking its DbContext).

In fact it depends on what you are looking for 🙂

I hope this suite of articles helped you to make a choice 😉

If you need to check the source code, you can find it here : https://github.com/AnthonyGiretti/MicroOrmDemo.net

Micro ORMs, alternatives to Entity Framework ? Part 8

 

<< Previous article (Part 7)

Performance comparisons

I did some benchmarks, only with Select query.

First benchmark scenario

I used single query that bring back from database 500 rows, here the query:

 SELECT TOP 500 [WorkOrderID] AS Id, P.Name AS ProductName, [OrderQty] AS Quantity, [DueDate] AS Date
 FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
 INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID

Second benchmark scenario

I used a serie of 500 queries that returns 1 row:

SELECT [WorkOrderID] AS Id, P.Name AS ProductName, [OrderQty] AS Quantity, [DueDate] AS Date
FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID
WHERE WorkOrderID = @Id

Let’s see what happened….

 

Most of time Micro Orms are faster than Entity Framework, but less than ADO.NET

 

What conclusions can we make ?

Micro ORMs, alternatives to Entity Framework ? Part 6

 

<< Previous article (Part 5)

Introduction of some Micro ORMs: OrmLite

Scenario Used

You can find the scenario used in a last article here.

OrmLite

  • Developed by the ServiceStack team
  • Compatible with several relational databases: SQL Server, Oracle, Mysql, SqlLite, PostgreSql, FireBird, VistaDB
  • Double Syntax LINQ-Like (elegant) and SQL
  • Feature-rich API
  • Active Community
  • Interesting performances
  • Supports .Net Core
  • Supports transactions
  • Testable unitarily
  • Provides Async queries
  • Mandatory to create an AdHoc query to populate an object (relationships are not supported)
  • Mandatory to add schema DataAnnotation Attribute ([Schema(“schemaName”)])

Code samples :

Required entities and business objects for our scenario :

public class Orders
    {
        public Orders() { }

        public int Id { get; set; }
        public string ProductName { get; set; }
        public int? Quantity { get; set; }
        public DateTime? Date { get; set; }
    }

    //Db entity
    [Schema("Production")] //OrmLite set schema
    public class WorkOrder
    {
        public WorkOrder() { }

        public int WorkOrderId { get; set; }
        public int ProductID { get; set; }
        public int? OrderQty { get; set; }
        public int? StockedQty { get; set; }
        public int? ScrappedQty { get; set; }
        public DateTime? StartDate { get; set; }
        public DateTime? EndDate { get; set; }
        public DateTime? DueDate { get; set; }
        public int? ScrapReasonID { get; set; }
        public DateTime? ModifiedDate { get; set; }
    }

    //Db entity
    [Schema("Production")] //OrmLite set schema
    public class Product
    {
        public int ProductID { get; set; }
        public string Name { get; set; }
    }

Repository sample :

public class OrmLiteRepository
    {
        private OrmLiteConnectionFactory _connectionFactory;
        public OrmLiteRepository()
        {
            _connectionFactory = new OrmLiteConnectionFactory(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString(), SqlServer2014Dialect.Provider);
        }

        public async Task<List> GetOrders()
        {
            using (var dbConnection = _connectionFactory.OpenDbConnection())
            {
                var sql = dbConnection
                          .From()
                          .Join((w, p) => w.ProductID == p.ProductID)
                          .Select<WorkOrder,Product>((w,p) => new { Id = w.WorkOrderId, ProductName =  p.Name, Quantity = w.OrderQty, Date = w.DueDate })
                          .Limit(0,500);

                var data = await dbConnection.SelectAsync(sql);
                return data.ToList();
                //SQL Syntax
                return await dbConnection.SelectAsync(@"SELECT TOP 500 [WorkOrderID] AS Id, P.Name AS ProductName, [OrderQty] AS Quantity, [DueDate] AS Date
                                                     FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
                                                     INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID");
            }
        }

        public async Task GetOrderById(int id)
        {
            using (var dbConnection = _connectionFactory.OpenDbConnection())
            {
                // Linq-Like syntax
                var sql = dbConnection
                          .From()
                          .Join((w, p) => w.ProductID == p.ProductID)
                          .Select<WorkOrder, Product>((w, p) => new { Id = w.WorkOrderId, ProductName = p.Name, Quantity = w.OrderQty, Date = w.DueDate })
                          .Where(w => w.WorkOrderId == id);

                var data =  await dbConnection.SelectAsync(sql);
                return data.FirstOrDefault();

                //SQL Syntax
                data = await dbConnection.SelectAsync(@"SELECT TOP 500 [WorkOrderID] AS Id, P.Name AS ProductName, [OrderQty] AS Quantity, [DueDate] AS Date
                                                     FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
                                                     INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID
                                                     WHERE WorkOrderID = @Id", new { Id = id });
                return data.FirstOrDefault();
            }
        }

        public async Task Add(WorkOrder workOrder)
        {
            using (var dbConnection = _connectionFactory.OpenDbConnection())
            {
                await dbConnection.InsertAsync(workOrder);
            }
        }
        public async Task Update(WorkOrder workOrder)
        {
            using (var dbConnection = _connectionFactory.OpenDbConnection())
            {
                await dbConnection.UpdateAsync(workOrder);
            }
        }

        public async Task Delete(WorkOrder workOrder)
        {
            using (var dbConnection = _connectionFactory.OpenDbConnection())
            {
                await dbConnection.DeleteAsync(p=> p.WorkOrderId == workOrder.WorkOrderId);
                //dbConnection.DeleteById(workOrder.WorkOrderId);
            }
        }
    }

Amazing no ? 🙂

 

And now, the last but not the least: Dapper!