An Active Record Implementation with LINQ


Tuesday, 9 February 2010 Login \| Register

What follows is my initial attempt to create an active record implementation with LINQ. The design is heavily influenced by the active record implementation in ruby on rails, and as such it makes the assumption that all entity tables have a single column integer primary key. Converting it to handle composite primary keys is trivial. In the future I will be adding validation support to the active record class, and once again I expect to following a similar design to that used by ruby on rails.

The active record base class exposeses the following public methods:

public static T Find(object Id);

public static T FindFirst(Funcbool> exp);

public static IEnumerable FindAll(Funcbool> exp);

public void Delete(T entity);

public int DeleteById(object Id);

public static T NewEntity();

FindAll is a flexible query method that accepts a lambda expression for a query. It returns IEnumerable which is a reference to a query. This means that the query can be passed to the UI allowing for some very flexible querying options. The query will not be executed until the IEnumerable collection is accessed. The full source of the active record base class is:

   1:  public class ActiveRecordBase where T : class

   2:      {

   3:          #region Public Methods

4:

   5:          public static T Find(object Id)

   6:          {

   7:              AssertSinglePK();

   8:              string sql = "select * from "   TableMetadata.TableName   " where "   PrimaryKeyName   "={0}";

   9:              List entityList = WebDataContextFactory.Context.ExecuteQuery(sql, Id).ToList();

  10:              if (entityList.Count == 0) throw new ApplicationException("Entity of type "   ClassMetadata.Name   " with Id = "   Id.ToString()   " not found.");

  11:              if (entityList.Count > 1) throw new ApplicationException("Primary key is not unique.");

  12:              return entityList[0];

  13:          }

14:

  15:          public static T FindFirst(Funcbool> exp)

  16:          {

  17:              return GetTable.First(exp);

  18:          }

19:

  20:          public static IEnumerable FindAll(Funcbool> exp)

  21:          {

  22:              return GetTable.Where(exp);

  23:          }

24:

  25:          public void Delete(T entity)

  26:          {

  27:              GetTable.DeleteOnSubmit(entity);

  28:              WebDataContextFactory.Context.SubmitChanges();

  29:          }

30:

  31:          public int DeleteById(object Id)

  32:          {

  33:              AssertSinglePK();

  34:              return WebDataContextFactory.Context.ExecuteCommand(

  35:                  "delete from "   GetTable   " where "   PrimaryKeyName   "={0}",Id);

  36:          }

37:

  38:          public static T NewEntity()

  39:          {

  40:              T entity = Activator.CreateInstance();

  41:              GetTable.Attach(entity, true);

  42:              return entity;

  43:          }

44:

  45:          #endregion

46:

  47:          #region Private Methods

48:

  49:          private static void AssertSinglePK()

  50:          {

  51:              if (TableMetadata.RowType.IdentityMembers.Count < 1)

  52:                  throw new ApplicationException(TableMetadata.TableName   " doesn't have a primary key. Not supported for a business object mapping table.");

  53:              if (TableMetadata.RowType.IdentityMembers.Count > 1)

  54:                  throw new ApplicationException(TableMetadata.TableName   " has a composite primary key. Not supported for a business object mapping table.");

  55:          }

56:

  57:          #endregion

58:

  59:          #region Properties

60:

  61:          private static string PrimaryKeyName

  62:          {

  63:              get { return TableMetadata.RowType.IdentityMembers[0].Name; }

  64:          }

65:

  66:          private static Table GetTable

  67:          {

  68:              get { return WebDataContextFactory.Context.GetTable(); }

  69:          }

70:

  71:          private static MetaTable TableMetadata

  72:          {

  73:              get { return WebDataContextFactory.Context.Mapping.GetTable(typeof(T)); }

  74:          }

75:

  76:          private static MetaType ClassMetadata

  77:          {

  78:              get { return WebDataContextFactory.Context.Mapping.GetMetaType(typeof(T)); }

  79:          }

80:

  81:          #endregion

  82:      }

To demonstrate usage I will use the Products table from the Northwind sample database. The LINQ to Sql class generator generates the following class:

   1:  [Table(Name = "dbo.Products")]

   2:      public partial class Product : ActiveRecordBase, INotifyPropertyChanging, INotifyPropertyChanged

   3:      {

4:

   5:          private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);

   6:          private int _ProductID;

   7:          private string _ProductName;

   8:          private System.Nullable<int> _SupplierID;

   9:          private System.Nullable<int> _CategoryID;

  10:          private string _QuantityPerUnit;

  11:          private System.Nullable<decimal> _UnitPrice;

  12:          private System.Nullable<short> _UnitsInStock;

  13:          private System.Nullable<short> _UnitsOnOrder;

  14:          private System.Nullable<short> _ReorderLevel;

  15:          private bool _Discontinued;

  16:          private EntityRef _Category;

