ASP.NET C# - set Column names programmatically with Dynamic Linq

Today, while I was working to one of my ASP.NET Core + Entity Framework Core projects, I've stumbled upon a rather typical scenario: being able to make a dynamic query - a query on variable columns - using LINQ.

Here's how my data retrieval method looked like:

// dresses: the Entity object to filter
var dresses = DbContext.Dresses.AsQueryable();

// filters: an IEnumerable of Dictionary<string,string>
foreach (var d in filters)
{
    string colName= d["name"];
    string colValue = d["value"];
   
    // TODO: filter my dresses programmatically
    //       using colName and colValue
}

// dresses: the Entity object to filter

var dresses = DbContext.Dresses.AsQueryable();

// filters: an IEnumerable of Dictionary<string,string>

foreach (var d in filters)

{

string colName= d["name"];

string colValue = d["value"];

// TODO: filter my dresses programmatically

// using colName and colValue

}

As we can see, I had a list dresses - as a IQueryable object - and a collection of string-based filters I needed to apply. Those filters are the result of a typical user "advanced search query" over zero, one or multiple columns.

Given that situation, a rather easy way to perform such task would be doing somethig like this:

// dresses: the Entity object to filter
var dresses = DbContext.Dresses.AsQueryable();

// filters: an IEnumerable of Dictionary<string,string>
foreach (var d in filters)
{
    string colName= d["name"];
    string colValue = d["value"];
   
    switch (colName) 
    {
        case "Id":
            dresses = dresses.Where(i => i.Id.ToString() == colValue);
            break;
        case "DressName":
            dresses = dresses.Where(i => i.DressName.Contains(colValue));
            break;
        case "DressDescription":
            dresses = dresses.Where(i => i.DressDescription.Contains(colValue));
            break;
        default:
            // column not handled - nothing to filter
            break;
    }
}

// dresses: the Entity object to filter

var dresses = DbContext.Dresses.AsQueryable();

// filters: an IEnumerable of Dictionary<string,string>

foreach (var d in filters)

