Example Usage

Suppose you have some data access methods that use ordinary SQL queries (database API massively simplified):

IEnumerable<Employee> GetEmployees(string name)
{
    string query = 
        "SELECT id, name\n" +
        "  FROM employees\n" +
        " WHERE name LIKE @name;";

    IDataReader reader = db.Execute(query, new Dictionary<string, object>() { {"@name", name} });

    List<Employee> results = new List<Employee>();

    while (reader.Read())
    {
        results.Add(new Employee(reader));
    }

    return results;
}

Then suppose that your number of employees grows so large that sending the entire result set over the wire is a substantial performance drain. To avoid transferring unnecessary data, you allow the caller to specify a range of results to return:

IEnumerable<Employee> GetEmployees(string name, int skip, int take)
{
    string query = 
        "SELECT id, name\n" +
        "  FROM employees\n" +
        " WHERE name LIKE @name\n" +
        " LIMIT @skip, @take\";

    /* ... */

    return results;
}

The problem with this is that your data access layer has to change constantly as your application requirements change. What if you need sorting? Sorting combined with limit, as well as sorting without a limit? Sorting in the database may be substantially faster if it means the DBMS uses an index to read the rows off in already-sorted order.

LINQ Wrapper allows you to leave the details of sorting and restricting your result set out of the data access layer, letting the caller control them without any knowledge of the SQL.

The high-level view is that you can modify the interface to your data access layer to return a LINQ IQueryable object:

IQueryable<Employee> GetEmployees(string name)
{
/* ... */
}

Application code can then apply standard LINQ operators:

IQueryable<Employee> employees = GetEmployees(name);

IEnumerable<Employee> orderedEmployees = from employee in employees
                                         orderby employee.Age
                                         select employee;

DisplayMethod(orderedEmployees.Skip(30).Take(20));

All the modifications to the SQL are done automatically and lazily, so no data is sent over the wire that isn’t needed.

Constructing a query object

The IQueryable<T> interface to return to the caller is provided by the Query<T> class. In order to instantiate this, you must first instantiate a query provider:

LazyDBQueryProvider<Employee> provider = 
    new LazyDBQueryProvider<Employee>(() => GetAdoConnection(), sqlBuilder, parameters);

The LazyDBQueryProvider isn’t visible to the calling code, it’s only used inside the Query object. To construct the query provider, you need three things:

• A delegate that returns a connection to the database (here implemented with a lambda). This is needed rather than a single connection because the provider may make multiple calls to the database at widely separated points in time, and if the connection is shared between them it gets very difficult to control lifetime.
• An SQLBuilder instance that contains the base SQL to be executed
• A dictionary of parameter values to bind into the SQL expression at execution time

Once you have the Query provider, instantiating the Query object is easy:

IQueryable<Employee> GetEmployees(string name)
{
    LazyDBQueryProvider<Employee> provider = 
        new LazyDBQueryProvider<Employee>(() => GetAdoConnection(), sqlBuilder, parameters);

    Query<Employee> query = new Query<Employee>(provider);
    return query;
}