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!