Introduction
LINQ (Language INtegrated Query) provides a great query
language which can be used to simplify database and object
quering. LINQ queries are embedded within code with out the
need to be contained within a string as with in-line SQL.
While this approach does have many advantages, at first
glance it appears to have one disadvantage. Unlike LINQ, SQL
statements can be created dynamically using various string
concatentation. This often useful for when it is necessary
dynamically add to a Where
Clause and/or the
Sort
Order. While performed differently, LINQ
is capable of performing the same tasks without the need for
string concatentation.
To get started, we will create a basic class and a
generic list to be used for searching as shown below:
class Book
{
public string Title { get; set; }
public string Description { get; set; }
public int Pages { get; set; }
public double Popularity { get; set; }
}List<Book> BookList = new List<Book>;
The Book
class and the BookList
provide us with a simple data source which we can be used to
perform queries against the data. Next we can create the
starting point of the LINQ query as shown below:
var query = from b in BookList
select new { b.Title, b.Description, b.Pages, b.Popularity };
This query will serve as the base for the dynamic
where
clause to follow. Given this data you
would think that the user could perform queries to search on
any combination of those fields, so 4×4 = 16. While you
could create all 16 combinations it is very ineffecient. To
make it as efficient as possible, you should put each
parameter within an if
statement and add the
filters only when necessary. To start with, it would be
logical to perform a LIKE
comparison on the
Title and Description fields and to perform range filters
over the Pages and Popularity. Shown below is the code
necessary to perform the dynamic Title filter.
if (TitleSearch != “”)
{
query = query.Where(b => b.Title.Contains(TitleSearch));
}
Walking through this code we first need to determine if
we should search by title. Next we use the statement
query = query.Where
( … which appends the
where
clause onto the initial query created
above. The next code block shows a similar code snippet to
append the other 3 filters.
if (DescSearch != “”)
{
query = query.Where(b => b.Description.Contains(DescSearch));
}if (PagesMinSearch != null)
{
query = query.Where(b => b.Pages >= PagesMinSearch && b.Pages <= PagesMaxSearch);
}if (PopMinSearch != null)
{
query = query.Where(b => b.Popularity >= PopMinSearch && b.Popularity <= PopMaxSearch);
}
This code shows a similar filter for Description Search
and 2 range filters for Pages and Popularity. Before we end,
I should bring up that another very common filter in dynamic
SQL is the Order By
. Again the order
can also be created dynamically in LINQ.
by
Logically, you would think that is possible to perform the
order by on the Title, Number of Pages and Popularity. The
code block below shows an example of allowing the user to
sort either ascending or descending by any of the 3
fields.
if (OrderByAsc)
{
switch (OrderByField)
{
case “Title”:
query = query.OrderBy(b => b.Title);
break;
case “Pages”:
query = query.OrderBy(b => b.Pages);
break;
case “Pop” :
query = query.OrderBy(b => b.Popularity);
break;
}
}
else
{
switch (OrderByField)
{
case “Title”:
query = query.OrderByDescending(b => b.Title);
break;
case “Pages”:
query = query.OrderByDescending(b => b.Pages);
break;
case “Pop” :
query = query.OrderByDescending(b => b.Popularity);
break;
}
}
Similar to Where
clauses above, we use the
query = query.OrderBy
to append the desired
order by to the query.
Conclusion
In addition to where
and order
clauses many other operations can be peformed
by
dynamically in LINQ, such as Joins
.
Joins
can be very useful especially when needed
by dynamic filters. It is important to note that using this
append
method to make further complex queries
will not cause the query to execute at each step. To execute
the query you will need to perform a ToList, ToArray,
operation, iterate through the results
ToDictionary
or any of several other methods which will execute the
query. This has the advantage of allowing the LINQ to delay
execution as long as possible to assemble all of the
necessary filters and order bys
. These
techniques used above can also be used for creating LINQ to
SQL, LINQ to Entities and LINQ to XML queries.