Micro ORMs, alternatives to Entity Framework ? Part 4

<< Previous article (Part 3)

Introduction of some Micro ORMs: PetaPoco

Scenario Used

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

PetaPoco

• “Inspired” of Massive, probably a fork of this latter
• Compatible with databases: SQL Server, Oracle, SqlLite, PostgreSql, MySQL, FireBird
• Triple SQL Syntax, LINQ-Like and LINQ / SQL Hybrid
• Interesting performances
• Active Community
• No need to create an AdHoc query to populate an object (relationships are supported)
• Supports .Net Core
• Supports transactions
• Testable unitarily
• Insert, Update, Delete identical to Massive
• Mandatory to add mapping attributes if aliases are used in the SQL query ([Column])
• Doesn’t provide Async queries

Code samples :

Required entities and business objects for our scenario :

[ExplicitColumns]
    public class Orders
    {
        public Orders() { }

        [Column]
        public int Id { get; set; }

        [Column]
        public string ProductName { get; set; }

        [Column]
        public int? Quantity { get; set; }

        [Column]
        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 PetaPocoRepository
    {
        public PetaPocoRepository()
        { }

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

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

        public List GetWorkOrdersWithProduct()
        {
            using (var db = new PetaPoco.Database("AdventureWorks2014"))
            {
                return GetWorkOrdersWithProduct(db);
            }
        }

        private List GetWorkOrdersWithProduct(Database db)
        {
            return db.Fetch<WorkOrder, Product>(@"SELECT TOP 500 WO.*, P.* 
                                                        FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
                                                        INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID").ToList();
        }
    }

Interesting isn’t it? 🙂

What about NPoco ?