<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
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
in your database, which is mapped to an
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; _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; _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
] 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
s to get a filter.
What's worse (from a coders point of view) is that you will have many
's for each scenario.
Does the user want to filter
? 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)
Now you could call this function as follows:
List<Product> products = GetFilteredProducts("it.Artist LIKE 'B%'");
List<Product> products = GetFilteredProducts("it.Price < 10");
In this case
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
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
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 + "%'");
if (Decimal.TryParse(TextBoxPrice.Text, price))
List<Product> products = GetFilteredProducts("it.Price < " + price.ToString());
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
matches the given type (or even present the user with a
that only takes the expected type as input, like a
) 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
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