Dynamic LINQ

LINQ to SQL is an extension of LINQ that allows developers to write "queries" in .NET to retrieve and manipulate data from a SQL Server database. This gives the ability to access relational database objects as normal .Net objects.

LINQ to SQL integrates SQL based schema definitions to the common language runtime (CLR) type system. This provides strong typing, syntax checking, intellisense over SQL objects, while retaining the expressive power of the relational model and the performance of query evaluation directly in the underlying database.

LINQ to SQL enables us to write type-safe queries (static SQL Queries ) against an IQueryable objects (SQL data tables). But some times we need to construct the queries dynamically on the fly. Normally we build dynamic SQL queries by concatenating strings together. The same can be achieved in LINQ to SQL using the Dynamic Expression API which extends the core LINQ API capabilities through dynamic construction of expression trees using the classes in the System.Linq.Expressions namespace.

Normally LINQ queries constructed by using language operators or type-safe lambda extension methods. but the dynamic query library provides us with string based extension methods, where we will pass the expressions in string format.

The Dynamic Expression API is present in the System.Linq.Dynamic namespace. This API provides classes:

- Responsible for string-based querying by using IQueryable extension methods.

- Responsible for parsing strings and producing the equivalent expression trees.

- Responsible for generating new classes which represent the Select columns in the dynamically constructed query.

The Dynamic Expression API provides the following IQueryable Extension Methods as part of System.Linq.Dynamic.DynamicQueryable class for dynamically querying objects.

public static IQueryable Where(this IQueryable source,  string predicate, params object[] values);
public static IQueryable<T> Where<T>(this IQueryable<T> source,   string predicate, params object[] values);
public static IQueryable Select(this IQueryable source,    string selector, params object[] values);
public static IQueryable OrderBy(this IQueryable source,    string ordering, params object[] values);
public static IQueryable<T> OrderBy<T>(this IQueryable<T> source,  string ordering, params object[] values);
public static IQueryable Take(this IQueryable source, int count);
public static IQueryable Skip(this IQueryable source, int count);
public static IQueryable GroupBy(this IQueryable source,    string keySelector, string elementSelector, params object[] values);
public static bool Any(this IQueryable source);
public static int Count(this IQueryable source);

These methods operate on IQueryable and use strings instead of lambda expressions to express predicates, selectors, and orderings.

Constructing Dynamic Queries using IQueryable extension Methods

DataClassesDataContext db=new DataClassesDataContext();
var products = from p in db.Products
where p.Model == "SD1000" && p.onsale==true
select p;

We can construct the above query using the IQueryable extension method Where() like below:

DataClassesDataContext db=new  DataClassesDataContext();
 var products = db.Products
     .Where("Model==@0 && onsale==@1","SD1000", true) ;

In the above query Where method is the IQueryable extension method provided in the Dynamic Expression API and takes the input string and produces an equivalent expression tree dynamically to query the products table. Even the string passed to Where method can be constructed dynamically like below

DataClassesDataContext db=new  DataClassesDataContext();
string condition = "Model==\"SD1000\" && onsale==true ";
var products = db.Products.Where(condition );

Like the above example we can use the other IQueryable extension methods to construct the queries dynamically according to our programming needs. Some more examples are:

 DataClassesDataContext db=new DataClassesDataContext();
var product = from p in db.Products
where p.Model == "SD1000" && p.onsale == true
orderby p.Name
select new { p.ItemId, p.Name, p.Model1, p.onsale };

That can be converted like below:

DataClassesDataContext db = new DataClassesDataContext();
string condition = "Model==\"SD1000\" && onsale==true ";
var products = db.Products
     .Where(condition)
     .OrderBy("Name")
     .Select(" new (ItemId,Name,Model,onsale)");

In the above code the OrderBy extension method is used for ordering the results by Name column and the Select extension method is used for creating the classes dynamically . Observe that the syntax is using '(',')' instead of '{','}' while constructing new classes dynamically as opposed to static class constuction.

The ParseException Class

Normally when constructing dynamic queries by concatenating strings to form an expression string there is a chance of getting parsing errors. The Dynamic Expression API provides ParseException class which can be used to catch the parsing errors. The Position property gives the character index in the expression string at which the parsing error occurred and the message gives the error message.

try
{
DataClassesDataContext db=new DataClassesDataContext();
string condition = "Model==\"SD1000\" && onsale==true ";
var products = db.Products
     .Where(condition)
     .OrderBy("Name")
     .Select(" new (ItemId,Name,Model,onsale)");
}
catch (ParseException ex)
{
Response.Write(ex.Position + ex.Message);
}

The Take() extension method is used to select a no of rows from the query result and Skip() extension method is used to skip no of rows from the query result.

DataClassesDataContext db=new DataClassesDataContext();
string condition = "Model==\"SD1000\" && onsale==true ";
var product2 = db.Products.Where(condition).OrderBy("Name").Select(" new (ItemId,Name,Model,onsale)").Skip(2).Take(3);

The Count extension method is used to count the no of rows returned as the query result.

DataClassesDataContext db = new DataClassesDataContext();
string condition = "Model==\"SD1000\" && onsale==true ";
var count = db.Products.Where(condition).Count();

The GroupBy extension method is used to group the returned results in to IEnumerable groups.

DataClassesDataContext db = new DataClassesDataContext();
var Models= db.Products.GroupBy("Model", "new (Model)");

JOINS in LINQ

There is no direct support for Joins using the extension methods provided in the Dynamic Expression API. However one can use joins indirectly by using inner queries, where the inner queries can be constructed using Dynamic Expression API like below.

DataClassesDataContext db=new DataClassesDataContext();
string condition = "Model==\"SD1000\" && onsale==true ";
var products = from m in (db.Models.Where("Model1==@0", "SD1000"))
join p in (db.Products.Where(condition).OrderBy("Name"))
on m.Model1 equals p.Model
select new { p.ItemId, p.Name, p.Model, m.company, p.Price, p.onsale, p.discount, p.Description, m.features };

While using joins on inner queries, which are constructed using the Dynamic Expression API the Select extension method can't be used on the dynamic queries.

This tutorial is written by RELIANCE CONSULTING.