Build dynamic query

Question

0.00/5 (No votes)

See more:

I'm developing an MVC3 and EF.4.1 application where the user must be able to perform "custom" searches.

For example, I must be able to search activities specifing that they must be "my activities", or that they are activities for a "specific customer", or both.

What I would like to know, is if there's a way to build the query dynamically based on user input in a way similar to this:

NOTE: function names like addcondition() are "invented" to better explain, hopefully, what I need.

C#

public actionresult search(bool filterbyuser, int userid, bool filterbycontact, int contactid )
{
activities = db.activities;

//add where clause to restrict search result

if (filterByUser) {
   activites.addcondition(model=>model.userid == userid);
}

if (filterByContact){
  activities.addcondition(model=>model.contactid == contactid);
}

return PartialView(activities);
}

This is a simplified example, because I should be able to search, for example, activities of many users done to many customers, and with many other possible filters (for example contact categorization, user role, activity type and so on), so a "solution" like:

C#

if (filterbyuser && filterbycontact){
  //filter both fields
}
else if (filterbyuser){
  //filter user
}
else {
  //filter contact
}

must be avoided, otherwise there will be a mess of if condition to check wich fields have been set by user, granting me a safe place in the hall of shame for years to come ;) .

Thank you in advance,

Alberto

Posted 3-Apr-12 5:19am

Alberto Biasiutti

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Alberto Biasiutti · Accepted Answer · 2012-04-04T03:18:00

Well, it seems like It's all simpler than I thought. I can simply do:

C#

/*
Example class Product
*/

public class Product{
  public int id {get;set;}
  public string name {get;set;}
  public string category {get;set;}
}

//controller action for search

public ActionResult searchProducts(string name, string category){

  IQueryable<product> searchResult = db.Products.asQueryable();

  if(!String.IsNullOrEmpty(name)){
    searchResult = searchResult.Where(m=>m.name == name);
  }

  if(!String.IsNullOrEmpty(category)){
    searchResult = searchResult.Where(m=>m.category == category);
  }
  

  return View(searchResult);
}

</product>

I thought that this would perform multiple queries, one for each subsequent "Where" added; instead, the query is actually performed each time I loop in the collection, in this case when I pass the data to the view for rendering.

Sander Rossel · Accepted Answer · 2012-04-03T10:26:00

<edit>Oops... I just noticed you are using ASP.NET and I linked to WinForms Controls... This doesn't matter for the idea though. I just put the links there as a reference, you shouldn't need to click them if you know your trade ;)</edit>

Not sure what you are trying to achieve exactly, but it sounds like you want something like dynamic SQL[^] in Entity Framework[^].
Let's say you have a table called Product in your database, which is mapped to an Entity called Product.
Product is always a CD and has the following fields:
ID, Number, Artist, Album, Price

And the following products:

1 - CD001 - Amy Winehouse - Back To Black - 19,99
2 - CD002 - Amy Winehouse - Frank - 15,99
3 - CD003 - Beach Boys - Surfin' USA - 9,99
4 - CD004 - Björk - Homogenic - 15,99

Now, let's say you want the users to be able to filter for all CD's by Amy Winehouse.
This could be achieved by the following code:

C#

String name = TextBox1.Text; // Amy Winehouse
_context.Products.Where(p => p.Artist = name).ToList();

However, now you want to retrieve all artists that start with a B (Björk and Beach Boys).

C#

String name = TextBox1.Text; // B
_context.Products.Where(p => p.Artist.StartsWith = name).ToList();

At this point your user interface would need an option to filter by artist and a CheckBox[^]/RadioButton[^] to check if it's the full name or only the first letter.
The user interface will become pretty clogged up and before you know it a user can select many CheckBoxes and RadioButtons to get a filter.
What's worse (from a coders point of view) is that you will have many cases and if/then/else's for each scenario.
Does the user want to filter Artist, Album, Price? And does the user want to know the full name, first letter, last letter, anything lower than, higher than, equal to? etc.

This is where entity sql comes to the rescue!
Consider the following:

C#

IEnumerable<Product> GetFilteredProducts(String whereClause)
{
    return _context.Products.Where(whereClause);
}

Now you could call this function as follows:

C#

 // Returns artists that start with B.
List<Product> products = GetFilteredProducts("it.Artist LIKE 'B%'");
// Returns products that cost less than 10.
List<Product> products = GetFilteredProducts("it.Price < 10");

In this case B or 10 could be user input, the rest of the query could be pre-set by you from a database, so the user could select one of the filters you made for them and they could enter the value they want to filter by in one or more TextBox[^]es.
Or you could create something fancy where they can select the fieldname, function, type etc. from a Control[^] which gets parsed to the filter string you want. But this is a very difficult task. I achieved something similiar by using the DevExpress FilterControl and parsing the operands to their respective filter strings, making for very fine-grained, but still semi-user friendly filtering.

So let's revice the above example. We have a ComboBox[^] on the form with two values: 'Artist starts with' and 'Price lower than'.

C#

List<Product> products = GetFilteredProducts("it.Artist LIKE '" + TextBoxArtist.Text + "%'");
Decimal price;
if (Decimal.TryParse(TextBoxPrice.Text, price))
{
    List<Product> products = GetFilteredProducts("it.Price < " + price.ToString());
}
else
{
    // No valid price was entered.
}

Now if you could get the complete filter string excluding values (but including the value type) from the database you could easily check if the text in the TextBox matches the given type (or even present the user with a Control that only takes the expected type as input, like a CheckBox for Boolean) and paste the value in the filter string. Perhaps by using a Regular Expression[^]. For example: it.Artist LIKE '[@PARAM; STRING]'. Here you can replace [@PARAM...] with the user input and you also know the user input is of the type String.

I isn't easy to create 'dynamic filters' this way, but using Entity SQL your queries ARE parameterized[^] which is a HUGE advantage over dynamic SQL! And in this case using dynamic SQL wouldn't be easy either. However, this approach is future proof (adding more fields to your table only results in typing some extra filters without having to alter your source code!) and very flexible.

I hope this answered (at least some of) your question, put you on the right track or gave you some idea's. Good luck! :)

Some links on Entity SQL:
Entity SQL Overview[^]
Entity SQL Reference[^]
Entity SQL Language[^]

Build dynamic query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0