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.

 

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!

Micro ORMs, alternatives to Entity Framework ? Part 5

 

<< Previous article (Part 4)

Introduction of some Micro ORMs: NPoco

Scenario Used

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

NPoco’s origin

NPoco is a fork from PetaPoco itself Inspired of Massive.

PetaPoco has been forked a second time: XPoco released in 2017

NPoco

  • PetaPoco’s fork
  • Same advantages of PetaPoco, with additional features :
  1.  Enriching an existing object
  2.  Supports multiple data sets (like Entity Framework, but more stylish)
  3.  Async queries supported (but not all operations)
  4.  And many others….
  • Syntax almost identical to PetaPoco, simpler in most cases
  1.  Simplified relationship management
  2.  No need for mapping attributes like PetaPoco, aliases are better supported
  • Testable unitarily
  • Less popular than PetaPoco, less active community

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
    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 NPocoRepository
    {           
        public NPocoRepository()
        { }

        public async Task<List> GetOrders()
        {
            using (var db = new Database("AdventureWorks2014"))
            {
                var data = await db.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 data.ToList();

                // Other syntax
                var data2 = await GetWorkOrdersWithProduct(db);
                return data2.Select(x => new Orders { Id = x.WorkOrderId, Date = x.DueDate, Quantity = x.OrderQty, ProductName = x.Product.Name }).ToList();
            }
        }

        public void PopulateExistingOrder(Orders order)
        {
            using (var db = new Database("AdventureWorks2014"))
            {
                db.FirstOrDefaultInto(order, @"SELECT 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 = @0", order.Id);

            }
        }

        public async Task<List> GetWorkOrdersWithProduct()
        {
            using (var db = new Database("AdventureWorks2014"))
            {
                return await GetWorkOrdersWithProduct(db);
            }
        }

        private async Task<List> GetWorkOrdersWithProduct(Database db)
        {
            var data = await db.FetchAsync(@"SELECT TOP 500 WO.*, P.* 
                                                  FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
                                                  INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID");

            return data.ToList();
        }

        public Tuple<List,List> GetWorkOrdersAndProducts()
        {
            using (var db = new Database("AdventureWorks2014"))
            {
                return db.FetchMultiple<WorkOrder, Product>("SELECT TOP 500 * FROM[AdventureWorks2014].[Production].[WorkOrder];SELECT * FROM [Production].[Product];");
            }
        }
        public async Task Add(WorkOrder workOrder)
        {
            using (var db = new Database("AdventureWorks2014"))
            {
                await db.InsertAsync(workOrder);
            }
        }

        public async Task Update(WorkOrder workOrder)
        {
            using (var db = new Database("AdventureWorks2014"))
            {
                await db.UpdateAsync(workOrder);
            }
        }
        public async Task Delete(WorkOrder workOrder)
        {
            using (var db = new Database("AdventureWorks2014"))
            {
                await db.DeleteAsync(workOrder);
            }
        }
    }

More and more fun ? ūüėČ

 

The next is OrmLite !