Thursday, October 16, 2008

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

  1. How Do I Use LightSpeed?
  2. How Do These Two ORMs Compare?
  3. What about LINQ?
  4. Summary

How Do I Use LightSpeed?

What Do I Need?

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?

  1. From Visual Studio, create new Web Application Project (e.g. http://localhost/AdventureWorks/
  2. 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
  3. Add New File… Application Configuration
    • Add a configuration section for a LightSpeed Context:
         <section name=”lightSpeedContexts” type=”Mindscape.LightSpeed.Configuration.LightSpeedConfigurationSection, Mindscape.LightSpeed” />
    • Add a configuration string section (not shown here).
    • Add a LightSpeed Context section:
         <add name=”default”
                  pluralizeTableNames=”false” />
    • Click Save
  4. 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;
                  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

  1. Add a Class Item to the project, call it e.g. Product
  2. 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”;

    //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();

  3. Click Save
  4. Build and Run (F5)

How Do These Two ORMs Compare?






Rob Conery


Chad Myers

Open Source?






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


NZ Made?




Generates DAL?




Mapping Files?




VS Designer?






Opinionated Convention

Config files

LINQ Support?




Focus on

Developer speed




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:

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.

1 comment:

  1. James

    Here is an ORM that works with Microsoft Access