In quite a few of my projects I've had to write a web service that handles requests from mobile devices to a SQL Server DB. Personally I'm not a fan of SQL so I mostly use LINQ to SQL and LINQ to objects in my web services. To be quite honest I've gotten tired of writing a million LINQ  queries on each table in the database. So I put together a simple class acting as a wrapper for a data model context when using "LINQ to SQL Classes" in Visual Studio. The idea is that I don't want to have write code for each entity type to populate entity fields when updating fields, or writing code for each entity type that I'd like to delete i.e. (querying it and then deleting). Not to mention issues of having to update my code each time I change my database. Use the code below, I don't have to edit a single line when updating any field in my database. I only have to update my data model.  Anyway the point is that I wanted a generic class that handles any entity type and performs all the regular actions on the database.

Firstly, this class only handles databases where surrogate keys are used on all the tables. Obviously you can inherit from this class and add your own entity specific code, but basically this generic database context wrapper should be able to accept an entity of any type  and perform any of the following basic functions:

  • Save (Insert/Update).
  • Get entity by surrogate key.
  • Get multiple entities by a specific field as a filter.
  • Get all entities.
  • Delete an entities.
  • Have the option of creating a tombstone record of the one deleted into a tombstone table (specified on the method call).
  • Delete all entities of a specific type.
  • Return a list of changes.

This class will need to use both generics and a bit of reflection on the types to determine which is the primary key, whether it's an identity column etc. Furthermore since LINQ to SQL doesn't support method calls withing the queries, I loop through the entities in some cases. These are obviously issues what will impact  performance, so you'll have to determine for yourself if performing such calls are worth the ease of coding over the performance hit.

Alright so firstly it should accept a System.Data.Linq.DataContext in its constructor and use it for all operations. In my constructor I have an option for applying settings which I deserialize from an XML settings file, but I won't go into detail about that.

[sourcecode language="csharp"]
#region Constructors

/// <summary>
/// Creates a new LINQ to SQL context.
/// </summary>
/// <param name="db">The LINQ to SQL DataContext that must contain all the entity types etc.</param>
/// <param name="applyContextSettings">Determines whether the settings in the settings file should be applied to the DataContext</param>
public PTXLINQContext(DataContext db, bool applyContextSettings)
{
DB = db;
if (applyContextSettings)
{
_db.Connection.ConnectionString = PTXLINQOptions.Instance.Settings.ConnectionString;
_db.CommandTimeout = PTXLINQOptions.Instance.Settings.LinqToSQLCommandTimeout;
}
}

#endregion //Constructors

#region Fields

private DataContext _db;

#endregion //Fields
[/sourcecode]

Then it should be able to save any an entity of any type. It basically gets the surrogate key of the entity type and its value. Determines whether it's an indetity column, and if so inserts the entity in the appropriate table corresponding the class type specified. If it's not, then it determines whether it already exists in order to know whether an update or insert is required. It also returns a list of change results to indicate to the caller what fields where updated if any, or if an insert was performed instead.

[sourcecode language="csharp"]
/// <summary>
/// Saves (updates/inserts) an entity to the table corrseponding to the entity type.
/// If the entity's surrogate key is an identity entity will be inserted and not updated.
/// </summary>
/// <typeparam name="E">The type of the entity i.e. which table it will be saved to.</typeparam>
/// <param name="entity">The the entity to save.</param>
/// <returns>Returns a list of change results i.e. what entities where updated</returns>
protected List<ChangeResult> Save<E>(E entity) where E : class
{
PropertyInfo surrogateKey = GetEntitySurrogateKey<E>();
bool containsIdentityColumn = IsIdentityColumn(surrogateKey);
Type entityType = typeof(E);
E original = null;
if (!containsIdentityColumn)
{
original = GetEntityBySurrogateKey<E>(surrogateKey.GetValue(entity, null));
}
List<ChangeResult> result = null;
if ((containsIdentityColumn) || (original == null))
{
DB.GetTable<E>().InsertOnSubmit(entity);
DB.SubmitChanges();
result = new List<ChangeResult>();
result.Add(new ChangeResult()
{
Function = "INSERT",
DateChanged = DateTime.Now,
EntityChanged = entityType.Name,
FieldChanged = surrogateKey.Name,
});
}
else
{
result = UpdateOriginalEntity<E>(original, entity);
}
DB.SubmitChanges();
return result;
}
[/sourcecode]

Here's the method for getting an entity's surrogate key. We simply get all the properties of the entity type and return the PropertyInfo that has the DataColumn attribute with its IsPrimaryKey property set to true.

[sourcecode language="csharp"]
/// <summary>
/// Determines the primary key of an entity type. The first primary key found on the entity type i.e.
/// the assumption is made that the entity type only has one primary key, which is the surrogate key.
/// </summary>
/// <typeparam name="E">The entity type i.e. the table whose surrogate key needs to be determined.</typeparam>
/// <returns>Retruns the PropertyInfo corresponding to the column which is the surrogate key for the specified entity type.</returns>
protected PropertyInfo GetEntitySurrogateKey<E>()
{
PropertyInfo[] properties = typeof(E).GetProperties();
PropertyInfo surrogateKey = null;
foreach (PropertyInfo p in properties)
{
object[] attributes = p.GetCustomAttributes(typeof(ColumnAttribute), false);
ColumnAttribute columnAttribute = attributes.Length < 1 ? null : (ColumnAttribute)attributes[0];
if ((columnAttribute == null) || (!columnAttribute.IsPrimaryKey))
{
continue;
}
if (surrogateKey != null)
{
throw new Exception(
string.Format("{0} has more than one primary key. A surrogate key has to be a single field.",
typeof(E).Name));
}
surrogateKey = p;
}
if (surrogateKey == null)
{
throw new NullReferenceException(string.Format("{0} does not have surrogate key.", typeof(E).Name));
}
return surrogateKey;
}
[/sourcecode]

To be able to update the original entity from the data source with the one given by the caller you might have noticed in the above method that we call the following method. It simply gets all the properties of the original entity that have the DataColumn attribute and looks for the corresponding property of the latest entity. It then copies the value from one entity to the other:

[sourcecode language="csharp"]

/// <summary>
/// Updates the original entity with values of the latest entities. In other words, it copies the
/// column values of the latest entity to the original entity.
/// </summary>
/// <typeparam name="E">The entity type i.e. the table whose original record will be updated.</typeparam>
/// <param name="original">The original entity retrieved from the database.</param>
/// <param name="latest">The latest entity received from the client.</param>
/// <returns>Returns a list of change results containing all the fields that were changed and their original and new values.</returns>
private List<ChangeResult> UpdateOriginalEntity<E>(E original, E latest)
{
List<ChangeResult> result = new List<ChangeResult>();
Type entityType = typeof(E);
PropertyInfo[] properties = entityType.GetProperties();
foreach (PropertyInfo p in properties)
{
object[] attributes = p.GetCustomAttributes(typeof(ColumnAttribute), false);
ColumnAttribute columnAttribute = attributes.Length < 1 ? null : (ColumnAttribute)attributes[0];
if ((columnAttribute == null) || (columnAttribute.IsPrimaryKey))
{
continue;
}
object originalValue = p.GetValue(original, null);
object latestValue = p.GetValue(latest, null);
if (object.Equals(originalValue, latestValue))
{
continue;
}
result.Add(new ChangeResult()
{
Function = "UPDATE FIELD",
DateChanged = DateTime.Now,
EntityChanged = entityType.Name,
FieldChanged = p.Name,
OriginalValue = originalValue,
NewValue = latest,
});
p.SetValue(original, latestValue, null);
}
return result;
}
[/sourcecode]

This is what the ChangeResult looks like. We basically just keep track of the table changed, when it happened, what the old and new value is etc. All methods that modify the data in the data source return a list of ChangeResult objects.

[sourcecode language="csharp"]
namespace PTX.LINQ.Utilities
{
#region Using Directives

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

#endregion //Using Directives

public class ChangeResult
{
#region Properties

/// <summary>
/// The function which was being performed.
/// </summary>
public string Function { get; set; }

/// <summary>
/// The date on which the change was made.
/// </summary>
public DateTime DateChanged { get; set; }

/// <summary>
/// The entity on which the change was made.
/// </summary>
public string EntityChanged { get; set; }

/// <summary>
/// The field on the entity which was changed.
/// </summary>
public string FieldChanged { get; set; }

/// <summary>
/// The original value of the field which was changed on the entity.
/// </summary>
public object OriginalValue { get; set; }

/// <summary>
/// The new value that was set on the field which was changed on the entity.
/// </summary>
public object NewValue { get; set; }

#endregion //Properties
}
}
[/sourcecode]

Here's the method that determines whether a column property of an entity is an identity field. It accepts a PropertyInfo and determines whether it has the DataColumn attribute. On that attribute it looks on the DbType property and determines if it contains the "IDENTITY" text.

[sourcecode language="csharp"]
/// <summary>
/// Determines whether a property is an identity column.
/// </summary>
/// <param name="p"></param>
/// <returns>Returns true if the property is an identity column.</returns>
protected bool IsIdentityColumn(PropertyInfo p)
{
object[] attributes = p.GetCustomAttributes(typeof(ColumnAttribute), false);
ColumnAttribute columnAttribute = attributes.Length < 1 ? null : (ColumnAttribute)attributes[0];
if (columnAttribute == null)
{
return false;
}
return columnAttribute.DbType.Contains("IDENTITY");
}
[/sourcecode]

There's two overloads for deleting a single entity. One allows for the creation of a tombstone record and the other simply deletes the a record without the caller specifying entity type of the tombstone table to be deleted. These methods also return change results. The tombstone entity is simply created based on the entity passed in as the tombstone type (T) and then copying the fields from one entity to the other.

[sourcecode language="csharp"]
/// <summary>
/// Deletes an entity from the table corresponding to the entity type.
/// </summary>
/// <typeparam name="E">The entity type i.e. which table it will be deleted from.</typeparam>
/// <param name="entity">The entity to be deleted.</param>
/// <returns>Returns a list of change results.</returns>
protected List<ChangeResult> Delete<E>(E entity) where E : class
{
return this.Delete<E, object>(entity, false);
}

/// <summary>
/// Deletes an entity from the table correspoding to the entity type (E) and creates
/// a tombstone in the table correspoging to the tombstone entity type (T) if the
/// createTombstone flag is set to true.
/// </summary>
/// <typeparam name="E">The entity type of the entity which will be deleted i.e. the table from where it will be deleted.</typeparam>
/// <typeparam name="T">The tombstone entity type i.e. the table where an tombstone will be created.</typeparam>
/// <param name="entity">The entity to be deleted</param>
/// <param name="createTombstone">Indicates whether a tombstone should be created.</param>
/// <returns>Returns a list of change results.</returns>
protected List<ChangeResult> Delete<E, T>(E entity, bool createTombstone) where E : class where T : class
{
PropertyInfo surrogateKey = GetEntitySurrogateKey<E>();
E original = GetEntityBySurrogateKey<E>(surrogateKey.GetValue(entity, null));
if (original == null)
{
throw new Exception(
string.Format("Could not find entity with key {0} and value {1} to delete.",
GetEntitySurrogateKey<E>().Name,
GetEntitySurrogateKey<E>().GetValue(entity, null)));
}
if (createTombstone)
{
T tombstone = Activator.CreateInstance<T>();
CopyToTombstoneEntity<E, T>(original, tombstone);
DB.GetTable<T>().InsertOnSubmit(tombstone);
}
DB.GetTable<E>().DeleteOnSubmit(original);
DB.SubmitChanges();

List<ChangeResult> result = new List<ChangeResult>();
result.Add(new ChangeResult()
{
Function = "DELETE",
DateChanged = DateTime.Now,
EntityChanged = typeof(E).Name,
FieldChanged = surrogateKey.Name,
});
return result;
}
[/sourcecode]

This is the method called to copy the field values of the entity to be deleted to its tombstone entity. It simply goes through all the properties of the original entity type and determines whether it has the DataColumn attribute. If so, it looks for a corresponding field on the tombstone entity's type and the copies the value across.

[sourcecode language="csharp"]
/// <summary>
/// Copies all the values from the original entity to a tombstone entity. If the fields/columns on the two entities
/// do not match an exception will be thrown.
/// </summary>
/// <param name="original">The original entity retrieved from the database.</param>
/// <param name="tombstone">The tombstone entity containing the same fields/columns as the original entity.</param>
public void CopyToTombstoneEntity<E, T>(E original, T tombstone)
where E : class
where T : class
{
Type originalType = typeof(E);
Type tombstoneType = typeof(T);
PropertyInfo[] properties = originalType.GetProperties();
foreach (PropertyInfo p in properties)
{
object[] attributes = p.GetCustomAttributes(typeof(ColumnAttribute), false);
ColumnAttribute columnAttribute = attributes.Length < 1 ? null : (ColumnAttribute)attributes[0];
if (columnAttribute == null)
{
continue;
}
PropertyInfo tombstoneProperty = tombstoneType.GetProperty(p.Name);
if (tombstoneProperty == null)
{
throw new NullReferenceException(
string.Format(
"Could not find property on tombstone entity with the name {0}.",
p.Name));
}
object originalValue = p.GetValue(original, null);
tombstoneProperty.SetValue(tombstone, originalValue, null);
}
}
[/sourcecode]

