Click here to Skip to main content
14,842,038 members
Articles / LINQ
Technical Blog
Posted 26 Jul 2011

Tagged as


10 bookmarked

Dynamic Query with Linq

Rate me:
Please Sign up or sign in to vote.
3.00/5 (2 votes)
26 Jul 2011CPOL2 min read
Building dynamic query with LINQ

In this post, I am going to discuss about building dynamic query with LINQ. LINQ to SQL allows a user to query data from the database without writing SQL queries by writing LINQ queries. LINQ represents each table as one entity and where LINQ queries allow to manipulate data in type safe.

But static LINQ queries are not able to meet all our programming needs. A dynamic LINQ query is needed when we need to retrieve a set of records based on different search parameters.

For example - An employee search screen or a general purpose report which needs to execute a different SELECT statement based on a different WHERE as well as Sorting column to sort data.

Dynamic Query in SQL Server

In SQL, there is concept of dynamic queries which allows to write and execute dynamic queries easily. In SQL Server, we use EXECUTE or sp_executesql to execute dynamic query.

For example:

SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = + 
    convert(@EmpID as varchar(10))


DECLARE @ParameterDefinition AS NVARCHAR(100)
SET @EmpID =100
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID'
SET @ParameterDefinition =  '@EmpID INT'
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

What to do when I am using LINQ? There are two ways to achieve this thing easily:

  1. Use Dynamic LINQ library
  2. Use PredicateBuilder

To understand both of the above libraries, consider the below screen shot:

I want to search data by entering in the above screen. Note here that I may have left some fields blank and some fields filled with value.

Use Dynamic LINQ library

Dynamic LINQ library allows build query which are having varying where clause or orderby. To work with the dynamic LINQ library, you need to download and install file in your project. You can get the file from this link: Basic Dynamic LINQ C# Sample

Once you install the file, you can build query as we can do in PL-SQL query.

The following code demonstrates how to build where dynamically.

string strWhere = string.Empty;
        string strOrderBy = string.Empty;

        if (!string.IsNullOrEmpty(txtAddress.Text))
            strWhere = "Address.StartsWith(\"" + txtAddress.Text + "\")";  
        if (!string.IsNullOrEmpty(txtEmpId.Text))
            if(!string.IsNullOrEmpty(strWhere ))
                strWhere = " And ";
            strWhere = "Id = " + txtEmpId.Text;
        if (!string.IsNullOrEmpty(txtDesc.Text))
            if (!string.IsNullOrEmpty(strWhere))
                strWhere = " And ";
            strWhere = "Desc.StartsWith(\"" + txtDesc.Text + "\")";
        if (!string.IsNullOrEmpty(txtName.Text))
            if (!string.IsNullOrEmpty(strWhere))
                strWhere = " And ";
            strWhere = "Name.StartsWith(\"" + txtName.Text + "\")";

        EmployeeDataContext edb = new EmployeeDataContext();
        var emp = edb.Employees.Where(strWhere);
        grdEmployee.DataSource = emp.ToList();

In the above code, I am building strWhere dynamically because there may be some criteria that does no have value whereas some have.

Predicate Builder

Predicate builder works the same as dynamic linq library but the main difference is that it allows to write more type safe queries easily. You can get the details about predicate builder form here: Dynamically Composing Expression Predicates.

The following code shows how you can use PredicateBuilder easily to create dynamic clause easily.

var predicate = PredicateBuilder.True<employee>();

            predicate = predicate.And(e1 => e1.Address.Contains(txtAddress.Text));
        if (!string.IsNullOrEmpty(txtEmpId.Text))
            predicate = predicate.And(e1 => e1.Id == Convert.ToInt32(txtEmpId.Text));
        if (!string.IsNullOrEmpty(txtDesc.Text))
            predicate = predicate.And(e1 => e1.Desc.Contains(txtDesc.Text));
        if (!string.IsNullOrEmpty(txtName.Text))
            predicate = predicate.And(e1 => e1.Name.Contains(txtName.Text));

        EmployeeDataContext edb= new EmployeeDataContext();
        var emp = edb.Employees.Where(predicate);
        grdEmployee.DataSource = emp.ToList();

So as you see in the above code, I had created one PredicateBuilder for AND condition and building where clause in the same way you can build OR clause by using PredicateBuilder.

Difference Between Both Libraries

  • Predicatebuilder allows to build TypeSafe dynamic queries.
  • Dynamic LINQ library allows to build query with the Dynamic ORDER BY clause.

Note: The above difference is based on the experience that I have with both libraries. If you know more, then please comment so that I can include it in my list.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Pranay Rana
Software Developer (Senior)
India India

Microsoft C# MVP (12-13)

Hey, I am Pranay Rana, working as a Team Leadin MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.

For me def. of programming is : Programming is something that you do once and that get used by multiple for many years

You can visit my blog

StackOverFlow -
My CV :-


Comments and Discussions

QuestionDateTime in DynamicQueryable Pin
mohammadmot27-Jul-12 20:04
Membermohammadmot27-Jul-12 20:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.