Dynamically Query a data set using Linq

c# dynamic-linq linq

Question

I would want to dynamically query a collection of data. If I declare the field name directly, this code functions as intended, but I don't want to do so. Using a variable for the field name, I want to dynamically query the data. What method should I be taking here?

This operates:

var dataField = "DemandStatusName";
var searchParam = form.GetValues("columns[" + j + "][search][value]").FirstOrDefault();

if(searchParam != "")
{
    dataSet = (from a in dataSet
               where a.DemandStatusName.Contains(searchParam)
               select a);
}

However, this does not

var dataField = "DemandStatusName";
var searchParam = form.GetValues("columns[" + j + "][search][value]").FirstOrDefault();

if(searchParam != "")
{
    dataSet = (from a in dataSet
               where dataField.Contains(searchParam)
               select a);
}
1
2
2/6/2019 9:37:30 PM

Accepted Answer

How should I be approaching this?

You obtain type safety, which is one of LINQ's key advantages. This advantage is automatically lost when you query it dynamically. While not technically "dynamic," you may maintain this advantage by creating an extension method that contains the particular fields you wish to search.

public static IQueryable<MyTable> WhereContains(this IQueryable<MyTable> source, string field, string value)
{
  switch (field)
  {
    case nameof(MyTable.SomeField):
      return source.Where(a => a.SomeField.Contains(value));
    case nameof(MyTable.SomeOtherField):
      return source.Where(a => a.SomeOtherField.Contains(value));
    // ... etc
    default:
      throw new ArgumentOutOfRangeException($"Unexpected field {field}");
  }
}

Your code may then make the following calls:

var dataField = "DemandStatusName";
var searchParam = form.GetValues("columns[" + j + "][search][value]").FirstOrDefault();

dataset.WhereContains(dataField, searchParam).OrderBy(a => a.Whatever)

To formally respond to your query, you may, in theory, construct a LINQ expression dynamically. This may resemble the following, but be careful that the performance will be subpar, unexpected values may damage it, or perhaps expose certain security vulnerabilities:

var table = Expression.Parameter(typeof(MyTable));
var property = Expression.PropertyOrField(table, dataField);
var param = Expression.Constant(searchParam);
var contains = Expression.Call(property, "Contains", Type.EmptyTypes, searchParam);
var expression = Expression.Lambda<Func<MyTable,bool>>(contains, table);

var result = dataset.Where(expression);
1
2/6/2019 10:13:00 PM

Popular Answer

Is LINQ use required? Dynamic SQL would be much simpler (with parameters).

var dataField = "DemandStatusName";
var searchParam = form.GetValues("columns[" + j + "][search][value]").FirstOrDefault();
var sql = string.Format("select * from tableName where {0} = @param", dataField);

if(searchParam != "")
{
    dataSet = context.Database.SqlQuery<YourEntity>(sql,
                  new SqlParameter("param", searchParam));
}


Related Questions





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