LINQ Select Dynamic Columns and Values

c# entity-framework linq wpf

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.

1
11
2/11/2016 6:15:04 PM

Expert Answer

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;
    }
}
9
8/22/2018 10:10:53 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow