Dynamically Query a data set using Linq

c# dynamic-linq linq

Question

I have a data set that I would like to dynamically query. This code works fine if I explicitly define the field name, but I don't want to do that. I want to dynamically query the data using a variable for the field name. How should I be approaching this?

This works:

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);
}

But 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?

The one of the main benefits of LINQ is that you get type safety. By querying it dynamically, you inherently loose this benefit. Although not really truly "dynamic", you could keep this benefit by writing an extension method that includes the specific fields you want to query:

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}");
  }
}

This way, your code could call the following:

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

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

That being said, to literally answer your question, you technically can build up a LINQ expression dynamically. This might look something like this, but be aware that the performance will be poor and unexpected values may break it and/or open up some security vulnerabilities, especially if they're coming from user input:

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 it necessary to use LINQ? Dynamic SQL (with parameters) would be a lot easier.

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