LINQ TO SQL All Common Operations (Insert,Update,Delete,Get) in ONE BASE Class

• Download DataBaseProcessBase - 2.13 KB

Introduction

Base Class For LINQ TO SQL Common Operations (CRUD)

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. so 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 separated 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 any of the DB operations.

having said so, i created a base class which encapsulates the most common logic for DB 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

Below is the definition of the class:

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

As you can notice that this is a generic class taking two types, the first one is your entity type, while the other one is your DataConext, 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 following:

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 straight forward, it just insert the entity into DB andreturns the ID of the new record, NOTE, the second parameter 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: name of the property is CASE SENSITIVE.

The Get Operations

        /// <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 4 parameters:

1. DataLoadOptions: if defined then it would be assigned to the DB.
2. Expression<Func<T,bool>> query: this is the query or Lamda expression in which the results will be returned upon.
3. int from: starting Index, for pagination purpose.
4. int to: end 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);

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

Another Two Overload for this Method as following:

        /// <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 the DataLoadOptions is null it wont be assigned to the DB Conext, 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 was 0, then it will be defaulted to 100 (you can change this by a config value) and if the 'from' is greater than 'to' parameter it will be defaulted to 0 (also you can change it if you dont like this behaviour).

Those methods are 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 to the client code, you just create your methods in the facade and the process class as following:

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 adthe query logic to get by ID, somehow like this:

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

At the facade class, notice that we are instantiating a new instance of 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 its 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.

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 DB is done dynamically, so any nested objects WILL NOT be mapped, the only property mapping would be against ValueTypes 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 valuetypes 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 DB, then do adynamic property mapping between the passed entity and the retrieved one, and then submit the changes, so in the above example, i am updating DB entity where the ID = the passed entity ID.

Points of Interest

the reason on why we are getting the entity from DB and do the mapping is because as you can notice we are using Facade to get our entites, so once the DB Conext is closed then we cant sumbit the changes using the entity passed to this method as you will hit a Object disposed exception, so the other way around is to get the entity from DB , do the mapping and then submit the changes.

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

Delete Operation only takes one parameter which is the query, 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 benefit from this class. thank you all for reading.

Best Regards.

History

Initial Version of this article.