#dynamiclinq

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.

Conclusion