Linq to SQL Tutorial – Linq to SQL Generic Framework using reflection

Download source code

In a previous post; Base Repository/Business Logic wrapper, I talked about a basic Linq to SQL Framework I created. I then extended it in my ObjectDataSource binding with paging and sorting post to show how to use it with the ObjectDataSource.

The problem was that although it worked quite nicely for getting all records and getting by ID, if I wanted to perform any specific filtering I had to create a derived repository class with a specific method for each query.

In this post I’ll explain how I have extended this framework using reflection to allow filtering on multiple columns without having to created separate repository objects.

To pass parameters to the repository I created a class called ParameterItem which has a PropertyName property, which is used to map to the Property on the Linq to SQL generated class. It also has properties for PropertyType, Value and EvaluationType.

[Serializable()]
public class ParameterItem
{
    #region Enumerations
    /// <summary>
    /// Property to parameter evalution type
    /// </summary>
    public enum ParameterEvaluationType : int
    {
        Equals = 0,
        NotEqual = 1,
        Like = 2,
        GreaterThan = 4,
        GreaterThanOrEqualTo = 8,
        LessThan = 16,
        LessThanOrEqualTo = 32
    }
    #endregion
    #region Constructors
    public ParameterItem()
    {
    }
    public ParameterItem(string propertyName, TypeCode propertyType, string value)
    {
        PropertyName = propertyName;
        PropertyType = propertyType;
        Value = value;
    }
    public ParameterItem(string propertyName, TypeCode propertyType, ParameterEvaluationType evaluationType, string value)
        :this(propertyName, propertyType, value)
    {
        EvaluationType = evaluationType;
    }
    #endregion
    #region Public Properties
    /// <summary>
    /// The name of the entity property
    /// </summary>
    public string PropertyName { get; set; }
    /// <summary>
    /// The type of the entity property
    /// </summary>
    public TypeCode PropertyType { get; set; }
    /// <summary>
    /// The evaluation type to use
    /// </summary>
    public ParameterEvaluationType EvaluationType { get; set; }
    /// <summary>
    /// The value to compare
    /// </summary>
    public string Value { get; set; }
    #endregion
}

My Repository class now has a method called Get which accepts a generic list of ParameterItem which uses a private method called GetQuery. This method looks through the ParameterItem collection and uses reflection to check if the Linq to SQL entity being using with the Repository contains the property specified in the ParameterItem and it is of the correct type.

The GetQuery method then looks at the EvaluationType and builds the query using the System.Linq.Dynamic library.

public List<T> Get(List<ParameterItem> parameters)
{
    return GetQuery(parameters).ToList();
}
private IQueryable<T> GetQuery(List<ParameterItem> parameters)
{
    var query = from t in _context.GetTable<T>()
                select t;
    PropertyInfo[] propertyInfos = typeof(T).GetProperties();
    foreach (ParameterItem parameter in parameters)
    {
        PropertyInfo propertyInfo = propertyInfos.Where(pi => pi.Name.Equals(parameter.PropertyName)).FirstOrDefault();
        if (propertyInfo == null)
        {
            throw new InvalidPropertyException(parameter.PropertyName);
        }
        if (!System.Type.GetTypeCode(propertyInfo.PropertyType).Equals(parameter.PropertyType))
        {
            throw new InvalidPropertyTypeException(parameter.PropertyType.ToString());
        }
        if (!string.IsNullOrEmpty(parameter.Value))
        {
            string evaluationType = "=";
            switch (parameter.EvaluationType)
            {
                case ParameterItem.ParameterEvaluationType.Equals:
                    evaluationType = "=";
                    break;
                case ParameterItem.ParameterEvaluationType.NotEqual:
                    evaluationType = "!=";
                    break;
                case ParameterItem.ParameterEvaluationType.Like:
                    evaluationType = "LIKE";
                    break;
                case ParameterItem.ParameterEvaluationType.GreaterThan:
                    evaluationType = ">";
                    break;
                case ParameterItem.ParameterEvaluationType.GreaterThanOrEqualTo:
                    evaluationType = "<";
                    break;
                case ParameterItem.ParameterEvaluationType.LessThan:
                    evaluationType = "<=";
                    break;
                case ParameterItem.ParameterEvaluationType.LessThanOrEqualTo:
                    evaluationType = ">=";
                    break;
            }
            if (parameter.EvaluationType == ParameterItem.ParameterEvaluationType.Like)
            {
                query = query.WhereLike<T>(parameter.PropertyName, parameter.Value);
            }
            else
            {
                query = query.Where(string.Format("{0} {1} @0", parameter.PropertyName, evaluationType), parameter.Value);
            }
        }
    }
    return query.OfType<T>();
}

The System.Linq.Dynamic library doesn’t support LIKE functionality but I found an extension method here that gives me what I need.

public static IQueryable<T> WhereLike<T>(this IQueryable<T> data, string propertyName, string value)
{
    var param = Expression.Parameter(typeof(T), "x");
    var body = Expression.Call(typeof(Extensions).GetMethod("Like", BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.Public), Expression.PropertyOrField(param, propertyName), Expression.Constant(value, typeof(string)));
    var lambda = Expression.Lambda<Func<T, bool>>(body, param);
    return data.Where(lambda.Compile()).AsQueryable();
}
static bool Like(string a, string b)
{
    return a.ToLower().Contains(b.ToLower());
}

In the Repository there are some overloaded Get methods that provided sorting and paging, these can be seen in the source code.

The following code shows how to populate a GridView dynamically using this new functionality (this can be seen in WebForm1 in the source).

using (Repository<Student> repository = new Repository<Student>())
{
    List<ParameterItem> parameters = new List<ParameterItem>()
    {
        new Repository.ParameterItem("EmailAddress", TypeCode.String, Repository.ParameterItem.ParameterEvaluationType.Like, "hotmail")
    };
    gvStudent.DataSource = repository.Get(parameters);
    gvStudent.DataBind();
}

This all works quite nicely but I wanted to be able to use the ObjectDataSource to reduce the amount of code needed so I created a custom datasource parameter called RepositoryParameter. This custom Parameter creates a list of ParameterItem populated from either a control, query string or session value. The code for RepositoryParameter can be seen in the source code, but here is how it can be used.

<SelectParameters>
    <jks:RepositoryParameter Name="parameters">
        <ParameterItems>
            <jks:ControlParameterItem PropertyName="Forename" PropertyType="String" EvaluationType="Like" ControlID="txtSForename" />
            <jks:QueryStringParameterItem PropertyName="Forename" PropertyType="String" EvaluationType="Like" QueryStringField="Forename" />
            <jks:SessionParameterItem PropertyName="Forename" PropertyType="String" EvaluationType="Like" SessionField="Forename" />
        </ParameterItems>
    </jks:RepositoryParameter>
</SelectParameters>

In the source code this can be seen in WebForm2 in the source code.

To use the generic repository as the TypeName for the ObjectDataSource you need to use the AssemblyQualifiedName. You can get this using the following code.

Type t = typeof(Repository.Repository<Entities.Student>);
odsStudent.TypeName = t.AssemblyQualifiedName;

The above allows me to easily query any table using parameters with Linq to SQL without having to create any extra repositories or anything. Please note this is just me playing around with what is possible and I’m not entirely sure how it would work in a real world application. Of course the use of reflection may hinder performance, but I would imagine it would still perform fairly well.

Download source code

Posted on by Joe in ASP.NET, C#, Linq

One Response to Linq to SQL Tutorial – Linq to SQL Generic Framework using reflection

  1. Pingback: منابع یادگیری LINQ | alisite

Add a Comment