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:
1 2 3 4 5 6 7 8 9 10 11 12 |
// 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
// 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:
1 |
dresses = DbContext.Dresses.Where(i => i.DressName == "TestName"); |
into this:
1 |
dresses = DbContext.Dresses.Where("DressName == 'TestName'"); |
or (more conveniently) into this:
1 |
dresses = DbContext.Dresses.Where("DressName == @0", colValue); |
or (even more conveniently) into this:
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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.
Thanks to that extension method, I was able to complete my original task with very few lines of code:
1 2 3 4 5 6 |
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.
Awesome stuff, thanks man! :)
Great!
is it possible use the “OR” operator?
I tried the code the various filters are inserted in AND
Thanks!
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 aStringBuilder
or any string-manipulation loop.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
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.
and then
or something like that (didn’t try the code, but you should get an idea).
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.
Thank you, I’ve fixed that typo :)
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
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