How to pass conditions for SQL clauses in functions?

Question

I want to arrange an interface that requires to implement functions that deal with LINQ queries for DB.

How can I pass conditions that can be set in a LINQ query? For instance, I have the function:

public IEnumerable<String> function(string whereClause, string orderByClause, int maxRowCount)

As you can see I am obliged to denote explicitly what clauses I will set on my executing query, and I am going to avoid it. I'd like a solution that lets me know about conditions within the function. How can I do this?

Answer 1

All of the following discussion is the result of my experience as a developer so far. There are no hard rules and it is enterely possible that you won't agree with me or that I'll change my mind in the future. That's fine, I think that this is part of a continuos learning process.

There are basically two approaches for your problem.

Being explicit

The idea here is defining an interface having methods designed for a specific purpose. Here is an example:

public interface IOrderRepository 
{
  IEnumerable<Order> GetOrdersForCustomer(Guid customerId);
}

Here you have a method GetOrdersForCustomer which allows you to get all the orders placed by one specific customer. Everything is explicit.

Notice that in this case the way to use the interface member is obvious and this is really valuable.

Being flexible

If you want to be more flexible and give the interface consumers more freedom, you can define the interface this way:

public interface IOrderRepository 
{
  IEnumerable<Order> GetOrders(Func<Order, bool> filterCondition);
}

Here you have provided to the consumers a way to inject a generic filter criteria by which they want to filter the orders. This is a lot more flexible, but less explicit.

An example of usage is the following:

Guid clientId = // some client id that you have in scope
var clientOrders = _repository.GetOrders(order => order.ClientId == clientId);

What should you do ?

It depends on your specific scenario, there is not a right and a wrong answer.

I tend to prefer being explicit, because I like to strictly adhere to the single responsibility principle when I write code and I think that explicitness is an added value (your code is easier to understand and to test).

Final note: what can I do if I really need a dynamic query ?

There are some adavanced use cases when there is the need for a completely dynamic query. When I say dynamic query I mean a query which is completely determined at runtime. A typical example is a scenario where the user is allowed to provide some sort of criteria (sorting criteria, ordering criteria and so on) at runtime in the form of plain text for example. These cases are actually rare and in my opinion, if possible, it should be better to avoid them.

If you really need such a level of flexibility the way to go is using an expression tree driven library such as this one, which allows you to execute text-based dynamic queries.

In this case I would define an interface dedicated to the execution of the user provided dynamic query, with a name which clearly identifies its purpose. A possible design is the following:

public class DynamicQuery 
{
  public string FilterCriteria { get; set; } // user provided filter criteria in the form of a string containing code
  public string OrderCriteria { get; set; } // user provided order criteria in the form of a string containing code
}

public interface IProductDynamicQueryExecutor 
{
  IEnumerable<Product> GetProducts(DynamicQuery query);
}

The way to consume this interface is pretty clear to the client code.

Again, I would avoid any kind of general purpose interface aimed to execute dynamic queries on any tipe of entity (as you can see, this interface is strictly designed for the use case of querying products).

Generally speaking unless absolutely necessary I prefer avoiding any non required abstraction in my code (in this case it can be tempting to define a generic interface where the entity type is expressed as a generic type parameter, something like IDynamicQueryExecutor<TEntity>).

The only general purpose idea in the above code is the class named DynamicQuery, because it seems reasonable to me that any dynamic query is composed by some sort of filter criteria and some sort of sorting criteria. As a rule of thumb, remember that a little bit of code duplication is better than too much abstraction in your code. Again, I think that being explicit and producing easy to understand code is invaluable. One of my favourite instructor once said that premature abstraction kills projects and I totally agree with him.

The exact way the consuming code can express the criteria depends on the chosen library for the concrete implementation, but generally speaking it is code expressed in some sort of meta language such as this one:

string filterCriteria = "Name = \"Contoso LTD\" AND City = \"Gotham City\"";

Answer 2

Expression provides the base class from which the classes that represent expression tree nodes are derived. You can make statically typed for your entities so it'll be checked for correctness at compile time.

Generic delegates will help you to make more generalized code for your entities. The simpliest example is in the code below:

public IEnumerable<TEntity> function<TEntity>(Expression<Func<TEntity, bool>> whereClause, Expression<Func<TEntity, TKey>> columnSelector, int maxRowCount) 
{
    return context
        .Set<TEntity>()   
        .Where(whereClause)
        .OrderBy(columnSelector)
        .Take(maxRowCount)
        .ToList();
}

// using
var result = function<User>(u => u.email.Contains("gmail.com") && y.Age < 50, u => u.Country, 10);
var result2 = function<Post>(p => p.Length > 1000, p => p.AuthorId, 100);

There are more complex and more powerfull solutions with generic repositories of UoW + repository pattern:

public class GenericRepository<TEntity> where TEntity : class
{
    internal DbContext context;
    internal DbSet<TEntity> dbSet;

    public GenericRepository(DbContext context)
    {
        this.context = context;
        this.dbSet = context.Set<TEntity>();
    }

    public virtual IEnumerable<TEntity> Get(
        Expression<Func<TEntity, bool>> filter = null,
        Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
        string includeProperties = "")
    {
        IQueryable<TEntity> query = dbSet;

        if (filter != null)
        {
            query = query.Where(filter);
        }

        foreach (var includeProperty in includeProperties.Split
            (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
        {
            query = query.Include(includeProperty);
        }

        if (orderBy != null)
        {
            return orderBy(query).ToList();
        }
        else
        {
            return query.ToList();
        }
    }

    public virtual TEntity GetByID(object id)
    {
        return dbSet.Find(id);
    }

    public virtual void Insert(TEntity entity)
    {
        dbSet.Add(entity);
    }

    public virtual void Delete(object id)
    {
        TEntity entityToDelete = dbSet.Find(id);
        Delete(entityToDelete);
    }

    public virtual void Delete(TEntity entityToDelete)
    {
        if (context.Entry(entityToDelete).State == EntityState.Detached)
        {
            dbSet.Attach(entityToDelete);
        }
        dbSet.Remove(entityToDelete);
    }

    public virtual void Update(TEntity entityToUpdate)
    {
        dbSet.Attach(entityToUpdate);
        context.Entry(entityToUpdate).State = EntityState.Modified;
    }
}