{

string colName= d["name"];

string colValue = d["value"];

switch (colName)

{

case "Id":

dresses = dresses.Where(i => i.Id.ToString() == colValue);

break;

case "DressName":

dresses = dresses.Where(i => i.DressName.Contains(colValue));

break;

case "DressDescription":

dresses = dresses.Where(i => i.DressDescription.Contains(colValue));

break;

default:

// column not handled - nothing to filter

break;

}

As we can see, such kind of approach would definitely work, yet it has some big downsides: each single entity column needs to be manually coded within that switch/case statement, which would easily become big and quite hard to maintain.

Since I had a lot of columns to filter - and a lot of other entities I would have to make "searchable" in that same way - I adopted a DRY and arguably far better approach using the awesome System.Linq.Dynamic NuGet Package, which also has an awesome .NETStandard & .NET Core port called System.Linq.Dynamic.Core.

That library does just like I needed: add dynamic language functionalities to LINQ using a powerful set of extensions that could be used to turn something like this:

dresses = DbContext.Dresses.Where(i => i.DressName == "TestName");

1	dresses = DbContext.Dresses.Where(i => i.DressName == "TestName");

into this:

dresses = DbContext.Dresses.Where("DressName == 'TestName'");

1	dresses = DbContext.Dresses.Where("DressName == 'TestName'");

or (more conveniently) into this:

dresses = DbContext.Dresses.Where("DressName == @0", colValue);

1	dresses = DbContext.Dresses.Where("DressName == @0", colValue);

or (even more conveniently) into this:

dresses = DbContext.Dresses.Where(StringFormat("{0} == @1", colName), colValue);

1	dresses = DbContext.Dresses.Where(StringFormat("{0} == @1", colName), colValue);

As we can easily guess, @0 and @1 are placeholders for variables, just like {0} and {1} for the String.Format method.

Despite its awesomeness, the System.Linq.Dynamic.Core alone was not enough for my given scenario, since I had to filter both Int32 and String entity values using the text inputed by the users. I needed a method that could be able to do some additional work, such as:

Retrieve the column type for each given column-based search.
Use the appropriate Where query to properly "filter" that type.

Point 1 was an important requirement because I wanted to implement an additional security check: verifying that the given colName was really the string representation of a valid column of that entity, in order to defy the risk of SQL injection attempts.

In order to implement all that, I came out with the following extension method:

public static IQueryable<TEntity> WhereContains<TEntity>(
    this IQueryable<TEntity> query, 
    string field, 
    string value,
    bool throwExceptionIfNoProperty = false,
    bool throwExceptionIfNoType = false) 
    where TEntity : class
{
    PropertyInfo propertyInfo = typeof(TEntity).GetProperty(field);
    if (propertyInfo != null)
    {
        var typeCode = Type.GetTypeCode(propertyInfo.PropertyType);
        switch (typeCode)
        {
            case TypeCode.String:
                return query.Where(String.Format("{0}.Contains(@0)", field), value);
            case TypeCode.Boolean:
                var boolValue = (value != null 
                    && (value == "1" || value.ToLower() == "true"))
                    ? true
                    : false;
                return query.Where(String.Format("{0} == @0", field), boolValue);
            case TypeCode.Int16:
            case TypeCode.Int32:
            case TypeCode.Int64:
            case TypeCode.UInt16:
            case TypeCode.UInt32:
            case TypeCode.UInt64:
                return query.Where(String.Format("{0}.ToString().Contains(@0)", field), value);

            // todo: DateTime, float, double, decimals, and other types.

            default:
                if (throwExceptionIfNoType)
                    throw new NotSupportedException(String.Format("Type '{0}' not supported.", typeCode));
                break;
        }
    }
    else
    {
        if (throwExceptionIfNoProperty)
            throw new NotSupportedException(String.Format("Property '{0}' not found.", propertyInfo.Name));
    }
    return query;
}

public static IQueryable<TEntity> WhereContains<TEntity>(

this IQueryable<TEntity> query,

string field,

string value,

bool throwExceptionIfNoProperty = false,

bool throwExceptionIfNoType = false)

where TEntity : class

{

PropertyInfo propertyInfo = typeof(TEntity).GetProperty(field);

if (propertyInfo != null)

{

var typeCode = Type.GetTypeCode(propertyInfo.PropertyType);

switch (typeCode)

{

case TypeCode.String:

return query.Where(String.Format("{0}.Contains(@0)", field), value);

case TypeCode.Boolean:

var boolValue = (value != null

&& (value == "1" || value.ToLower() == "true"))

? true

: false;

return query.Where(String.Format("{0} == @0", field), boolValue);

case TypeCode.Int16:

case TypeCode.Int32:

case TypeCode.Int64:

case TypeCode.UInt16:

case TypeCode.UInt32:

case TypeCode.UInt64:

return query.Where(String.Format("{0}.ToString().Contains(@0)", field), value);

// todo: DateTime, float, double, decimals, and other types.

default:

if (throwExceptionIfNoType)

throw new NotSupportedException(String.Format("Type '{0}' not supported.", typeCode));

break;

}

else

{

if (throwExceptionIfNoProperty)

throw new NotSupportedException(String.Format("Property '{0}' not found.", propertyInfo.Name));

}

return query;

}

I could definitely call it Where, but I wanted to be 100% sure it wouldn't conflict with other existing (or future) LINQ extension methods: that's why I went for WhereContains, which is also arguably a better name since it handles a very specific task - programmatically add text-search filters for various Types. I only implemented strings, integers and booleans for the time being, since they're all I needed, but it can be easily extended to support even dates, times, and so on.

UPDATE: it's worth noting that, starting with Entity Framework Core v1.1, the Contains() method changed its behaviour from case-insensitive to case-sensitive: if you want to "revert" such change and be able to perform case-insensitive search using the above method in EF Core v1.1+, read this post.

Thanks to that extension method, I was able to complete my original task with very few lines of code:

foreach (var d in filters)
{
    string field = d["field"];
    string value = d["value"];
    items = items.WhereContains(field, value);
}

foreach (var d in filters)

{

string field = d["field"];

string value = d["value"];

items = items.WhereContains(field, value);

}

Not too bad, right?

Conclusion

That's it, at least for now: I hope that my extension method will be useful to other ASP.NET developers who are looking for a quick and effective way to deal with dynamic LINQ-based queries.

Print Friendly & PDF Download

9 Comments on “ASP.NET C# - set Column names programmatically with Dynamic Linq How to use the System.Linq.Dynamic and System.Linq.Dynamic.Core package libraries to programmatically set a IQueryable column name in a LINQ Where query”

Sander says:

December 18, 2019 at 11:37

Awesome stuff, thanks man! :)

Valerix says:

December 31, 2019 at 11:35

Great!
is it possible use the “OR” operator?
I tried the code the various filters are inserted in AND
Thanks!

1. Ryan says:
  
  January 6, 2020 at 03:15
  
  Hello there,
  not sure I’ve understood your question properly: however, here’s a Dynamic Linq query example with “OR”:
  
  var query = db.Customers.Where("CityName.Contains(@0) or CityName.Contains(@1)", "London", "USA")
  
  You can chain multiple OR statements using a StringBuilder or any string-manipulation loop.
  
  1. Valerix says:
    
    January 6, 2020 at 19:06
    
    Thanks for the reply!
    Sorry for my English. I try to explain myself better…
    I would like to use the “WhereContains” method that you wrote in this article with “OR”, so as to write column names programmatically
    
    1. Ryan says:
      
      January 18, 2020 at 03:27
      
      You can’t, you need to change its signature to accept an array of values instead than a single value AND THEN cycle the values and programmatically write the OR condition that I posted you in the previous reply.
      
      var whereChain = new List<string>(); for (int i = 0; i < values.Count; i++) { whereChain.Add(String.Format("CityName.Contains(@{0})",i)); }
      
      and then
      
      var query = db.Customers.Where(string.Join(" OR ", whereChain), values);
      
      or something like that (didn’t try the code, but you should get an idea).
      
Marco says:

November 17, 2020 at 22:29

I used a method similar to
dresses = DbContext.Dresses.Where(“@0 == @1”, colName, colValue);
in my code and didn’t work. That statement will simply check whether the name of the column is the same as colValue.
On the contrary
dresses = DbContext.Dresses.Where(StringFormat(“{0} == @1”, colName), colValue);
as you did in your extension method, worked for me.

1. Ryan says:
  
  November 20, 2020 at 05:09
  
  Thank you, I’ve fixed that typo :)
  