17:

  18:          #region Extensibility Method Definitions

  19:          partial void OnLoaded();

  20:          partial void OnValidate(System.Data.Linq.ChangeAction action);

  21:          partial void OnCreated();

  22:          partial void OnProductIDChanging(int value);

  23:          partial void OnProductIDChanged();

  24:          partial void OnProductNameChanging(string value);

  25:          partial void OnProductNameChanged();

  26:          partial void OnSupplierIDChanging(System.Nullable<int> value);

  27:          partial void OnSupplierIDChanged();

  28:          partial void OnCategoryIDChanging(System.Nullable<int> value);

  29:          partial void OnCategoryIDChanged();

  30:          partial void OnQuantityPerUnitChanging(string value);

  31:          partial void OnQuantityPerUnitChanged();

  32:          partial void OnUnitPriceChanging(System.Nullable<decimal> value);

  33:          partial void OnUnitPriceChanged();

  34:          partial void OnUnitsInStockChanging(System.Nullable<short> value);

  35:          partial void OnUnitsInStockChanged();

  36:          partial void OnUnitsOnOrderChanging(System.Nullable<short> value);

  37:          partial void OnUnitsOnOrderChanged();

  38:          partial void OnReorderLevelChanging(System.Nullable<short> value);

  39:          partial void OnReorderLevelChanged();

  40:          partial void OnDiscontinuedChanging(bool value);

  41:          partial void OnDiscontinuedChanged();

  42:          #endregion

43:

  44:          public Product()

  45:          {

  46:              this._Category = default(EntityRef);

  47:              OnCreated();

  48:          }