Then for mass deleting entities there's two methods. One for deleting the entire table and the other deleting entities older than a certain time span e.g. older than 5 days.

[sourcecode language="csharp"]
/// <summary>
/// Deletes all the entities in a given table older than the time specified.
/// </summary>
/// <typeparam name="E">The entity type of the entity which will be deleted i.e. the table from where it will be deleted.</typeparam>
/// <param name="dateFieldName">The field name on the entity which must a date time field .</param>
/// <param name="time">The time relative to the current time i.e. current time subracted by the this time sets the threshhold for entities deleted.</param>
public void DeleteOlderThan<E>(string dateFieldName, TimeSpan time) where E : class
{
DateTime threshold = DateTime.Now.Subtract(time);
Table<E> table = DB.GetTable<E>();
PropertyInfo dateField = typeof(E).GetProperty(dateFieldName);
if ((dateField == null) || (dateField.PropertyType != typeof(DateTime)))
{
throw new Exception(
string.Format("Entity {0} does not contain the DateTime field with the name {1}",
typeof(E).GetType().Name,
dateFieldName));
}
List<E> toDelete = new List<E>();
foreach (E e in table)
{
if ((DateTime)dateField.GetValue(e, null) < threshold)
{
table.DeleteOnSubmit(e);
}
}
DB.SubmitChanges();
}

/// <summary>
/// Deletes all entities from the table corresponding to the entity type.
/// </summary>
/// <typeparam name="E">The entity type i.e. of the table whose records (entities) will be deleted.</typeparam>
protected List<ChangeResult> DeleteAll<E>() where E : class
{
Table<E> table = DB.GetTable<E>();
table.DeleteAllOnSubmit(table.ToList<E>());
DB.SubmitChanges();

List<ChangeResult> result = new List<ChangeResult>();
result.Add(new ChangeResult()
{
Function = "DELETE ALL",
DateChanged = DateTime.Now,
EntityChanged = typeof(E).Name,
});
return result;
}
[/sourcecode]

Here's the methods for retrieving entities; either by surrogate key, all or filtered by a field in the database. These are pretty straight forward.

[sourcecode language="csharp"]
/// <summary>
/// Queries for and returns an entity from the table corresponding to the entity type. The query is performed
/// on the surrogate key of the entity.
/// </summary>
/// <typeparam name="E">The entity type i.e. which table the entity will be queried from.</typeparam>
/// <param name="keyValue"></param>
/// <returns>Returns an entity with the specified type and surrogate key. Returns null if one is not found.</returns>
protected E GetEntityBySurrogateKey<E>(object keyValue) where E : class
{
PropertyInfo surrogateKey = GetEntitySurrogateKey<E>();
List<E> results = new List<E>();
foreach (E t in DB.GetTable<E>())
{
if (object.Equals(surrogateKey.GetValue(t, null), keyValue))
{
return t;
}
}
return null;
}

/// <summary>
/// Queries for entities in a table corresponding to entity type. The query is performed on the column/field
/// specified with the specified field value.
/// </summary>
/// <typeparam name="E">The entity type i.e. the table from which the entities will be returned.</typeparam>
/// <param name="fieldName">The name of the field/column on which the query will be performed.</param>
/// <param name="fieldValue">The value of the field which will be used for the query.</param>
/// <returns>Returns a list of entities of the specified type with the specified field/column and field value.</returns>
protected List<E> GetEntitiesByField<E>(string fieldName, object fieldValue) where E : class
{
PropertyInfo field = typeof(E).GetProperty(fieldName);
if (field == null)
{
throw new NullReferenceException(
string.Format("Entity {0} does not contain a field with the name {1}.",
typeof(E).Name,
fieldName));
}
//return DB.GetTable<E>().Where<E>(p => field.GetValue(p, null) == fieldValue).ToList();
List<E> results = new List<E>();
foreach (E t in DB.GetTable<E>())
{
if (object.Equals(field.GetValue(t, null), fieldValue))
{
results.Add(t);
}
}
return results;
}

/// <summary>
/// Queries for all the entities in a table corresponging to the specfied entity type.
/// </summary>
/// <typeparam name="E">The entity type i.e. the table from which the entities will be returned.</typeparam>
/// <returns>Returns all the entities of the specified type.</returns>
protected List<E> GetAllEntities<E>() where E : class
{
return DB.GetTable<E>().ToList();
}
[/sourcecode]

Source code can be downloaded from here.