LINQ to SQL: All common operations (Insert, Update, Delete, Get) in one base class

• Download source code - 2.13 KB
• Download updated source code with mapping providers - 6.74 KB

Introduction

I have been playing with LINQ to SQL for a bit. It is great and easy to use along with the designer that ships with VS.NET 2008. I wanted to create a Facade layer that integrates with LINQ to SQL classes. The old school way is to create a public class (Manager) and this class calls your database mappers to get/set the required info. Now, LINQ to SQL replaces those mapper classes, and the way to do your operations is to open the DataContext and start defining queries to perform such operations.

So, I grouped those queries and operations into another separate layer called Processes/Operations layer in which the facade will end up calling. By doing that, the Facade remains as is and the Processes/Operations layer replaces the mappers layer while it encapsulates the whole logic and complexity for the DB operations.

Having said that, I created a base class which encapsulates the most common logic for database operations in the Processes/Operations layer instead of repeating the same code over and over for each class.

Note: This article is intended for people familiar with LINQ and LINQ to SQL.

Using the code

Class definition

Shown below is the definition of the class:

internal class DataBaseProcessBase<T, DC> where T : 
               class, new() where DC : DataContext, new()

As you can notice, this is a generic class taking two types: the first one is your entity type, while the second is your DataContext. Moving forward, here are the operations listed in that class:

1. Add
2. Get
3. Update
4. Delete

You just create a class that inherits from this one, as follows:

internal class MyProcess : DataBaseProcessBase<MyEntity,MyDataContext>

The Add operation

/// <summary>
/// Adds a new record to the DB
/// </summary>
/// <param name="entity">Current Object</param>
/// <param name="IdPropertyName">Name of the property
///          containing identity Column or the ID returned by 
/// the DB</param>
/// <returns><see cref="System.Object"/> </returns>
protected virtual object Add(T entity, string IdPropertyName)
{
    using (DC db = new DC())
    {
        db.GetTable<T>().InsertOnSubmit(entity);
        db.SubmitChanges();
    }
    return entity.GetType().GetProperty(IdPropertyName).GetValue(entity, null);
}

The add operation is pretty easy and straightforward. It just inserts the entity into the database and returns the ID of the new record. Note, the second parameter is the ID property name as I'm using Reflection to get the value of the ID property for the inserted entity.

How to use

base.Add(MyEntity, "ID");

Note: the name of the property is case sensitive.

The Get operation

/// <summary>
/// Select From DB on the defined query
/// </summary>
/// <param name="options"><see
/// cref="System.Data.Linq.DataLoadOptions"/></param>
/// <param name="query">Select Query</param>
/// <param name="from">for pagination Purposes, starting Index</param>
/// <param name="to">for pagination Purposes, End Index</param>
/// <returns>collection of the current type,
/// <see cref="System.System.Collections.Generic.IList<T>"/></returns>
/// <remarks>if "to" parameter was passed as 0,
/// it will be defaulted to 100, you can replace it by
/// a valued defined in the config, and another point
/// of interest, if from > to, from will be
/// reseted to 0.
/// 
/// if there is no query defined, all results will be
/// returned, and also if there is no load data options
/// defined, the results will contain only the entity specified
/// with no nested data (objects) within that entity.
/// </remarks>
protected virtual IList<T> Get(DataLoadOptions options, 
          Expression<Func<T, bool>> query, int from, int to)
{
    IList<T> list = null;
    if (to == 0)
        to = 100;
    if (from > to)
        from = 0;
    using (DC db = new DC())
    {
        if (null != options)
            db.LoadOptions = options;
        if (null == query)
            list = db.GetTable<T>().Skip(from).Take(to - from).ToList();
        else
            list = 
              db.GetTable<T>().Where(query).Skip(from).Take(to - from).ToList();
    }
    return list;
}

The get method consists of four parameters:

• DataLoadOptions: if defined, it would be assigned to the database.
• Expression<Func<T,bool>> query: this is the query or Lambda expression in which the results will be returned upon.
• int from: starting index, for pagination purpose.
• int to: ending index, also for pagination purpose.

How to use

DataLoadOptions options = new DataLoadOptions();
//Load my object along with nested object with it,
//as an example MyObject = Customer, MyNestObject=Orders
options.LoadWith<MyObject>(m => m.MyNestedEntity);