49:

  50:          [Column(Storage = "_ProductID", AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]

  51:          public int ProductID

  52:          {

  53:              get

  54:              {

  55:                  return this._ProductID;

  56:              }

  57:              set

  58:              {

  59:                  if ((this._ProductID != value))

  60:                  {

  61:                      this.OnProductIDChanging(value);

  62:                      this.SendPropertyChanging();

  63:                      this._ProductID = value;

  64:                      this.SendPropertyChanged("ProductID");

  65:                      this.OnProductIDChanged();

  66:                  }

  67:              }

  68:          }

69:

  70:          [Column(Storage = "_ProductName", DbType = "NVarChar(40) NOT NULL", CanBeNull = false)]

  71:          public string ProductName

  72:          {

  73:              get

  74:              {

  75:                  return this._ProductName;

  76:              }

  77:              set

  78:              {

  79:                  if ((this._ProductName != value))

  80:                  {

  81:                      this.OnProductNameChanging(value);

  82:                      this.SendPropertyChanging();

  83:                      this._ProductName = value;

  84:                      this.SendPropertyChanged("ProductName");

  85:                      this.OnProductNameChanged();

  86:                  }

  87:              }

  88:          }

89:

  90:          [Column(Storage = "_SupplierID", DbType = "Int")]

  91:          public System.Nullable<int> SupplierID

  92:          {

  93:              get

  94:              {

  95:                  return this._SupplierID;

  96:              }

  97:              set

  98:              {

  99:                  if ((this._SupplierID != value))

 100:                  {

 101:                      this.OnSupplierIDChanging(value);

 102:                      this.SendPropertyChanging();

 103:                      this._SupplierID = value;

 104:                      this.SendPropertyChanged("SupplierID");

 105:                      this.OnSupplierIDChanged();

 106:                  }

 107:              }

 108:          }

 109:

 110:          [Column(Storage = "_CategoryID", DbType = "Int")]

 111:          public System.Nullable<int> CategoryID

 112:          {

 113:              get

 114:              {

 115:                  return this._CategoryID;

 116:              }

 117:              set

 118:              {

 119:                  if ((this._CategoryID != value))

 120:                  {

 121:                      if (this._Category.HasLoadedOrAssignedValue)

 122:                      {

 123:                          throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();

 124:                      }

 125:                      this.OnCategoryIDChanging(value);

 126:                      this.SendPropertyChanging();

 127:                      this._CategoryID = value;

 128:                      this.SendPropertyChanged("CategoryID");

 129:                      this.OnCategoryIDChanged();

 130:                  }

 131:              }

 132:          }

 133:

 134:          [Column(Storage = "_QuantityPerUnit", DbType = "NVarChar(20)")]

 135:          public string QuantityPerUnit

 136:          {

 137:              get

 138:              {

 139:                  return this._QuantityPerUnit;

 140:              }

 141:              set

 142:              {

 143:                  if ((this._QuantityPerUnit != value))

 144:                  {

 145:                      this.OnQuantityPerUnitChanging(value);

 146:                      this.SendPropertyChanging();

 147:                      this._QuantityPerUnit = value;

 148:                      this.SendPropertyChanged("QuantityPerUnit");

 149:                      this.OnQuantityPerUnitChanged();

 150:                  }

 151:              }

 152:          }

 153:

 154:          [Column(Storage = "_UnitPrice", DbType = "Money")]

 155:          public System.Nullable<decimal> UnitPrice

 156:          {

 157:              get

 158:              {

 159:                  return this._UnitPrice;

 160:              }

 161:              set

 162:              {

 163:                  if ((this._UnitPrice != value))

 164:                  {

 165:                      this.OnUnitPriceChanging(value);

 166:                      this.SendPropertyChanging();

 167:                      this._UnitPrice = value;

 168:                      this.SendPropertyChanged("UnitPrice");

 169:                      this.OnUnitPriceChanged();

 170:                  }

 171:              }

 172:          }

 173:

 174:          [Column(Storage = "_UnitsInStock", DbType = "SmallInt")]

 175:          public System.Nullable<short> UnitsInStock

 176:          {

 177:              get

 178:              {

 179:                  return this._UnitsInStock;

 180:              }

 181:              set

 182:              {

 183:                  if ((this._UnitsInStock != value))

 184:                  {

 185:                      this.OnUnitsInStockChanging(value);

 186:                      this.SendPropertyChanging();

 187:                      this._UnitsInStock = value;

 188:                      this.SendPropertyChanged("UnitsInStock");

 189:                      this.OnUnitsInStockChanged();

 190:                  }

 191:              }

 192:          }

 193:

 194:          [Column(Storage = "_UnitsOnOrder", DbType = "SmallInt")]

 195:          public System.Nullable<short> UnitsOnOrder

 196:          {

 197:              get

 198:              {

 199:                  return this._UnitsOnOrder;

 200:              }

 201:              set

 202:              {

 203:                  if ((this._UnitsOnOrder != value))

 204:                  {

 205:                      this.OnUnitsOnOrderChanging(value);

 206:                      this.SendPropertyChanging();

 207:                      this._UnitsOnOrder = value;

 208:                      this.SendPropertyChanged("UnitsOnOrder");

 209:                      this.OnUnitsOnOrderChanged();

 210:                  }

 211:              }

 212:          }

 213:

 214:          [Column(Storage = "_ReorderLevel", DbType = "SmallInt")]

 215:          public System.Nullable<short> ReorderLevel

 216:          {

 217:              get

 218:              {

 219:                  return this._ReorderLevel;

 220:              }

 221:              set

 222:              {

 223:                  if ((this._ReorderLevel != value))

 224:                  {

 225:                      this.OnReorderLevelChanging(value);

 226:                      this.SendPropertyChanging();

 227:                      this._ReorderLevel = value;

 228:                      this.SendPropertyChanged("ReorderLevel");

 229:                      this.OnReorderLevelChanged();

 230:                  }

 231:              }

 232:          }

 233:

 234:          [Column(Storage = "_Discontinued", DbType = "Bit NOT NULL")]

 235:          public bool Discontinued

 236:          {

 237:              get

 238:              {

 239:                  return this._Discontinued;

 240:              }

 241:              set

 242:              {

 243:                  if ((this._Discontinued != value))

 244:                  {

 245:                      this.OnDiscontinuedChanging(value);

 246:                      this.SendPropertyChanging();

 247:                      this._Discontinued = value;

 248:                      this.SendPropertyChanged("Discontinued");

 249:                      this.OnDiscontinuedChanged();

 250:                  }

 251:              }

 252:          }

 253:

 254:          [Association(Name = "Category_Product", Storage = "_Category", ThisKey = "CategoryID", IsForeignKey = true)]

 255:          public Category Category

 256:          {

 257:              get

 258:              {

 259:                  return this._Category.Entity;

 260:              }

 261:              set

 262:              {

 263:                  Category previousValue = this._Category.Entity;

 264:                  if (((previousValue != value)

 265:                              || (this._Category.HasLoadedOrAssignedValue == false)))

 266:                  {

 267:                      this.SendPropertyChanging();

 268:                      if ((previousValue != null))

 269:                      {

 270:                          this._Category.Entity = null;

 271:                          previousValue.Products.Remove(this);

 272:                      }

 273:                      this._Category.Entity = value;

 274:                      if ((value != null))

 275:                      {

 276:                          value.Products.Add(this);

 277:                          this._CategoryID = value.CategoryID;

 278:                      }

 279:                      else

 280:                      {

 281:                          this._CategoryID = default(Nullable<int>);

 282:                      }

 283:                      this.SendPropertyChanged("Category");

 284:                  }

 285:              }

 286:          }

 287:

 288:          public event PropertyChangingEventHandler PropertyChanging;

 289:

 290:          public event PropertyChangedEventHandler PropertyChanged;

 291:

 292:          protected virtual void SendPropertyChanging()

 293:          {

 294:              if ((this.PropertyChanging != null))

 295:              {

 296:                  this.PropertyChanging(this, emptyChangingEventArgs);

 297:              }

 298:          }

 299:

 300:          protected virtual void SendPropertyChanged(String propertyName)

 301:          {

 302:              if ((this.PropertyChanged != null))

 303:              {

 304:                  this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));

 305:              }

 306:          }

 307:      }

At line 1 I modified the generated class to inherit from the generic base class ActiveRecordBase. That is all that is required to make Product an active record class. This class can be used as follows:

   1:  protected void Page_Load(object sender, EventArgs e)

   2:      {

   3:          IEnumerable productsWith70 = Product.FindAll(product => product.UnitsOnOrder == 70);

   4:          grdProducts.DataSource = productsWith70;

   5:          grdProducts.DataBind();

6:

   7:          productsWith70.First().ProductName = productsWith70.First().ProductName   "xx3";

   8:          WebDataContextFactory.SaveAll();

   9:      }

Line 3 is an example of using the FindAll method with a lambda expression to provide the query.