Building a common set of methods that can operate on any linq table

Question

Problem: We make extensive use of a repository pattern to facilitate read/write operations on our datastore (MS SQL using LINQ) across multiple applications and subsections of functionality. We have series of methods that all do something similar to each other.

For example, we have the ProcessAndSortXXXXX class of methods.

private static IEnumerable<ClassErrorEntry> ProcessAndSortClassErrorLog(IQueryable<ClassErrorDb> queryable, string sortOrder)
{
    var dynamic = queryable;
    if (!String.IsNullOrEmpty(sortOrder.Trim()))
    {
        dynamic = dynamic.OrderBy(sortOrder);
    }
    return dynamic
        .Select(l =>
            new ClassErrorEntry(l.Id)
            {
                ClassId = l.ClassId,
                Code = l.Code,
                Message = l.Message,
                Severity = l.Severity,
                Target = l.Target
            }
        );
}

...and...

private static IEnumerable<ClassTimerLogEntry> ProcessAndSortClassTimerLog(IQueryable<ClassTimerDb> queryable, string sortOrder)
{
    var dynamic = queryable;
    if (!String.IsNullOrEmpty(sortOrder.Trim()))
    {
        dynamic = dynamic.OrderBy(sortOrder);
    }
    return dynamic
        .Select(l =>
            new ClassTimerLogEntry(l.Id)
            {
                ClassName = l.ClassName,
                MethodName = l.MethodName,
                StartTime = l.StartTime,
                EndTime = l.EndTime,
                ParentId = l.ParentId,
                ExecutionOrder = l.ExecutionOrder
            }
        );
}

As you can tell by the code, they're all very similar until you look at the signature and then get to the the return statement where we're building out the instances of the ClassErrorEntry and ClassTimerLogEntry.

I want to build a utility method that I'll add into the base class that all of the repositories inherit from.

I want to be able to pass in arguments that can be used to instantiate the objects and pack them into the returning IEnumerable.

I found this post by ScottGu and that gets me most of what I need. It looks like this (from the sample in the documentation):

var query =
    db.Customers.
    Where("City = @0 and Orders.Count >= @1", "London", 10).
    OrderBy("CompanyName").
    Select("new(CompanyName as Name, Phone)");

Here's where I get stuck, though. I need a pointer or suggestion how I can pass in the LINQ tables and DataContext in a generic fashion so I can build out the dynamic query.

If I were to mock up the signature in pseudocode I think it would look something like this:

protected internal IEnumerable ProcessAndSort(IQueryable source, string selectClause, string whereClause, string orderByClause);

I realize that the finished signature may look different as we figure this out.

Thank you!

Update!

I now have code that works to generate an anonymous type but fails when converting to the concrete type.

public static IEnumerable<TResult> ProcessAndSort<T, TResult>(IQueryable<T> queryable, 
    string selector, Expression<Func<T, bool>> predicate, string sortOrder)
{
    var dynamic = queryable.Where(predicate).AsQueryable();
    if (!String.IsNullOrEmpty(sortOrder.Trim()))
    {
        dynamic = dynamic.OrderBy(sortOrder);
    }
    var result= dynamic.Select(selector).Cast<TResult>();

    return result;
}

Here is the code that calls this method:

[TestMethod]
public void TestAnonymousClass()
{
    var loggingContext = new LoggingDbDataContext(DatabaseConnectionString);
    var repo = new LoggingRepository(loggingContext);

    var result = repo.TestGetClassErrorLog(4407, 10, 0, 
        "new ( ClassId as ClassId, " +
        "Code as Code, " +
        "Message as Message, " +
        "Severity as Severity, " +
        "Target as Target )", "Target");
    TestContext.WriteLine(result.ToList().Count.ToString());
}

The last line TestContext.WriteLine(result.ToList().Count.ToString()); throws the exception System.InvalidOperationException: No coercion operator is defined between types 'DynamicClass1' and 'Utilities.Logging.ClassErrorEntry'.

This chunk of code, though fails:

[TestMethod]
public void TestNamedClass()
{
    var loggingContext = new LoggingDbDataContext(DatabaseConnectionString);
    var repo = new LoggingRepository(loggingContext);

    var result = repo.TestGetClassErrorLog(4407, 10, 0,
        "new ClassErrorEntry(Id) { ClassId = ClassId, " +
        "Code = Code, " +
        "Message = Message, " +
        "Severity = Severity, " +
        "Target = Target }", "Target");
    TestContext.WriteLine(result.ToList().Count.ToString());
}

This fails on a parsing error. Test method eModal.Repositories.Test.RepositoryBaseTest.TestConcreteClass threw exception: System.Linq.Dynamic.ParseException: '(' expected, found 'ClassErrorEntry' ('Identifier') at char 19 in 'new ClassErrorEntry(Id) { ChassisAuthId = ChassisAuthId, Code = Code, Message = Message, Severity = Severity, Target = Target }'

I'm not sure that the character position is suspectas the 19th character position is a ( and the type passed into the Validate method indicates a position of 4, or the first 'C'.

Answer 1

I would completely advise you against making weakly typed queries just for the sake of code reuse.
Code reuse is for increasing maintainability, but weak typing can kill it, if used in a wrong way. By writing your queries in plain text, you're effectively making the classes very hard to refactor and change, and introduce a lot of obscure dependencies.

I suggest you take a look at LinqKit that allows to combine Expressions. For example, we wrote a Paging method that splits query by pages and use it across the project with different types:

var query = CompiledQuery.Compile(
    BuildFolderExpr( folder, false )
        .Select( msg => selector.Invoke( msg, userId ) ) // re-use selector expression
        .OrderBy( mv => mv.DateCreated, SortDirection.Descending )
        .Paging() // re-use paging expression
        .Expand() // LinqKit method that "injects" referenced expressions
    )

public static Expression<Func<T1, T2, PagingParam, IQueryable<TItem>>> Paging<T1, T2, TItem>(
    this Expression<Func<T1, T2, IQueryable<TItem>>> expr )
{
    return ( T1 v1, T2 v2, PagingParam p ) => expr.Invoke( v1, v2 ).Skip( p.From ).Take( p.Count );
}

In my example, BuildMessageExpr returns a relatively simple select expression (which already depends on folder and another parameter), and different methods reuse this expression by applying filtering, ordering, getting count, further selecting with selector expression being passed as a parameter, et cetera. Once the query is created, it gets cached for future usage when parameters are similar.

Answer 2

It is not a direct answer to your question.

As you have said you have quite a lot code that look similar but return different types. If you will go ahead and look for generic implementation of this approach, the result may have a few hacks, you may still pass some uncomfortable SQL or check the type of the object or do some reflection kung-fu. You may still select this pass and actually someone can have a sensible idea that wouldn't look like a dirty hack.

The other option is to use a proper ORM with generic repository pattern and dependency injection(google link). Your data access layer will look much simpler and easier to maintain.