base.Get(options,m => m.CategoryID == 1,0,100);

Suppose that you have a class called MyObject and this class has two properties, CategoryId and MyNestedObject. Here is the scenario, we want to get 100 results of MyObject where its CategoryID equals to 1 along with its nested object.

There are two overloads for this method, as shown:

/// <summary>
/// Select From DB on the defined query
/// </summary>        
/// <param name="query">Select Query</param>
/// <param name="from">for pagination Purposes, starting Index</param>
/// <param name="to">for pagination Purposes, End Index</param>
/// <returns>collection of the current type, 
///<see cref="System.System.Collections.Generic.IList<T>"/></returns>
/// <remarks>if "to" parameter was passed as 0, it will be defaulted to 100, 
///you can replace it by a valued defined in the config, and another point of 
/// interest, if from > to, from will be reseted to 0</remarks>
protected virtual IList<T> Get(Expression<Func<T, bool>> query, int from, int to)
{
    return Get(null, query, from, to);
}
/// <summary>
/// Select All From DB
/// </summary>
/// <param name="from">for pagination Purposes, starting Index</param>
/// <param name="to">for pagination Purposes, End Index</param>
/// <returns>collection of the current type,
///<see cref="System.System.Collections.Generic.IList<T>"/></returns>
/// <remarks>if "to" parameter was passed as 0, it will be defaulted to 100, 
///you can replace it by a valued defined in the config, and another point of 
/// interest, if from > to, from will be reseted to 0</remarks>
protected virtual IList<T> Get(int from, int to)
{
    return Get(null, null, from, to);
}

Points of interest

Each parameter defined in the get method can be null. Starting from the first one, if DataLoadOptions is null, it won't be assigned to the database context. If the query is null, then all results will be returned depending on the pagination indexes specified. And, last but not least, if the 'to' parameter is 0, then it will be defaulted to 100 (you can change this by a config value) and if the 'from' is greater than the 'to' parameter, it will be defaulted to 0 (also, you can change it if you don't like this behaviour).

Those methods are a great way when you do your search; based on conditions, you build up your query, and then you call one of those methods.

Remember, those methods will get any data based on the provided query. If you want to filter such get methods on the client code, you just create your methods in the facade and the process class, as follows:

public static class FacadeExample
{
  public static MyEntity GetMyEntityByID(int id)
      {
    return (new MyProcess()).GetByID(id);
   }
 }

Now, in the MyProcess class (the one defined at the top of this article), add the same method but add the query logic to get by ID, somehow like this:

public MyEntity GetByID(int id)
{
  return base.Get(m => m.ID == id,0,1)[0];
}

In the facade class, notice that we are instantiating a new instance of the MyProcess class (new MyProcess()), so each time this method is called, a new instance will be created. This is not a good practice, but I added it there for quick referencing the class as it's not related to this article subject. Consequently, you should think of a singleton or a factory class to get your process instances instead of creating a new instance each time a method is called.

Note: The code listed above will throw an IndexOutOfRangeException if the results count is zero, so you should always do null and count checking before returning results.

The Update operation

/// <summary>
/// Updates Entity
/// </summary>
/// <param name="entity">Entity which hold the updated information</param>
/// <param name="query">query to get the same
/// entity from db and perform the update operation</param>
/// <remarks>this method will do dynamic property mapping between the passed entity
/// and the entity retrieved from DB upon the query defined,
/// ONLY ValueTypes and strings are
/// mapped between both entities, NO nested objects will be mapped, you have to do
/// the objects mapping nested in your entity before calling this method</remarks>
protected virtual void Update(T entity, Expression<Func<T, bool>> query)
{
   using (DC db = new DC())
   {
    object propertyValue = null;
        T entityFromDB = db.GetTable<T>().Where(query).SingleOrDefault();
          if (null == entityFromDB)
             throw new NullReferenceException("Query Supplied to " + 
                   "Get entity from DB is invalid, NULL value returned");
        PropertyInfo[] properties = entityFromDB.GetType().GetProperties();
        foreach (PropertyInfo property in properties)
        {
           propertyValue = null;
                if (null != property.GetSetMethod())
                {
                  PropertyInfo entityProperty = 
                        entity.GetType().GetProperty(property.Name);
                    if (entityProperty.PropertyType.BaseType == 
                        Type.GetType("System.ValueType")|| 
                        entityProperty.PropertyType == 
                        Type.GetType("System.String"))

                      propertyValue = 
                       entity.GetType().GetProperty(property.Name).GetValue(entity, null);
                    if (null != propertyValue)
                        property.SetValue(entityFromDB, propertyValue, null);
                }
            }
            db.SubmitChanges();
        }
    }

The update operation is a bit tricky because the properties mapping between the passed entity and the entity from the database is done dynamically, so any nested object will not be mapped. The only property mapping would be against value types and string values. If you want to use this method as is, then you should do the logic of mapping objects in your entity, then use this method as a final step to map the other value types and string properties you have.

How to use

base.Update(MyEntity,e => e.ID == MyEntity.ID);

How it works

Depending on the query expression, this method will get the entity from the database, then do a dynamic property mapping between the passed entity and the retrieved one, and then submit the changes. So, in the above example, I am updating the database entity where the ID = the passed entity ID.

Points of interest

The reason why we are getting the entity from the database and doing the mapping is, as you can notice, we are using the Facade to get our entities. So, once the database context is closed, then we cant submit the changes using the entity passed to this method. As you will hit an Object Disposed exception, the other way around is to get the entity from the database, do the mapping, and then submit the changes.

Updated

I have been thinking of the mapping between the properties in the update method and wasn't satisfied with the design, so I redesigned the whole mapping thing and plugged in a mapping provider for each type supported, where you can also create your own providers and plug them into play.

First, I created an interface as follows:

/// <summary>
/// Common interface for all Property Mapping Providers
/// </summary>
public interface IPropertyMappingProvider
{
    /// <summary>
    /// Responsible for mapping the two properties
    /// </summary>
    /// <param name="entity">Entity received from
    /// the client code, <see cref="System.Object"/></param>
    /// <param name="LINQEntity">Entity retrieved from DB</param>
    /// <param name="LINQProperty">
    /// <see cref="System.Reflection.PropertyInfo"/> from LINQ entity
    /// retrieved from DB to be mapped</param>
    void MapProperties(object entity, object LINQEntity, PropertyInfo LINQProperty);
}

And, a base property mapper class that implements the previous interface:

internal class PropertyMappingProviderBase : IPropertyMappingProvider
{
    #region IPropertyMappingProvider Members
    /// <summary>
    /// Encapsulates the common functionality of mapping two 
    ///properties using <see cref="System.Reflection"/>
    /// </summary>
    /// <param name="entity">Entity received from the client code, 
    ///<see cref="System.Object"/></param>
    /// <param name="LINQEntity">Entity retrieved from DB</param>
    /// <param name="LINQProperty">
    /// <see cref="System.Reflection.PropertyInfo"/> 
    ///from LINQ entity
    /// retrieved from DB to be mapped</param>
    /// <remarks>If you want to create a new Provider, just inherit from this class
    /// and have MappingPropertyTypeNameAttribute set to the type you are providing 
    ///the mapping
    /// against</remarks>
    public virtual void MapProperties(object entity, 
           object LINQEntity, PropertyInfo LINQProperty)
    {
        object propertyValue = null;
        //Get Property from entity
        PropertyInfo entityProperty = entity.GetType().
    GetProperty(LINQProperty.Name);
        //Get Value from the property
        if (null != entityProperty)
            propertyValue = entityProperty.GetValue(entity, null);
        //Set LinqEntity to the value retrieved from the entity
        if (null != propertyValue)
            LINQProperty.SetValue(LINQEntity, propertyValue, null);
    }
    #endregion
   
}

So, as you can see, the base class implements the IPropertyMappingProvider, which has only one method, MapProperties. The MapProperties method accepts three parameters: the first one is your entity received from the client code, the next one is the entity retrieved from the database, and the last one is the property which the mapping will be held against.

Now, here is the fun part of creating providers. But, before pasting the code for each provider, the provider for each type should be initialized dynamically. No switch statements to initialize them, and no hardcoded values in the class responsible for initializing the correct providers. So, what's the best way to do this????

I found that creating a custom attribute and decorating our provider classes with it is the way. Here is the definition of it:

/// <summary>
/// Attribute specified on a <see cref="IPropertyMappingProvider"/>
/// indicating the type of property that it maps
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class MappingPropertyTypeNameAttribute : System.Attribute
{
    private string _propertyTypeName;
    public MappingPropertyTypeNameAttribute(string propertyTypeName)
    {
        _propertyTypeName = propertyTypeName;
    }
    public string PropertyTypeName
    {
        get
        {
            return _propertyTypeName;
        }
        
    }

Simply, this attribute is assigned to each provider class (except the base), which will hold the fully qualified name of the type that it will process (e.g.: System.String). So now, I can list the two providers I have created: one for string values and the other one for value types:

String mapping provider:

/// <summary>
/// Responsible for mapping String Values between both properties
/// </summary>
[MappingPropertyTypeName("System.String")]
internal class StringPropertyMappingProvider : PropertyMappingProviderBase
{
    /// <summary>
    /// Map String Values
    /// </summary>
    /// <param name="entity">Entity received from the client code,
    /// <see cref="System.Object"/></param>
    /// <param name="LINQEntity">Entity retrieved from DB</param>
    /// <param name="LINQProperty">
    /// <see cref="System.Reflection.PropertyInfo"/> from LINQ entity
    /// retrieved from DB to be mapped</param>
    public override void MapProperties(object entity, 
                    object LINQEntity, PropertyInfo LINQProperty)
    {
        base.MapProperties(entity, LINQEntity, LINQProperty);
    }
}

Value types mapping provider:

/// <summary>
/// Responsible for mapping Value types between both properties
/// </summary>
[MappingPropertyTypeName("System.ValueType")]
internal class ValueTypePropertyMappingProvider : PropertyMappingProviderBase
{
    /// <summary>
    /// Map Value types
    /// </summary>
    /// <param name="entity">Entity received from the
    /// client code, <see cref="System.Object"/></param>
    /// <param name="LINQEntity">Entity retrieved from DB</param>
    /// <param name="LINQProperty">
    /// <see cref="System.Reflection.PropertyInfo"/> from LINQ entity
    /// retrieved from DB to be mapped</param>
    public override void MapProperties(object entity, 
                    object LINQEntity, PropertyInfo LINQProperty)
    {
        base.MapProperties(entity, LINQEntity, LINQProperty);
    }
}

Notice the MappingPropertyTypeName which is set exactly to the type name. Nothing special in both classes except calling the base method. But, it's a good practice to create a provider for each type in case you want to do more manipulation, or if you want to change the behaviour of one of them without affecting others.

Now, everything is set, the only thing missing is our facade class to initiate the right provider dynamically. So, here is the definition of that class:

/// <summary>
/// Entry Point for the Client code to map the properties
/// </summary>
public static class MappingProvider
{
    /// <summary>
    /// Map Properties between two objects
    /// </summary>
    /// <param name="entity">Entity received from the client code, 
    ///<see cref="System.Object"/></param>
    /// <param name="LINQEntity">Entity retrieved from DB</param>
    /// <param name="LINQProperty">
    /// <see cref="System.Reflection.PropertyInfo"/> 
    ///from LINQ entity
    /// retrieved from DB to be mapped</param>
    /// <remarks>This class will get the provider dynamically and will map 
    ///the properties
    /// using that provider, so if you want
    /// to implement your own provider, you dont
    /// have to modify anything in the code</remarks>
    public static void MapProperties(object entity, 
           object LINQEntity, PropertyInfo LINQProperty)
    {
        IPropertyMappingProvider provider = null;
        //Get All Types in the current assembly which have 
        //MappingPropertyTypeNameAttribute defined
        Type[] currentProviders = Assembly.GetExecutingAssembly().GetTypes().Where(
          t => t.GetCustomAttributes(typeof(MappingPropertyTypeNameAttribute),
          false).ToArray().Length > 0).ToArray();
        if (null != currentProviders && currentProviders.Length > 0)
        {
            //Get the provider type,first try to get from its type
            //the mechanism used is to get the MappingPropertyTypeNameAttribute 
            //and compare the string defined there with the LINQProperty type
            Type providerType = currentProviders.Where(p =>
              (p.GetCustomAttributes(typeof(MappingPropertyTypeNameAttribute), 
               false).ToArray()[0] as MappingPropertyTypeNameAttribute).PropertyTypeName == 
               LINQProperty.PropertyType.ToString()).SingleOrDefault();
            //if no provider found,Try to get it from comparing LINQproperty 
            //base type with MappingPropertyTypeNameAttribute
            if(null == providerType)
              providerType = currentProviders.Where(p =>
              (p.GetCustomAttributes(typeof(MappingPropertyTypeNameAttribute), 
               false).ToArray()[0] as MappingPropertyTypeNameAttribute).PropertyTypeName == 
               LINQProperty.PropertyType.BaseType.ToString()).SingleOrDefault();
            
            if (null != providerType)
            {
                //Call the provider factory to get our instance
                provider = ProviderFactory.CreatePropertyMappingProvider(providerType);
                //Map Properties
                provider.MapProperties(entity, LINQEntity, LINQProperty);
            }
        }
    }
}

To be honest, this class was great fun to write, because it does everything dynamically using Reflection. First, it gets all the types defined in the assembly where they have our MappingPropertyTypeNameAttribute defined, meaning, it will get all our mapping providers. If it finds any, it proceeds with trying to get the right provider by comparing the passed property type with the attribute value defined on each of our provider classes, using a string comparison. If not found, then it will try and compare the base type of the property with our attribute's value (reason for that is, all value types (e.g.: Int32) have their type set to System.Int32 and their base type to value type; plus it's a good thing to expand the possibilities of comparison).

