Dynamic Linq: Construct linq queries on fly in C#

Linq is a great feature of .net framework which makes the queries more readable and simplifies the queries. Sometimes we may need to construct the queries dynamically.  Dynamic linq supports to construct queries on fly. This is mainly helpful when you want to construct logic from UI or pass queries from XML or any configuration file. The below is the test data used throughout this article.

Download Dynamic linq code

Test Data:

List<Employee> employees = new List<Employee>();  

  

Employee employee1 = new Employee {Name = "Sampath", Age = 25, Salary = 5000M, Id = 1};  

Employee employee2 = new Employee { Name = "Kumar", Age = 27, Salary = 10000M, Id = 2 };  

Employee employee3 = new Employee { Name = "Anil", Age = 28, Salary = 7000M, Id = 3 };  

Employee employee4 = new Employee { Name = "Vaibhav", Age = 26, Salary = 4000M, Id = 4 };  

Employee employee5 = new Employee { Name = "Sampath", Age = 25, Salary = 5000M, Id = 1 };  

  

employees.Add(employee1);  

employees.Add(employee2);  

employees.Add(employee3);  

employees.Add(employee4);  

employees.Add(employee5);  

  

List<Role> roles = new List<Role>();  

Role role1=new Role {EmpId = 1, RoleName = "QA"};  

Role role2 = new Role { EmpId = 2, RoleName = "QA" };  

Role role3 = new Role { EmpId = 3, RoleName = "Dev" };  

Role role4 = new Role { EmpId = 4, RoleName = "Dev" };  

  

roles.Add(role1);  

roles.Add(role2);  

roles.Add(role3);  

roles.Add(role4);  

In our examples we have taken two lists. One represents list of employees. Another is Roles. We can use dynamic linq to perform different queries. The below are the syntaxes to use different queries.

Where Condition:

/*Employees having salary above 5000*/  

var filteredEmployees = employees.AsQueryable().Where("Salary>=@0", 5000);  

When we apply where condition we can pass multiple parameters. In the above example it is using only one parameter. If you need to use multiple parameters then use and keyword to separate conditions.

/*Employees having salary above 5000*/  

var filteredEmployees = employees.AsQueryable().Where("Salary>=@0 and Id>=@1", 5000,2);  

Select Statement:

/*Select Employees Name and Id who are having salary above 5000*/  

var query  = employees.AsQueryable().Where("Salary>=@0", 5000).Select("new(Name,Age)");  

var finalList = (from dynamic dat in query select dat).ToList();  

Here you might notice that we are using new keyword and we are taking the result of a query and converting it into a list.

Group By:

/*Eliminate Duplicate Employees by performing Group by*/  

query = employees.AsQueryable().GroupBy("new(Name,Age,Salary,Id)","it").Select("new(it.Key.Name, it.Key.Id)");  

finalList = (from dynamic dat in query select dat).ToList();  

Group by can be used when you want to eliminate duplicates in a list or you want to group some set of elements. Group by take two parameters. First parameter represents the columns on which group by is applied. Second parameter you can use some name to refer it in select query.

Join:

/*Performing join using Linq. Select employees who are developers*/  

query = employees.AsQueryable().Join(roles,        "new(outer.Id as EmpId)", "new(inner.EmpId as EmpId)", "new (outer as outerResult, inner as innerResult)").Where("innerResult.RoleName=@0", "Dev");  

  

finalList = (from dynamic dat in query select dat).ToList();  

If you need to join two lists in c# based on some columns or properties, you can use join operation. It takes three parameters. First parameter represents columns involved from outer list. Second parameter represents columns involved from inner list. Third parameter represents aliases for outer and inner lists.

Left Join:

query = employees.AsQueryable().GroupJoin(roles,  

                "new(outer.Id as EmpId)", "new(inner.EmpId as EmpId)", "new (outer as outerResult, inner as innerResult)")  

                 .Where("innerResult.Count()==0");  

finalList = (from dynamic dat in query select dat).ToList();  

Group join can be used to perform left join in C#. In the above example we are filtering employees who do not have role.