Dynamic LINQ Simple Query
When querying data using Dynamic LINQ, everything can be expressed using strings. This includes predicates, selectors, and sorting.
Strongly typed LINQ looks like:
var result = context.Customers .Where(c => c.City == "Paris") .ToList();
Dynamic LINQ looks like:
var resultDynamic = context.Customers .Where("City == \"Paris\"") .ToList();
When we break down these examples:
The context.Customers in this case is a
DbSet<Customer>
, which implementsIQueryable<T>
. This is the same starting point for both examples.The
Where
-predicate for Dynamic Linq is written as a string, where the same property is used (City) and the same value (Paris).Since the value "Paris" is a string itself, we need to escape the double quotes with a
\
. So the"
becomes a\"
.This also is required for the escape character
\
itself, so if you want to query on a string like"\r\n"
, you need to double escape the escape characters, so it becomes:.Where("Text == \"\\r\\n\"")
For more examples, see this unit-test.
The
ToList()
method for both examples will just return a strongly typed list:List<Customer>
.
Note that instead of using the query value directly in the string like "City == \"Paris\""
you can also use composite string formatting. But instead of using the format item syntax {n}
, you need to use @n
(Where n
is the index). So line 2 in the example above will become:
.Where("City == @0", "Paris")`
And of course, it's not required to specify the value as a hard-coded value. So another valid Dynamic LINQ query example will be:
string cityToSearch = "Paris"; var resultDynamic = context.Customers .Where("City == @0", cityToSearch) .ToList();
See chapters below for more basic query examples.
Note that it's also possible to query data using multiple predicates:
var result = context.Customers .Where(c => c.City == "Paris" && c.Age > 50) .ToList();
This code can be used when using Dynamic LINQ:
var resultDynamic = context.Customers .Where("City == \"Paris\" && Age > 50") .ToList();
Note that instead of the &&
-operand, you can use and
. Also in this example you can use composite string formatting. In that case, the Dynamic LINQ will be:
var resultDynamic = context.Customers .Where("City == @0 and Age > @1", "Paris", 50) .ToList();
You can also mimic the strongly typed Where
-predicate using the => operator, like this:
var resultDynamic = context.Customers .Where("c => c.City == \"Paris\" && c.Age > 50") .ToList();
Selecting entities or only the properties you need is another powerful feature of LINQ. Since the generated SQL will only return a limited set and not the whole entity, it is more useful if you only want to return a few columns.
Here is an example of a strongly typed:
var result = context.Customers .Select(c => new { c.City, c.CompanyName } ) .ToList();
Dynamic LINQ looks like:
var resultDynamic = context.Customers .Select("new { City, CompanyName }") .ToDynamicList();
When we break down these two examples:
- The context.Customers in this case is a
DbSet<Customer>
, which implementsIQueryable<T>
. This is the same starting point for both examples. - The
Select
-method for Dynamic Linq is written as a string, where the same properties are used (City and CompanyName). - For Dynamic LINQ we need to use the
ToDynamicList()
method, because the return from theSelect
is anIQueryable
. The result from both the strongly typed and dynamic example is the same: a list from a dynamic-generated class with two properties (City and CompanyName).
Sorting the result based on a property or multiple properties is another feature from LINQ. Observe the differences.
A strongly typed example to sort on City and then on CompanyName would look like:
var ordered = context.Customers
.OrderBy(c => c.City).ThenBy(c => c.CompanyName)
.ToList();
Dynamic LINQ looks like:
var orderedDynamic = context.Customers .OrderBy("City, CompanyName") .ToList();
When we break down these two examples:
- The context.Customers in this case is a
DbSet<Customer>
, which implementsIQueryable<T>
. This is the same starting point for both examples. - In the strongly typed LINQ example, you need to call the
OrderBy
-method, followed by theThenBy
-method to sort the result; first on the City and then on the CompanyName. For Dynamic Linq you only need to use theOrderBy
-method, and provide the two sort properties as a comma separated string. - The
ToList()
method for both examples will return a strongly typed list:List<Customer>
.
In case you want to sort Descending, or a mixed combination, you can use the following Dynamic LINQ:
var orderedDynamic = context.Customers .OrderBy("City").ThenBy("CompanyName desc") .ToList();
Or
var orderedDynamic = context.Customers .OrderBy("City, CompanyName desc") .ToList();
It's also possible to use ThenBy
:
var orderedDynamic = context.Customers .OrderBy("City").ThenBy("CompanyName desc") .ToList();
The examples above will perform the sorting on the City property Ascending (= default) and then the sorting will be on the CompanyName in a Descending way.
Note that it's not possible to use identifiers of the form @x
to define the property name or the sorting direction.
The identifiers @x
are only used for the (constant) values in the query.
It's also possible to provide a comparer to the OrderBy
or ThenBy
. This can be done by providing class which implements the IComparer<T>
interface.
An example of provising a comparer for a string property:
var result = context.Customers .OrderBy("City", StringComparer.OrdinalIgnoreCase) .ToList();