So, after finding our provider type, we need to initialize it. I created a factory class to do that job for us, as follows:

/// <summary>
/// Responsible of instantiating each provider
/// and cahing it into a Dictionary
/// </summary>
internal static class ProviderFactory
{
    //Static providers cache
    static IDictionary<string, IPropertyMappingProvider> providers = 
           new Dictionary<string, IPropertyMappingProvider>();

    public static IPropertyMappingProvider 
           CreatePropertyMappingProvider(Type providerType)             
    {
        IPropertyMappingProvider provider = null;
        
        //Check if the provider already exists in the cahce
        if (providers.ContainsKey(providerType.ToString()))
            provider = providers[providerType.ToString()] 
            as IPropertyMappingProvider;
        else
        {
            //Instaniate a new provider and add it to the cache
            provider=  AppDomain.CurrentDomain.CreateInstanceAndUnwrap(
                       Assembly.GetExecutingAssembly().ToString(), 
                       providerType.ToString()) as IPropertyMappingProvider;

            providers.Add(provider.GetType().ToString(), 
            provider as IPropertyMappingProvider);
         }
         return provider;
    }
}

Nothing special there, except initializing the provider and inserting it into a custom Dictionary for caching purposes, as there is no need to keep creating the provider over and over, just a singleton will do.

And finally, it calls the MapProperties method from the corresponding provider, so our Updatemethod in the base class will look like this after the modification:

protected virtual void Update(T entity, Expression<Func<T, bool>> query)
{
    using (DC db = new DC())
    {
        T entityFromDB = db.GetTable<T>().Where(query).SingleOrDefault();
        if (null == entityFromDB)
            throw new NullReferenceException("Query Supplied to Get" + 
                  " entity from DB is invalid, NULL value returned");
        PropertyInfo[] properties = entityFromDB.GetType().GetProperties();
        foreach (PropertyInfo property in properties)
        {
            if (null != property.GetSetMethod())
            {
               //Just one Line Of Code to Do the mapping
                MappingProvider.MapProperties(entity, entityFromDB, property);
            }
        }
        db.SubmitChanges();
    }
}

Last, about the update operation, the old DatabaseProcessBase class is still available in the downloads, and the new design for all the providers and the modification for the class is added as well.

The Delete operation

/// <summary>
/// Deletes the entity upon the defined query
/// </summary>
/// <param name="query">Delete Query</param>
protected virtual void Delete(Expression<Func<T, bool>> query)
{
    using (DC db = new DC())
    {
        db.GetTable<T>().DeleteOnSubmit(
           db.GetTable<T>().Where(query).Single());
        db.SubmitChanges();
    }
}

The delete operation only takes one parameter, which is the query to perform to get the entity which is going to be deleted. You can extend this method to delete all, if no query was defined.

How to use

base.Delete(e => e.ID == 1);

Conclusion

Hope you all benefitted from this class. Thank you all for reading.

History

• 9 May 2008 - Initial version of this article.
• 31 May 2008 - Updated to include mapping providers to map properties between objects.