Rohit says:

January 4, 2021 at 15:13

Hi ,

Thanks for such great article.

I have one question, how can i check for the column in child entities for where condition ?

This works great if i need to search value from column from single entity.

Regards,
Rohit Pundlik

1. Ryan says:
  
  January 7, 2021 at 05:42
  
  Hi, this answer from the Dynamic Linq KB should help you:
  
  https://dynamic-linq.net/knowledge-base/38118300/how-to-filter-child-collection-with-linq-dynamic

ASP.NET C# - set Column names programmatically with Dynamic Linq How to use the System.Linq.Dynamic and System.Linq.Dynamic.Core package libraries to programmatically set a IQueryable column name in a LINQ Where query

Conclusion

About Ryan

9 Comments on “ASP.NET C# - set Column names programmatically with Dynamic Linq How to use the System.Linq.Dynamic and System.Linq.Dynamic.Core package libraries to programmatically set a IQueryable column name in a LINQ Where query”

Leave a Reply Cancel reply

Conclusion

Related Posts

ASP.NET Core 8 and Angular - Sixth Edition Learn how to build stunning web apps with Angular backed up with an efficient and lightweight Web API back-end built with ASP.NET Core

Building Web APIs with ASP.NET Core in Manning's Top 10! A great achievement for the "Building Web APIs with ASP.NET Core" book's first publishing week

Building Web APIs with ASP.NET Core - The Book Build fully-featured APIs with ASP.NET Core! An all-practical guide on building modern APIs utilizing REST and GraphQL standards

About Ryan

9 Comments on “ASP.NET C# - set Column names programmatically with Dynamic Linq How to use the System.Linq.Dynamic and System.Linq.Dynamic.Core package libraries to programmatically set a IQueryable column name in a LINQ Where query”

Leave a Reply Cancel reply