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 7

 

<< Previous article (Part 6)

Introduction of some Micro ORMs: Dapper

Scenario Used

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

Dapper

  • Developed by the StackExchange team
  • Compatible with several relational databases: SQL Server, Oracle, Mysql, SqlLite, PostgreSql, FireBird
  • Feature-rich API
  • Very active community
  • Very interesting performance (fastest mapping)
  • Supports .Net Core
  • Supports transactions
  • Testable unitarily easily (DapperWrapper & DapperParameters)
  • Provides Async queries
  • Relationships are not supported, provide a mapper easy to use to populate Adhoc objects

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

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

        public Product Product { get; set; }
    }

    //Db entity
    public class Product
    {
        public int ProductID { get; set; }
        public string Name { get; set; }
    }

Repository sample :

public class DapperRepository
    {
        public DapperRepository()
        { }

        public async Task<List> GetOrders()
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {

                var t = await connection.QueryAsync(@"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");

                return t.ToList();
            }
        }

        // Query with Built-in mapper
        public async Task<List> GetOrders2()
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {

                var t = await connection.QueryAsync<WorkOrder,Product, Orders>(@"SELECT TOP 500 WO.*, P.* 
                                                                        FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
                                                                        INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID", 
                                                                        (w,p) => {
                                                                            return new Orders
                                                                            {
                                                                                Id = w.WorkOrderId,
                                                                                Date = w.DueDate,
                                                                                Quantity = w.OrderQty,
                                                                                ProductName = w.Product.Name
                                                                            };
                                                                        });

                return t.ToList();
            }
        }

        // Multiple sets of data
        public async Task<Tuple<List, List>> GetWorkOrdersAndProducts()
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {
                var data =  await connection.QueryMultipleAsync("SELECT TOP 500 * FROM[AdventureWorks2014].[Production].[WorkOrder];SELECT * FROM [Production].[Product];");

                return Tuple.Create(data.Read().ToList(), data.Read().ToList());
            }
        }

        public async Task GetOrderById(int id)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {

                var t=  await connection.QueryAsync(@"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 t.FirstOrDefault();
            }
        }

        public async Task Add(WorkOrder workOrder)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {
                string sql = @"INSERT INTO [AdventureWorks2014].[Production].[WorkOrder] ([OrderQty], [DueDate]) 
                              VALUES (@OrderQty,@DueDate); 
                              SELECT CAST(SCOPE_IDENTITY() as int)";
                var t = await connection.QueryAsync(sql, workOrder);
                workOrder.WorkOrderId = t.First();

                return workOrder;
            }
        }
        public async Task Update(WorkOrder workOrder)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {
                string sql = @"UPDATE [AdventureWorks2014].[Production].[WorkOrder]
                               SET OrderQty = @OrderQty
                               WHERE (@OrderQty,@DueDate)";
                var t = await connection.QueryAsync(sql, workOrder);
            }
        }

        public async Task Delete(WorkOrder workOrder)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {
                string sql = @"DELETE 
                               FROM [AdventureWorks2014].[Production].[WorkOrder] 
                               WHERE (@OrderQty,@DueDate)";
                var t = await connection.QueryAsync(sql, workOrder);
            }
        }
    }

Incredible right?

 

Now it’s time to compare performances and make some conclusions.