LINQ Select Dynamic Columns and Values

Question

For various reasons I need to be able to allow the user to select an item from a database based on their choice of columns and values. For instance, if I have a table:

Name   | Specialty       | Rank
-------+-----------------+-----
John   | Basket Weaving  | 12
Sally  | Basket Weaving  | 6
Smith  | Fencing         | 12

The user may request a 1, 2, or more columns and the columns that they request may be different. For example, the user may request entries where Specialty == Basket Weaving and Rank == 12. What I do currently is gather the user's request and create a list ofKeyValuePairwhere theKeyis the column name and theValue` is the desired value of the column:

class UserSearch
{
    private List<KeyValuePair<string, string> criteria = new List<KeyValuePair<string, string>>();

    public void AddTerm(string column, string value)
    {
        criteria.Add(new KeyValuePair<string, string>(column, value);
    }

    public void Search()
    {
        using (var db = new MyDbContext())
        {
            // Search for entries where the column's (key's) value matches
            // the KVP's value.
            var query = db.MyTable.Where(???);
        }
    }
}

/* ... Somewhere else in code, user adds terms to their search 
 * effectively performing the following ... */
UserSearch search = new UserSearch();
search.Add("Specialty", "Basket Weaving");
search.Add("Rank", "12");

Using this list of KeyValuePair's, how can I most succinctly select database items which match all the criteria?

using (var db = new MyDbContext)
{
    // Where each column name (key) in criteria matches 
    // the corresponding value in criteria.
    var query = db.MyTable.Where(???);
}

EDIT: I would like to use EntityFramework instead of raw SQL if I can help it.

UPDATE 3: I am getting closer. I have discovered a way to use LINQ once I've downloaded all the values from the table. This is obviously not super ideal because it downloads everything in the table. So I guess the last step would be to figure out a way where I don't have to download the whole table every time. Here is an explanation of what I am doing:

For every row in the table

db.MyTable.ToList().Where(e => ...

I make a list of bools representing if the column matches the criteria.

criteria.Select(c => e.GetType()?.GetProperty(c.Key)?.GetValue(e)?.ToString() == c.Value)
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                         Basically just gets the value of specific column
                                            by string

Then I check to see if this bool list is all true

.All(c => c == true)

An example of the full code is below:

// This class was generated from the ADO.NET Entity Data Model template 
// from the database. I have stripped the excess stuff from it leaving 
// only the properties.
public class MyTableEntry
{
    public string Name { get; }
    public string Specialty { get; }
    public string Rank { get; }
}

class UserSearch
{
    private List<KeyValuePair<string, string> criteria = new List<KeyValuePair<string, string>>();

    public void AddTerm(string column, string value)
    {
        criteria.Add(new KeyValuePair<string, string>(column, value);
    }

    public async Task<List<MyTableEntry>> Search()
    {
        using (var db = new MyDbContext())
        {
            var entries = await db.MyTable.ToListAsync();
            var matches = entries.Where(e => criteria.Select(c => e.GetType()
                                                                  ?.GetProperty(c.Key)
                                                                  ?.GetValue(e)
                                                                  ?.ToString() == c.Value)
                                                      .All(c => c == true));

            return matches.ToList();
        }
    }
}

It seems as if my problem lies with this segment of code:

e.GetType()?.GetProperty(c.Key)?.GetValue(e)?.ToString()

I am unfamiliar with Expression trees so perhaps the answer lies in them. I may also try Dynamic LINQ.

Answer 1

Since your columns and filters are dynamic, Dynamic LINQ library may help you here

NuGet: https://www.nuget.org/packages/System.Linq.Dynamic/

Doc: http://dynamiclinq.azurewebsites.net/

using System.Linq.Dynamic; //Import the Dynamic LINQ library

//The standard way, which requires compile-time knowledge
//of the data model
var result = myQuery
    .Where(x => x.Field1 == "SomeValue")
    .Select(x => new { x.Field1, x.Field2 });

//The Dynamic LINQ way, which lets you do the same thing
//without knowing the data model before hand
var result = myQuery
    .Where("Field1=\"SomeValue\"")
    .Select("new (Field1, Field2)");

Another solution is to use Eval Expression.NET which lets you evaluate dynamically c# code at runtime.

using (var ctx = new TestContext())
{
    var query = ctx.Entity_Basics;

    var list = Eval.Execute(@"
q.Where(x => x.ColumnInt < 10)
 .Select(x => new { x.ID, x.ColumnInt })
 .ToList();", new { q = query });
}

Disclaimer: I'm the owner of the project Eval Expression.NET

Edit : Answer comment

Be careful, the parameter value type must be compatible with the property type. By example, if the “Rank” property is an INT, only type compatible with INT will work (not string).

Obviously, you will need to refactor this method to make it more suitable for your application. But as you can see, you can easily use even async method from Entity Framework.

If you customize the select also (the return type) you may need to either get the async result using reflection or use ExecuteAsync instead with ToList().

public async Task<List<Entity_Basic>> DynamicWhereAsync(CancellationToken cancellationToken = default(CancellationToken))
{
    // Register async extension method from entity framework (this should be done in the global.asax or STAThread method
    // Only Enumerable && Queryable extension methods exists by default
    EvalManager.DefaultContext.RegisterExtensionMethod(typeof(QueryableExtensions));

    // GET your criteria
    var tuples = new List<Tuple<string, object>>();
    tuples.Add(new Tuple<string, object>("Specialty", "Basket Weaving"));
    tuples.Add(new Tuple<string, object>("Rank", "12"));

    // BUILD your where clause
    var where = string.Join(" && ", tuples.Select(tuple => string.Concat("x.", tuple.Item1, " > p", tuple.Item1)));

    // BUILD your parameters
    var parameters = new Dictionary<string, object>();
    tuples.ForEach(x => parameters.Add("p" + x.Item1, x.Item2));

    using (var ctx = new TestContext())
    {
        var query = ctx.Entity_Basics;

        // ADD the current query && cancellationToken as parameter
        parameters.Add("q", query);
        parameters.Add("token", cancellationToken);

        // GET the task
        var task = (Task<List<Entity_Basic>>)Eval.Execute("q.Where(x => " + where + ").ToListAsync(token)", parameters);

        // AWAIT the task
        var result = await task.ConfigureAwait(false);
        return result;
    }
}

Answer 2

Try this as a general pattern for dynamic where clauses:

//example lists, a solution for populating will follow
List<string> Names = new List<string>() { "Adam", "Joe", "Bob" };
//these two deliberately left blank for demonstration purposes
List<string> Specialties = new List<string> () { };
List<string> Ranks = new List<string> () { };
using(var dbContext = new MyDbContext())
{
    var list = dbContext.MyTable
                        .Where(x => (!Names.Any() || Names.Contains(x.Name)) &&
                                    (!Specialties.Any() || Specialties.Contains(x.Specialty)) &&
                                    (!Ranks.Any() || Ranks.Contains(x.Rank))).ToList();

}

Making some assumptions about your underlying data, the following is the SQL that is likely to be generated by the LINQ shown above:

DECLARE @p0 NVarChar(1000) = 'Adam'
DECLARE @p1 NVarChar(1000) = 'Joe'
DECLARE @p2 NVarChar(1000) = 'Bob'

SELECT [t0].[Name], [t0].[Specialty], [t0].[Rank]
FROM [MyTable] AS [t0]
WHERE [t0].[Name] IN (@p0, @p1, @p2)

To populate these lists within your UserSearch class:

foreach(var kvp in criteria)
{
    switch(kvp.Key)
    {
        case "Name": Names.Add(kvp.Value); break;
        case "Specialty": Specialties.Add(kvp.Value); break;
        case "Rank": Ranks.Add(kvp.Value); break;
    }
}

If you're concerned with maintainability and that the table's columns are going to change often, then you may want to go back to using raw SQL via the SqlCommand class. That way, you can easily generate dynamic selects and where clauses. You could even query the list of columns on the table to dynamically determine which options are available for selecting/filtering.

Answer 3

Fine. Let me give my two cents. If you want to use dynamic LINQ, expression trees should be your option. You can generate LINQ statements as dynamic as you want. Something like following should do the magic.

// inside a generic class.
public static IQueryable<T> GetWhere(string criteria1, string criteria2, string criteria3, string criteria4)
{
    var t = MyExpressions<T>.DynamicWhereExp(criteria1, criteria2, criteria3, criteria4);
    return db.Set<T>().Where(t);
}

Now in another generic class you can define your expressions as.

public static Expression<Func<T, bool>> DynamicWhereExp(string criteria1, string criteria2, string criteria3, string criteria4)
{
    ParameterExpression Param = Expression.Parameter(typeof(T));

    Expression exp1 = WhereExp1(criteria1, criteria2, Param);
    Expression exp2 = WhereExp1(criteria3, criteria4, Param);

    var body = Expression.And(exp1, exp2);

    return Expression.Lambda<Func<T, bool>>(body, Param);
}

private static Expression WhereExp1(string field, string type, ParameterExpression param) 
{
    Expression aLeft = Expression.Property(param, typeof(T).GetProperty(field));
    Expression aRight = Expression.Constant(type);
    Expression typeCheck = Expression.Equal(aLeft, aRight);
    return typeCheck;   
}

Now you can call the methods anywhere as.

// get search criterias from user
var obj = new YourClass<YourTableName>();
var result = obj.GetWhere(criteria1, criteria2, criteria3, criteria4);

This will give you a powerfully dynamic expression with two conditions with AND operator between them to use in your where extension method of LINQ. Now you can pass your arguments as you want based on your strategy. e.g. in params string[] or in key value pair list... doesn't matter.

You can see that nothing is fixed here.. its completely dynamic and faster than reflection and you an make as many expressions and as many criterias...

Answer 4

Not sure what you are after here. But this should give you an Idea.

var query = db.Mytable.Where(x=> x.Specialty == criteria[0].Value && c=> c.Rank == criteria[1].Value).ToString(); 

I am not even sure why you even have to use List. As List needs to be iterated. You can just use Key first the First Condition and Value for the last Condition to avoid List of KeyValuePair.

Answer 5

Continuing @Jakotheshadows's answer but not requiring all the extra checks in the EF output when there's nothing to check, this is closer to what we do in house here:

// Example lists, a solution for populating will follow
var Names = new List<string> { "Adam", "Joe", "Bob" };
// These two deliberately left blank for demonstration purposes
var specialties = new List<string>();
var ranks = new List<string>();
using(var dbContext = new MyDbContext())
{
    var list = dbContext.MyTable
       .FilterByNames(names)
       .FilterBySpecialties(specialties)
       .FilterByRanks(ranks)
       .Select(...)
       .ToList();
}

The Table

[Table(...)]
public class MyTable : IMyTable
{
    // ...
}

The Filter By Extensions

public static class MyTableExtensions
{
    public static IQueryable<TEntity> FilterMyTablesByName<TEntity>(
        this IQueryable<TEntity> query, string[] names)
        where TEntity : class, IMyTable
    {
        if (query == null) { throw new ArgumentNullException(nameof(query)); }
        if (!names.Any() || names.All(string.IsNullOrWhiteSpace))
        {
            return query; // Unmodified
        }
        // Modified
        return query.Where(x => names.Contains(x.Name));
    }
    // Replicate per array/filter...
}

Also, there are significant performance issues with using Contains(...) or Any(...) inside an EF query. There is a much faster method using Predicate Builders. This is an example with an array of IDs (this requires the LinqKit nuget package):

public static IQueryable<TEntity> FilterByIDs<TEntity>(
    this IQueryable<TEntity> query, int[] ids)
    where TEntity : class, IBase
{
    if (ids == null || !ids.Any(x => x > 0 && x != int.MaxValue)) { return query; }
    return query.AsExpandable().Where(BuildIDsPredicate<TEntity>(ids));
}
private static Expression<Func<TEntity, bool>> BuildIDsPredicate<TEntity>(
    IEnumerable<int> ids)
    where TEntity : class, IBase
{
    return ids.Aggregate(
        PredicateBuilder.New<TEntity>(false),
        (c, id) => c.Or(p => p.ID == id));
}

This outputs the "IN" syntax for a query which is really fast:

WHERE ID IN [1,2,3,4,5]