Generic Class to Perform Insert/Update/Get/Delete in LINQ to SQL Generated Classes

Introduction

This article creates a Generic class which can be used to perform the basic CRUD operations in just a one line of code. The pre-requirement for this article is that we need to have a LINQ to SQL generated classes, because the generic class has been designed in such a way to perform opertion only with the LINQ to SQL generated classes.

Using the Code

A brief description of how to use the article or code. The class names, the methods and properties, any tricks or tips.

The pre-requisite for this generic class is that we should have the LINQ to SQL generated classes available. The process to generate the LINQ to SQL classes can be found here.

We will assume all the classes in the LINQ to SQL classes as the entity and also that the name of the DataContext class created is TestDBContext. So, we need to create a interface named IEntity having the generic method signatures available. The code for the interface is

// C# Code
public interface IEntity<T> where T : class
{
    IList<T> List();

    IList<T> List(int? page, int? pageSize, Expression<Func<T, bool>> predicate, Expression<Func<T, object>> sort);

    void Add(T item);

    T Get(Int64 Id);

    void Update(T item);

    bool Delete(T Item);

}

To create a generic class we need to implement this interface and name it as Entity. Before creating this class we need to have some namespaces included in our class i.e.,

// Namespaces included
using System.Data.Linq;
using System.Reflection;
using System.Linq.Expressions;

The main problem raised during the creating of this generic class was that different tables in the database can have their primary key column defined with different types of data types. For example, one Table might have its Primary Key defined as 'BIGINT' and another table might have defined its primary key data type with 'VARCHAR'. So, to get the data from these tables using this generic class, we need to send the column name alongwith the data type of the column. And my generic class will determine the property of the class as per the parameter defined and will return the result. The other operation on of the table were easy and needed on 2-3 lines of code.

Code for the Entity class

public class Entity<T> : IEntity<T> where T : class
{

    // Inserts the data values in the LINQ to SQL generated class.
    public virtual void Add(T item)
    {
        using (TestDBContext db = new TestDBContext())
        {
            db.GetTable<T>().InsertOnSubmit(item);
            db.SubmitChanges();
        }
    }

    // Returns the list of the object of LINQ to SQL Class
    public virtual IList<T> List()
    {
        using (TestDBContext db = new TestDBContext())
        {
            return db.GetTable<T>().ToList();
        }
    }

    // Returns the list of the object of LINQ to SQL Class on the basis of parameters
    public virtual IList<T> List(int? page, int? pageSize, System.Linq.Expressions.Expression<Func<T, bool>> predicate, System.Linq.Expressions.Expression<Func<T, object>> sort)
    {
        var result = this.List().AsQueryable();

        if (predicate != null)
            result = result.Where(predicate);

        if (sort != null)
            result = result.OrderBy(sort);

        if (page.HasValue && pageSize.HasValue)
            result = result.Skip((page.Value - 1) * pageSize.Value).Take(pageSize.Value);

        return result.ToList();
    }

    // Returns the object on the basis of the objects ID, if ID datatype is Int64.
    public virtual T Get(Int64 value)
    {
        return Get(typeof(System.Int64), "ID", value);
    }

    // Returns the object on the basis of the objects ID, if ID datatype is String.
    public virtual T Get(string value)
    {
        return Get(typeof(System.String), "ID", value);
    }

    // Returns the object on the basis of the objects property column.
    public virtual T Get(Type propertyType, string propertyName, object propertyValue)
    {
        T result = null;
        using (TestDBContext db = new TestDBContext())
        {
            IQueryable<T> queryableData = db.GetTable<T>().AsQueryable<T>();
            if (queryableData != null)
            {
                ParameterExpression pe = Expression.Parameter(typeof(T), "entity");

                Expression left = Expression.Property(pe, GetPropertyInfo(propertyName));
                Expression right = Expression.Constant(propertyValue, propertyType);

                Expression predicateBody = Expression.Equal(left, right);

                MethodCallExpression whereCallExpression = Expression.Call(
                    typeof(Queryable),
                    "Where",
                    new Type[] { queryableData.ElementType },
                    queryableData.Expression,
                    Expression.Lambda<Func<T, bool>>(predicateBody, new ParameterExpression[] { pe }));

                IQueryable<T> results = queryableData.Provider.CreateQuery<T>(whereCallExpression);
                foreach (T item in results)
                {
                    result = item;
                    break;
                }
            }
            return result;
        }
    }

    // Updates the data values in the LINQ to SQL generated class.
    public virtual void Update(T item)
    {
        using (TestDBContext db = new TestDBContext())
        {
            db.GetTable<T>().Attach(item, true);
            db.SubmitChanges();
        }
    }

    // Deletes the data values in the LINQ to SQL generated class.
    public virtual bool Delete(T Item)
    {
        using (TestDBContext db = new TestDBContext())
        {
            db.GetTable<T>().DeleteOnSubmit(Item);
            db.SubmitChanges();
            return true;
        }
    }

    private PropertyInfo GetPropertyInfo(string propertyName)
    {
        PropertyInfo[] properties = typeof(T).GetProperties();
        PropertyInfo result = null;
        foreach (PropertyInfo pi in properties)
        {
            if (pi.Name.Equals(propertyName))
            {
                result = pi;
                break;
            }
        }
        return result;
    }

}

Example of calling the Entity class for CRUD operations:

//Suppose we have a class named 'NOTEs' generated by the LINQ to SQL.

// Code to add the object values
NOTES obj = new NOTES();
obj.CATEGORY = 'First Category';
obj.SUBJECT = 'Subject of the Category';
obj.NODETEXT = 'Sample text of the Node';

Entity<NOTES> entity = new Entity</NOTES>();
entity.Add(obj);

// Code to get the object values
Entity<NOTES> entity = new Entity<NOTES>();
NOTES note = entity.Get(typeof(System.Int64), "ID", 1);

....

Points of Interest

How to: Create LINQ to SQL Classes
How to: Use Expression Trees