<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:
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[
^]/
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
CheckBox
es and
RadioButton
s 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:
List<Product> products = GetFilteredProducts("it.Artist LIKE 'B%'");
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());
}
else
{
}
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[
^]