Dynamic LINQ Queries

Dynamic LINQ is a library built on the .NET framework, which allows string based query expressions.

In this article I’ll show some simple ways to build queries based off of the type of field you want to query against in the database.

Note: Throughout this article, I will be using the variable dataItems as the IQueryable interface in which I will run my dynamic LINQ queries against. For more information on IQueryables in C# please see this article.

String Value Query

Exact Match

In order to filter the database values based on an exact match using a regular SQL query in SQL Server Management Studio, one would need to run this line:

Where Title = 'Exact Search Results'

 

The above query would check the string column Title for the exact match of the string Exact Search Results. In order to do this, the below line would need to be the query run, in code, to filter the results from the database.

var resultingDataItems = dataItems.Where("Title = \"Exact Search Results\"");

One important part about this query is the need to escape the quotes to indicate that the comparison is a string comparison.

Handling Multiple Search Parameters

In some scenarios, I’ve needed to query based on multiple parameters. One of the issues with this is that using the multiple .Where() query, it will act as an AND rather than an OR. This means that after the first .Where() query is run, the results will already be filtered down to only the results of the first query and not a combination of the two parameters that you are trying to filter. In the following scenario, I will show you one way of solving this issue.

For this example, we want to filter based off of multiple parameters. We have a column in the database titled Location and can hold one of the following values: United States, Canada, or Europe. Say we want to filter Locations to return all results for items either in the United States or Canada. In Management Studio, the query would look like the following:

Where Location = 'United States' OR Location = 'Canada'

In dynamic LINQ, we would need to do something like this:

var locations = locationsToFilterBy.Split(',');
var dynamicQuery = "";
foreach (var location in locations)
{
    if (string.IsNullOrEmpty(dynamicQuery))
        dynamicQuery = string.Format("Location = \"{0}\"", location);
    else
        dynamicQuery += string.Format(" OR Location = \"{0}\"", location);
}
if (!string.IsNullOrEmpty(dynamicQuery))
    dataItems = dataItems.Where(dynamicQuery);
return dataItems;

Here I essentially build one large query to avoid using multiple .Where() clauses.

String Contains Certain Parameter

In SQL, to see if a field contains a certain value the following query could be run:

Where Title LIKE '%Some Value%'

Now, in order to do this as a dynamic LINQ query, we need to use the Contains keyword.

dataItems.Where("Title.Contains(@0)", "Some Value");

Lets break this query apart bit by bit so we understand exactly what it does.

The Title in the query is the name of the column that you want to run the query against.

The Contains keyword functions the same as the LIKE command in a SQL query.

The (@0) is a placeholder for the value you wish to pass into the query. Above, we are essentially passing the string parameter “Some Value” into the Contains function.

Passing Multiple Parameters to Single Query

You are able to pass multiple parameters into a single query.

var values = new[] {"First Value", "Second Value"};
dataItems.Where("Title.Contains(@0) OR Title.Contains(@1)", values);

When using the .Where() function, you are able to pass an optional parameter. This parameter is an object parameter[], meaning you can pass an array of values as demonstrated above. In the above example, I passed an array of string values, each with a corresponding (@#) value. So, First Value corresponds with (@0) value, and Second Value corresponds with (@1) value.

Int Value Query

In order to filter database values based on integer comparisons, a regular SQL query would look like the following:

Where Duration = 5
Where Duration <= 5
Where Duration > 5

As you can see, the normal comparator operators can be applied in an SQL query. This also will hold true in writing the dynamic LINQ query, in code, to query the database.

dataItems.Where("Duration = 5");
dataItems.Where("Duration <= 5");
dataItems.Where("Duration > 5");

Conclusion

These are just a few uses I’ve come across for Dynamic LINQ. I’m sure there will be much more to update in the future as I come across it, so feel free to comment if you have any thing to add or have any questions.

Thanks for reading!