How to Create Dynamic Query with LINQ

No.of Views3593
Bookmarked0 times
Downloads 
Votes0
By  pranay rana   On  16 Apr 2011 11:04:32
Tag : LINQ , How to
In this article i'm going to discuss about building dynamic query with the LINQ. LINQ to SQL allow user to query data from the database without writing sql queries by writing LINQ queries. LINQ represent each table as one entity and where LINQ queries allows to manipulate data in type safe.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

LINQ to SQL allow user to query data from the database without writing sql queries by writing LINQ queries. LINQ represent each table as one entity and where LINQ queries allows to manipulate data in type safe.But Static LINQ queries not able to meet all our programming needs. A Dynamic LINQ queries 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 allow to write and execute dynamic queries easily. In SQL server we use EXECUTE or sp_executesql to execute dynamic query.

Example:

DECLARE @SQLQuery AS NVARCHAR(500)
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = + convert(@EmpID as varchar(10))
EXECUTE(@SQLQuery) 

We could compose above query as like below as well,

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

Now Question is , how to do create same query when you are use the LINQ?,There are two ways to implement,

1. Use Dynamic LINQ libarary
2. Use PredicateBuilder

To understand both of the above library consider the below screen shot

Image Loading

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

1.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 get the file from this link : Basic Dynamic LINQ C# Sample

so once you install the file you can build query as we can do in pl-sql query.Following code demonstrate 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();
        grdEmployee.DataBind();

In above code I am building strWhere dynamically because there may be some criteria no have value where some has.

2.Predicate Builder

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

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

var predicate = PredicateBuilder.True<employee>();
 
        if(!string.IsNullOrEmpty(txtAddress.Text))
            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();
        grdEmployee.DataBind();
</employee>

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

Difference between both library

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

Note : Above difference is based on the experience that I have with both library. If you know more than please comment so that I can include in my list.

 
Sign Up to vote for this article
 
About Author
 
pranay rana
Occupation-CEO
Company-GMind Solusion
Member Type-Expert
Location-India
Joined date-08 Jan 2011
Home Page-http://pranayamr.blogspot.com
Blog Page-http://pranayamr.blogspot.com
Hey, I am Pranay Rana, working as a Senior Software engineer in mid-size company located in ahmedabad. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 4.3 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 me on my blog - http://pranayamr.blogspot.com/ StackOverFlow - http://stackoverflow.com/users/314488/pranay My CV :- http://careers.stackoverflow.com/pranayamr
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top