Click here to Skip to main content
11,795,568 members (79,840 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: MVC3 EF4.0
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.

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:

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 Wink | ;) .

Thank you in advance,

Posted 3-Apr-12 5:19am
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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

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();
    searchResult = searchResult.Where(m=> == name);
    searchResult = searchResult.Where(m=>m.category == category);
  return View(searchResult);

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.
Naerling at 5-Apr-12 14:06pm
Actually, my 5. It's not obvious, so it's a good tip, that the Where function can be "chained" without it executing every time. I wonder if the query that goes to the database will be the same for two Where functions and one with the two criteria though? I could imagine that the first Where returns a set of records and the second Where returns a subset of the first set.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

<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 Wink | ;) </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:
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).
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:
IEnumerable<Product> GetFilteredProducts(String whereClause)
    return _context.Products.Where(whereClause);
Now you could call this function as follows:
 // 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'.
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());
    // 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! Smile | :)

Some links on Entity SQL:
Entity SQL Overview[^]
Entity SQL Reference[^]
Entity SQL Language[^]
Alberto Biasiutti at 4-Apr-12 3:25am
Wow, thank you very much for the very long answer! It seems like I could use Entity SQL to do what I want, but I think it would be quite tricky to write the correct expression based on user input.. perhaps Expression Trees is a solution more suitable to my situation. Anyway, I'll accept the solution.
Naerling at 4-Apr-12 13:14pm
Thanks. After reading your answer I think you wanted something far simpler than what I described. I guess I misunderstood your question :laugh:
I had not considered Expression Trees, but I guess it's a possibility. I wonder how that is easier than some string concatenation though ;)
Of course with your current solution everything is much easier :)
Alberto Biasiutti at 5-Apr-12 3:03am
I think that creating the correct SQL expression can get more complex in my case, because an example query can ideally go from

-search products that starts with B


-search products created after 1/1/2011 where category is (1, 2 or 3), and where the producer belongs to producerCategory (A B or C)

This is just a random example not really relating to my application, but I think (hope) it gives an idea. Since the filters can ideally be omitted by user input, I think that "manually" building the correct query string can become actually insidious.
Since it seems that with my solution E.F. is smart enough to execute the query only at the end, it seems like the right solution for me.
About expression trees, I read some documentation and examples and it actually seems to be rather more complex than your solution ;) thank you again
Naerling at 5-Apr-12 14:09pm
True, building filters is a tough job :)

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

  Print Answers RSS
0 Maciej Los 340
1 Sergey Alexandrovich Kryukov 316
2 OriginalGriff 285
3 Abhinav S 223
4 KrunalRohit 214
0 OriginalGriff 1,797
1 Maciej Los 1,415
2 KrunalRohit 1,240
3 CPallini 840
4 ppolymorphe 808

Advertise | Privacy | Mobile
Web01 | 2.8.151002.1 | Last Updated 4 Apr 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100