ORM 102: A Comparison of Two ORMs

This is part 2 of a 2 part series of articles:

• In part 1, I introduced the concepts of Object Relational Mappers, and demonstrated the integration of a popular Open Source ORM called SubSonic into the Visual Studio Integrated Development Environment (IDE).
• In part 2, I shall be integrating a Kiwi ORM, LightSpeed, into Visual Studio, and comparing the products on functionality.

Who Should Read This Article	You are starting out developing, or have been developing for a while and wondered about ORM. You are classically trained in Entity Relational Modelling and know you need to get your head around Object Oriented Design. You have a BCOM and want to understand what all those BSc’s are talking about.
What Is Covered in This Article	How Do I Use LightSpeed? How Do These Two ORMs Compare? What about LINQ? Summary
How Do I Use LightSpeed?	What Do I Need? ORM: Download a trial version of LightSpeed for free at http://www.mindscape.co.nz/products/LightSpeed/ IDE: Visual Studio 2005 or 2008, any edition RDBMS: SQL Server, MySQL, Oracle, Vista or PostGres Desire: To go home early! How Do I Integrate LightSpeed into Visual Studio? After you have downloaded it, install it.  What is LightSpeed? LightSpeed is a commercially-available ORM created by MindScape, a NZ company.  Unlike SubSonic, LightSpeed is a fully supported product.  How Do I Generate DAL Classes? From Visual Studio, create new Web Application Project (e.g. http://localhost/AdventureWorks/)  Add VB Class Library Project (e.g. AdventureWorks.DataAccess) Add… New Item Under Data, select LightSpeed Model.  Click Add. From the new designer that opens up, go to the Server Explorer Point, click and drag tables (up to 8) from the Server Explorer to the LightSpeed Model designer. Right-mouse-click, select Arrange, to arrange the class definitions into a classic hierarchy. Click Save Add New File… Application Configuration Add a configuration section for a LightSpeed Context: <configSections>    <section name=”lightSpeedContexts” type=”Mindscape.LightSpeed.Configuration.LightSpeedConfigurationSection, Mindscape.LightSpeed” /> </configSections> Add a configuration string section (not shown here). Add a LightSpeed Context section: <lightSpeedContexts>    <add name=”default”             connectionStringName=”DBconnection”             dataProvider=”SqlServer2005”             identityMethod=”KeyTable”             pluralizeTableNames=”false” /> </lightSpeedContexts> Click Save Add New Item… Class Call it e.g. Repository Add the following code: using System; using System.Collections.Generic; using System.Linq; using System.Text; using Mindscape.LightSpeed; namespace Demo9 {    public static class Repository    {       private static LightSpeedContext<ModelUnitOfWork> _context;       public static LightSpeedContext<ModelUnitOfWork> Context;       {          get          {             if (_context == null)             {                _context = new LightSpeedContext<ModelUnitOfWork>();                _context.ConnectionString = LightSpeedContext.Default.ConnectionString;                _context.PluralizeTableNames = LightSpeedContext.Default.PluralizeTableNames;                _context.IdentityMethod = IdentityMethod.KeyTable;             }             return _context;          }       }    } } Click Save Consume DAL Add a Class Item to the project, call it e.g. Product Type the following code under appropriately name and parameterized methods: //The Add Method {    using (ModelUnitOfWork uow = Repository.Context.CreateUnitOfWork())    {       Product product = new Product();       product.Name = “LightSpeed”;       product.Description = “Fantastic .NET O/R Mapper”;             uow.Add(product);       uow.SaveChanges();    } } //The Find Method {    using (ModelUnitOfWork uow = Repository.Context.CreateUnitOfWork())    {       Product product = uow.Products.Where(p => p.Name == '”LightSpeed”).First();    } } //The Delete Method {    using (ModelUnitOfWork uow = Repository.Context.CreateUnitOfWork())    {       Product product = uow.Products.Where(p => p.Name == '”LightSpeed”).First();       uow.Remove(product);    } } Click Save Build and Run (F5)
How Do These Two ORMs Compare?	Feature SubSonic LightSpeed NHibernate Author Rob Conery MindScape Chad Myers Open Source? Yes No Yes Price $0.00 Ed. Limitation Price Exp Up to 8 classes Free! Std Up to 30 classes $99 USD Pro None $299 USD Ent. None (source!) $499 USD $0.00 NZ Made? No Yes No Generates DAL? Yes Yes Yes Mapping Files? No No Yes VS Designer? No Yes No Configuration Web.config Opinionated Convention Config files LINQ Support? Yes Yes Some Focus on Developer speed Performance Enterprise
Summary	In this two-part article, I have shown and compared the functionality of two different Object Relational Mappers.  SubSonic is an Open-Source, simple, free utility for quickly getting data classes out of the database and into .NET design environment.  LightSpeed is a commercial product, designed to optimize the developers experience through industry-standard use of LINQ and a Visual Designer to allow reverse-engineering of new class attributes back into their table columns. In the end, you will decide which criteria is more convenient for you.  It has not been my intention to attempt to persuade you to decide one product over another.  I hope I have adequately explained the purpose and use of Object Relational Mappers in general, by showing these two specific examples. ORMs are great time-savers for developers who have to work with existing database structures.  They cut out the necessity for 90% of stored procedures in one application I was creating, because I didn’t have to worry about the four CRUD procs.  This leaves more time to concentrate on the other 10% of stored procedures that are really necessary, because they can’t be done any other way.  One example that comes to mind is this: suppose you had to count the number of purchase orders that a product appeared in.  The LINQ way to do it is: qry.Where(Columns.ProductID).Equals(productID) qry.From(DAL.Product.Schema) Dim prd As DAL.Product = qry.ExecuteAsCollection(Of DAL.ProductCollection).FirstOrDefault Return prd.PurchaseOrderDetailRecords.Count   However, the problem with this way of doing it is that the line prd.PurchaseOrderDetailRecords.Count, in order to succeed, must first go and get all Purchase Order Detail records.  This is hugely inefficient for a mere count.  The better way to do this is to create a Stored Proc which accepts the Product ID and only returns the integer count.
About the Author	James Hippolite started programming in 1983 on an Apple IIe, at the age of 14. After graduating with a bachelor degree in Information Systems from Victoria University of Wellington in New Zealand in 1990, he started working with small systems relational databases, like dBase, Paradox, FoxPro and finally Microsoft Access. In 1991 he founded Mana Information Systems, a company for SME who couldn’t afford their own IT departments. As the lead developer, he developed small to medium WinForms applications using SQL Server, Visual Basic and latterly ASP.  In 2003 James developed in C# his first .NET web application, an internal metrics reporting tool for his new employer, Telecom New Zealand, utilising SQL Server stored procedures and .NET classes. Due to the absolute ease of use of ORMs, James finally converted to their use in 2008. James is slow adopter of technology, which is no reflection upon his Microsoft Certified Trainer status.  He has contributed lectures on Microsoft Certification and SQL Server Reporting Services to the .NET community. He lives in Wellington and is currently employed full time in a large corporate and loving the regular hours that non-consultants enjoy.