Generics and Database Access

Question

I have the following method I can pass in a lambda expression to filter my result and then a callback method that will work on the list of results. This is just one particular table in my system, I will use this construct over and over. How can I build out a generic method, say DBget that takes a Table as a parameter(An ADO.NET dataservice entity to be fair) and pass in a filter (a lambda experssion).

 public void getServiceDevelopmentPlan(Expression<Func<tblServiceDevelopmentPlan, bool>> filter, Action<List<tblServiceDevelopmentPlan>> callback)
        {
            var query = from employerSector in sdContext.tblServiceDevelopmentPlan.Where(filter)
                        select employerSector;


            var DSQuery = (DataServiceQuery<tblServiceDevelopmentPlan>)query;
            DSQuery.BeginExecute(result =>
            {
                callback(DSQuery.EndExecute(result).ToList<tblServiceDevelopmentPlan>());

            }, null);

    }

My first bash at this is:

public delegate Action<List<Table>> DBAccess<Table>(Expression<Func<Table, bool>> filter);

Answer 1

If you are using Linq to Ado.NET Dataservices or WCF Dataservices, your model will build you a lot of typed. Generally though you will be selecting and filtering. You need the following, then all your methods are just candy over the top of this:

Query Type 1 - One Filter, returns a list:

public  void makeQuery<T>(string entity, Expression<Func<T, bool>> filter, Action<List<T>> callback)
    {
        IQueryable<T> query = plussContext.CreateQuery<T>(entity).Where(filter);

        var DSQuery = (DataServiceQuery<T>)query;
        DSQuery.BeginExecute(result =>
        {
            callback(DSQuery.EndExecute(result).ToList<T>());
        }, null);

    }

Query Type 2 - One Filter, returns a single entity:

public void makeQuery(string entity, Expression> filter, Action callback) {

        IQueryable<T> query = plussContext.CreateQuery<T>(entity).Where(filter);
        var DSQuery = (DataServiceQuery<T>)query;
        DSQuery.BeginExecute(result =>
        {
            callback(DSQuery.EndExecute(result).First<T>());
        }, null);

    }

What you need to do is overload these and swap out the filter for a simple array of filters

Expression<Func<T, bool>>[] filter

And repeat for single and list returns.

Bundle this into a singleton if you want one datacontext, or keep track of an array of contexts in some sort of hybrid factory/singleton and you are away. Let the constructor take a context or if non are supplied then use its own and you are away.

I then use this on a big line but all in one place:

GenericQuery.Instance.Create().makeQuery<tblAgencyBranches>("tblAgencyBranches", f => f.tblAgencies.agencyID == _agency.agencyID, res => { AgenciesBranch.ItemsSource = res; });

This may look complicated but it hides a lot of async magic, and in certain instances can be called straight from the button handlers. Not so much a 3 tier system, but a huge time saver.