smart way to get data out of database through data access layer?

Question

I have a database with a lot of different datasets. I want to make it easy to search the different datasets depending on many different parameters, for example I currently have:

public List<Dataset> GetAllDatasetsByMethod(Method method)

with this query:

var datasets = from b in db.Method where b.Id == method.Id select b.Dataset;

and then I have another method looking like this:

public List<Dataset> GetAllDatasetsByTargetMaterial(TargetMaterial material)

and query like this:

var datasets = from b in db.Dataset
                where b.TargetMaterial.Name.ToLower() == material.Name.ToLower()
                select b;

I got a lot of these methods(like 10-15) comparing ether on an unique id or name. and I need to make more but I am starting to feel like I repeat myself and as far as I have learned DRY. So is there a smarter way to do this?

Answer 1

Create a class with all the different variations of query parameters, like

public class MyTypeQueryParameters
{
    public int? Id {get; set;}
    public string MaterialName {get; set;}
}

You can then design your data access layer like an internal API and have a method like:

public List<DataSet> GetDataSets(MyTypeQueryParameters parameters) {
    var query = db.DataSet.AsQueryable;
    if (parameters.Id != null) 
    {
        query = query.Where(x => x.Id == parameters.Id.Value);
    }

    if (!string.IsNullOrWhitespace(MaterialName))
    {
        query = query.Where(x => x.TargetMaterial.Name == parameters.MaterialName);
    }

    return query.ToList();
}

There are ways to clean up the method's logic a bit but that's how I would start off, so you don't end up with a ton of different methods that are all based on your query filters.