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

How to Deploy a ASP.NET Core 2 Web Application to Linux CentOS - Tutorial

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:

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:

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:

into this:

or (more conveniently) into this:

or (even more conveniently) into this:

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:

  1. Retrieve the column type for each given column-based search.
  2. 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:

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:

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.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

7 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

    1. 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. 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. 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 &lt; 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).

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.