Geeks With Blogs

@RiaGuru
  • RiaGuru Attending windows azure spring camp @Chennai and its cool about 762 days ago
  • RiaGuru I hosted my blog http://t.co/G1ZHfZB Its related to asp.net mvc,nhibernate. The blog is build on top of funnelweblog.com framework. about 1103 days ago
  • RiaGuru I recently experienced the power of NHIbernate. A very powerful ORM ! about 1220 days ago

Thanigainathan Siranjeevi Sharing my learning

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:

  1. Visual Studio 2008
  2. VS Service Pack 1
  3. Sql Server 2005 Express Edition
  4. 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

Posted on Wednesday, April 29, 2009 7:47 AM | Back to top


Comments on this post: Dynamic Query with Entity Framework

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
HOW ABOUT .StartsWith() or .Contains() or in() search filters?

Thanks for the article.
Left by DANIEL LIU on Jul 07, 2009 5:28 PM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
Hi Siranjeevi,

I am dealing with a similar problem,

Here you have taken a table which have the following columns which you are passing as parameters. That table is fetch dynamically which is refered as 'it' a runtime.

Now my problem is that I have to even deal with the relation ships i.e

if Table1 is main table
and Column2 has a foreign key relationship with Table2
and I want Column2_2 in select

then in select I can simply write Table1.Table2.Column2_2, for which Entity Framework will dynamically do join and fetch data.

Can you just help me how to do this type of query in a way you explained in your article.


Thanks

Ashish Jain
Left by Ashish Jain on Aug 17, 2009 8:10 AM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
Cool. Helped me. Thanks.
Left by Parag on Apr 13, 2010 11:10 PM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
Hi SiranjeeviHow can I transfer you code to generic method?
Left by Daniel on Jun 28, 2010 9:20 PM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
This is cool.
It works as along as you are dealing with the one entity and creating retun based on dynamic where clause.

I got another requirement in which dynamic SQL return different entities based on some condition.

How this SP will be mapped to any entity? Is there any providion in EF to assign the return type based on condn? may be with extended methods?

any help would be appreciated

Thanks
Left by Shailesh on Aug 27, 2010 2:12 AM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
Wow, this is bad code... it really does suck...

What about learning about string.join, string.format and separation of concerns before giving advice to anyone?
Left by McGee on Nov 16, 2010 11:59 AM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
you can help me to improve the code.But dont give bad comments
Left by Thanigainathan on Nov 16, 2010 1:15 PM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
Great solution, you have saved me from a big wast time.
But I have a question:

With this method how to display description instead ID?

of course table A is Related to table B

For example:
table A have ID_City (int) and Username(string)
table B have ID_C (int) and NameOfCIty

When I display Table A whit this solution how to display:

Username and NameOfCIty
instead of
Username and Id_City
?

thanks


Left by Angelo on May 20, 2011 8:42 AM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
Very, very well!
I'm novice in EF4.
This logic it's all that I need.
Thanks a lot!
Crush
Left by Paulo Crush on Jun 08, 2011 12:25 PM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
This is fine... Can we use (or) build this Dynamic queries With Joins & Inner Joins... if yes Please can you give me an Example....

Thanks in Advance...
Left by Santhosh on Jan 31, 2012 11:45 PM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
Sorry, I need Left Outer Joins...
Left by Santhosh on Jan 31, 2012 11:46 PM

# re: Dynamic Query with Entity Framework
Requesting Gravatar...
EasyQuery now allows to build dynamic queries with Entity Framework.
http://devtools.korzh.com/easyquery/usecase/dynamic-query-entity-framework
Left by Sergiy on Sep 02, 2013 9:07 PM

Your comment:
 (will show your gravatar)
 


Copyright © thanigai | Powered by: GeeksWithBlogs.net | Join free