Dynamic query building is most often used in the enterprise world for search screens and other operations. I remember writing code very tediously night and day for a very big search screen. Those days where the nightmares, but good experiences we got. Now these things are made very simple by means of Microsoft's Ado.Net entity framework. ADO.Net Entity Framework is an ORM extension from Microsoft. This is something similar to Linq to Sql . But this one is having some good features and we will be discussing one of them. I will demonstrate this with a sample project.
Prerequisites:
- Visual Studio 2008
- VS Service Pack 1
- Sql Server 2005 Express Edition
- VB.Net knowledge will be a added advantage
ADO.Net Entity framework will be coming with the Service Pack 1. We can take Tour Checklist search functionality for the Demo.
Create a Demo project called "LinqDemo". I am using VB.Net project files.
Fig –1 Creating New project
We will create a new database for our Demo called "DemoDb".This will be placed inside the App_Data folder. Create a table called "TourChecklist" with the following Definitions.
Fig-2 New Database
Fig-3 Table Definitions
Now we are ready to add a new item to the project by Right clicking the solution. Select ADO.Net Entity framework and name it "DemoModel.edmx"
Fig – 2 Adding the EF Model
The new model added will ask for the options for two options named Empty Model (or) Generating Model from database. We will be using the second option to generate from database. The following Picture will demonstrate that.
Fig – 3 Data Model options
Fig – 4 Connection Setting for the model
In fig –4 we are assigning the DemoDb as the database name. Now the mode needs a context name for creating instances. Name it "DemoDBEntities". As a next step the wizard will ask for the tables that have to be included in the model.
fig – 5 Table selection
This page will display all the tables that are present in the DB. Select the TourChecklist table which we are going to query. Clicking the next button will generate the model that we need.
fig – 6 Model
That's it and the model is ready. Now we can design a search WebForm to query this table. Let it be with the default name as "Default.aspx". Create a form with seven columns each containing the fields to be queried.
fig – 7 Search Form
Inside the Search button click event we will do the search functionality. Create a Instance for the DataContext . This instance will hold the details to instantiate the table entities. We also create a instance for the list of ObjectParameter. This is object is used to build the dynamic queries.
Dim dEntity As New DemoDbEntities
Dim ob As New List(Of ObjectParameter)
The traditional dynamic queries were build based upon the input conditions. We will do the same . But the query will hold only the condition and a predicate variable through which we pass the input values. Here there are two important things to be considered.
- The dynamic query
- The value for the dynamic fields
The dynamic query will look like
"it.Name =@Name"
"it" – This is the table alias name that will be dynamically generated
"it.Name"- This indicate the column name for which the search needs to be applied
"@Name" – This is the predicate. We have a to build the object parameter class with this name only. Then pass the object to the where condition of the Entity Class like as follows.
Dim obp=New ObjectParameter("Name", txtName.Text)
Dim qry = dEntity.TourChecklist.Where("it.Name =@Name", obp)
The above code is the syntax for applying a where condition to the Entity Framework. So the next thing is how we are going to build this for a number of search fields.
Solution:
Build a String for forming the query based upon the selected user inputs. At the same time build a list of Object parameters that has to be passed for the where condition. We will be converting the list to an array since "Where condition" can accept a array of ObjectParameters that contains the list of values for the predicates. Since we need to have a And condition between the queries we will be validating if the string is empty using "IIF" condition in VB.Net .
dynStr = IIf(dynStr.Length > 0, dynStr + " And ", dynStr)
So if the final code is listed below and you can get the whole content easily. Code can be downloaded at EFDemo
'Declare the instances for the Entities and ObjectParameter collection
Dim dEntity As
New DemoDbEntities
Dim ob As
New List(Of ObjectParameter)
'Declare the query holder variable
Dim dynStr As
String = String.Empty
'Build the query based on the selected used inputs
If
Me.txtName.Text <> String.Empty Then
dynStr += "it.Name =@Name"
'Add the object parameter into the collection
ob.Add(New ObjectParameter("Name", txtName.Text))
End
If
If
Me.txtGuide.Text <> String.Empty Then
dynStr = IIf(dynStr.Length > 0, dynStr + " And ", dynStr)
dynStr += "it.GuideName =@guide"
ob.Add(New ObjectParameter("guide", txtGuide.Text))
End
If
If
Me.txtModel.Text <> String.Empty Then
dynStr = IIf(dynStr.Length > 0, dynStr + " And ", dynStr)
dynStr += "it.Model =@Model"
ob.Add(New ObjectParameter("Model", txtModel.Text))
End
If
If
Me.txtJourneyDate.Text <> String.Empty Then
dynStr = IIf(dynStr.Length > 0, dynStr + " And ", dynStr)
dynStr += "it.JourneyDate =@jrDate"
ob.Add(New ObjectParameter("jrDate", CDate(txtJourneyDate.Text)))
End
If
If
Me.chkBk.Checked Then
dynStr = IIf(dynStr.Length > 0, dynStr + " And ", dynStr)
dynStr += "it.BookedLodge =@brlodge"
ob.Add(New ObjectParameter("brlodge", True))
End
If
If
Me.chkFood.Checked Then
dynStr = IIf(dynStr.Length > 0, dynStr + " And ", dynStr)
dynStr += "it.FoodBeverage =@fbr"
ob.Add(New ObjectParameter("fbr", True))
End
If
If
Me.chkMobile.Checked Then
dynStr = IIf(dynStr.Length > 0, dynStr + " And ", dynStr)
dynStr += "it.Mobile =@cmobile"
ob.Add(New ObjectParameter("cmobile", True))
End
If
Dim qry
If dynStr.Length > 0 Then
If dynStr.EndsWith("And") Then
dynStr.Remove(dynStr.Length, -3)
End
If
'Executing the query for the where condition
qry = dEntity.TourChecklist.Where(dynStr, ob.ToArray)
Else
'Executes the whole resule
qry = dEntity.TourChecklist
End
If
'Bind the Grid Control
Me.grdResults.DataSource = qry
Me.grdResults.DataBind()
Conclusion:
ADO.Net has a beautiful feature. The query is executed only when the databind event in the Grid is fired. This makes the performance very good. There are a lot more features available with EF framework and i have given one of the usages. If one uses this framework effectively in their projects then they can see a good productivity and development time will be very less. Thanks for reading my article and please bar with me if I had done any mistakes.
Thanks,
Thanigainathan Siranjeevi