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
foreach (var d in filters)
{
string colName= d;
string colValue = d;
// 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
foreach (var d in filters)
{
string colName= d;
string colValue = d;
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");
into this:
dresses = DbContext.Dresses.Where("DressName == 'TestName'");
or (more conveniently) into this:
dresses = DbContext.Dresses.Where("DressName == @0", colValue);
or (even more conveniently) into this:
dresses = DbContext.Dresses.Where("@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 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:
public static IQueryable WhereContains(
this IQueryable 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.
