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.
