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 really important because it also allowed me to implement an additional (and very important) security check: check 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.

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

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.