C# Generics Question

Question

I have a couple of areas in an application I am building where it looks like I may have to violate the living daylights out of the DRY (Don't Repeat Yourself) principle. I'd really like to stay dry and not get hosed and wondered if someone might be able to offer me a poncho. For background, I am using C#/.NET 3.51 SP1, Sql Server 2008, and Linq-to-Sql.

Basically, my situations revolve around the following scenario. I need to be able to retrieve either a filtered list of items from virtually any table in my database or I need to be able to retrieve a single item from any table in my database given the id of the primary key. I am pretty sure that the best solutions to these problems will involve a good dose of generics and/or reflection.

Here are the two challenges in a little more depth. (Please forgive the verbosity.)

1. Given a table name (or perhaps a pluralized table name), I would like to be able to retrieve a filtered list of elements in the table. Specifically, this functionality will be used with lookup tables. (There are approximately 50 lookup tables in this database. Additional tables will frequently be added and/or removed.) The current lookup tables all implement an interface (mine) called IReferenceData and have fields of ID (PK), Title, Description, and IsActive.

For each of these lookup tables, I need to sometimes return a list of all records. Other times I need to only return the active records. Any Linq-to-Sql data context automatically contains a List property for each and every TableName. Unfortunately, I don't believe I can use this in it's raw form because it is unfiltered, and I need to apply a filter on the IsActive property.

One option is to write code similar to the following for all 50 tables. Yuk!!!

public List<AAA> GetListAAA(bool activeOnly)
{
    return AAAs.Where(b => b.IsActive == true || b.IsActive == activeOnly).OrderBy(c => c.Title).ToList();
}

This would not be terribly hard, but it does add a burden to maintenance.

Note: It is important that when the list is returned that I maintain the underlying data type. The records in these lookup tables may be modified, and I have to apply the updates appropriately.

1. For each of my 150 tables, I need to be able to retrieve an individual record (FirstOrDefault or SingleOrDefault) by its primary key id. Again, I would prefer not to write this same code many times. I would prefer to have one method that could be used for all of my tables.

I am not really sure what the best approach would be here. Some possibilities that crossed my mind included the following. (I don't have specific ideas for their implementation. I am simply listing them as food for thought.)

A. Have a method like GetTableNameItemByID (Guid id) on the data context. (Good) B. Have an extension method like GetItem(this, string tableName, Guid id) on the data context. (Better) C. Have a Generic method or extension method like GetItem (this, Table, Guid id). (I don't even know if this possible but it would be the cleanest to use.) (Best)

Additional Notes

For a variety of reasons, I have already created a partial class for my data context. It would certainly be acceptable if the methods were included in that partial class either as normal methods or in a separate static class for extension methods.

Answer 1

Have you considered using a code generation tool? Have a look at CodeSmith. Using a tool like that or T4 will allow you to generate your filter functions automatically and should make them fairly easy to maintain.

I'm not sure the best link to provide for T4, but you could start with this video.

Answer 2

Would this meet your needs?

public static IEnumerable<T> GetList<T>(this IEnumerable<IReferenceData> items, bool activeOnly)
{
    return items.Where(b => b.IsActive == true || b.IsActive == activeOnly).OrderBy(c => c.Title).Cast<T>().ToList();
}

You could use it like this:

IEnumerable<IReferenceData> yourList;
List<DerivedClass> filtered = yourList.GetList<DerivedClass>(true);

Answer 3

Since you already have a partial implementation of your data context, you could add:

public IQueryable<T> GetList<T>( bool activeOnly ) where T : class, IReferenceData
{
     return this.GetTable<T>()
                .Where( b => !activeOnly || b.isActive )
                .OrderBy( c => c.Title );
}

Retaining the IQueryable character of the data will defer the execution of the query until you are ready to materialize it. Note that you may want to omit the default ordering or have separate methods with and without ordering to allow you to apply different orderings if you desire. If you leave it as an IQueryable, this is probably more valuable since you can use it with paging to reduce the amount of data actually returned (per query) if you desire.

Answer 4

There's a design pattern for your needs called "Generic Repository" .Using this pattern you'll get an IQueryable instead of a real list of your entities which lets you do some other stuff with your query as you go.The point is to let the business layer gets whatever it needs whenever it needs it in a generic approach.

You can find an example here.

Answer 5

To do something like this without demanding interfaces etc, you can use dynamic Expressions; something like:

public static IList<T> GetList<T>(
    this DataContext context, bool activeOnly )
    where T : class
{
    IQueryable<T> query = context.GetTable<T>();
    var param = Expression.Parameter(typeof(T), "row");
    if(activeOnly)
    {
        var predicate = Expression.Lambda<Func<T, bool>>(
            Expression.Equal(
                Expression.PropertyOrField(param, "IsActive"),
                Expression.Constant(true,typeof(bool))
            ), param);
        query = query.Where(predicate);
    }
    var selector = Expression.Lambda<Func<T, string>>(
        Expression.PropertyOrField(param, "Title"), param);
    return query.OrderBy(selector).ToList();
}