What is the best way to store custom query parameter filters in a database table?

Question

I have an ASP.NET MVC website. In my backend, I have a table called People with the following columns:

• ID
• Name
• Age
• Location
• ... (a number of other columns)

I have a shared web page that uses model binding to request this data. Here is my controller action:

 public ActionResult GetData(FilterParams filterParams) { return View(_dataAccess.Retrieve(filterParams.Name, filterParams.Age, filterParams.location, . . .) } 

which displays something like this:

  http://www.mysite.com/MyController/GetData?Name=Bill .. . 

The dataAccess level simply checks each parameter to see if it is populated by adding it to the db where clause. This works great.

Now I want to be able to save custom filtered queries, and I'm trying to find a better way to save a specific filter. Since some of the filters have only one parameter in the queryString, while others have 10+ fields in the filter, I cannot find the most elegant way to store this “filter” in my database.

The options I can think of are as follows:

• You have full table replication (with some additional columns), but name it PeopleFilterQueries and fill in each FilterName entry and put the filter value in each of the fields (Name, etc.)

• Only store the table with the name FilterName and the row in which I store the actual query string Name = Bill & Location = NewYork. This way, I don’t have to add new columns if the filters change or grow.

What is the best practice for this situation?

Answer 1

If the goal is to maintain a list of recently used filters, I would serialize the complete FilterParams object in the XML / column field after binding the model. By storing it in an XML field, you also provide the flexibility of using XQuery and DML if you need a later version for a more focused request for information.

  public ActionResult GetData(FilterParams filterParams) { // Peform action to get the information from your data access layer here var someData = _dataAccess.Retrieve(filterParams.Name, filterParams.Age, filterParams.location, . . .); // Save the search that was used to retrieve later here _dataAccess.SaveFilter(filterParams); return View(someData); } 

And then in your DataAccess class, you will need two methods: one for saving and one for retrieving filters:

 public void SaveFilter(FilterParams filterParams){ var ser = new System.Xml.Serialization.XmlSerializer(typeof(FilterParams)); using (var stream = new StringWriter()) { // serialise to the stream ser.Serialize(stream, filterParams); } //Add new database entry here, with a serialised string created from the FilterParams obj someDBClass.SaveFilterToDB(stream.ToString()); } 

Then, when you want to get the saved filter, perhaps with Id:

 public FilterParams GetFilter(int filterId){ //Get the XML blob from your database as a string string filter = someDBClass.GetFilterAsString(filterId); var ser = new System.Xml.Serialization.XmlSerializer(typeof(FilterParams)); using (var sr = new StringReader(filterParams)) { return (FilterParams)ser.Deserialize(sr); } } 

Remember that your FilterParams class must have a default constructor (no parameters), and you can use the [XmlIgnore] attribute to prevent properties from being serialized to the database if you want.

 public class FilterParams{ public string Name {get;set;} public string Age {get;set;} [XmlIgnore] public string PropertyYouDontWantToSerialise {get;set;} } 

Note. SaveFilter returns Void, and for brevity, error handling is not required.

Answer 2

Instead of storing the request, I would serialize the FilterParams object as JSON / XML and store the result in your database.

Here I use JSON Serializer regularly:

 using System.IO; using System.Runtime.Serialization.Json; using System.Text; namespace Fabrik.Abstractions.Serialization { public class JsonSerializer : ISerializer<string> { public string Serialize<TObject>(TObject @object) { var dc = new DataContractJsonSerializer(typeof(TObject)); using (var ms = new MemoryStream()) { dc.WriteObject(ms, @object); return Encoding.UTF8.GetString(ms.ToArray()); } } public TObject Deserialize<TObject>(string serialized) { var dc = new DataContractJsonSerializer(typeof(TObject)); using (var ms = new MemoryStream(Encoding.UTF8.GetBytes(serialized))) { return (TObject)dc.ReadObject(ms); } } } } 

You can then deserialize the object and pass it a data access code according to your example above.

Answer 3

You did not specify the exact purpose of the filter.

If you insist on saving the filter in the database table, I will have the following table structure.

• Filterid
• Field
• Fieldvalue

Example table could be

 FilterId Field FieldValue 1 Name Tom 1 Age 24 1 Location IL 3 Name Mike ... 

Answer 4

The answer is much simpler than you do it:

Essentially, you have to store the raw query in your own table and link it to your People table. Do not worry, keeping separate filter parameters.

Define value for storage (2 options)

• Save URL Query String

  This identifier will be useful if you like open API-style applications and want something that you can transfer in reverse order from client to server and reuse without conversion.

• Serialize the Filter object as a string

  This is a very good approach if your goal of storing these filters remains completely server-side and you want the data to be closer to the class object.

Take the People table to the Query Filters Table :

The best strategy here depends on your intentions and performance needs. Some suggestions below:

• Simple filtering (for example, 2-3 filters, 3-4 options)

  Use Many-To-Many because the number of combinations suggests that the same filter combinations will be used many times by many people.

• Complex filtering

  Use One-To-Many , since there are so many possible individual queries, it is less likely that they need to be reused often enough to improve performance and normal performance.

There are, of course, other options, but they will depend on the more detailed nuances of your application. The above suggestions will work well if you say trying to keep track of “recent requests” for users, or “user favorite” filtering options ...

Personal opinion Without knowing much more about your application, I would say (1) save the query string and (2) use OTM related tables ... if and when your application shows the need for further performance profiling or problems with refactoring filter parameters, then come back ... but most likely it is not.

GL.

Answer 5

In my opinion, the best way to save the "Filter" is to have some kind of json text string with each of the column names

So you will have something in db like

Table filters

FilterId = 5; FilterParams = {'age': '> 18', ...

Json will provide many features, such as using age as an array, to have more than one filter in one column, etc.

Also json is a kind of standard, so you can use these “filters” with other db on some day or just “display” the filter or edit it in a web form. If you save the request, you will be attached to it.

Ok, hope this helps!

Answer 6

Assuming that a nosql / object database such as Berkeley DB is out of the question, I would definitely go with option 1. Sooner or later you will find the following requirements or other options:

• Allow users to save their filters, tags, tags, search and share them through bookmarks, tweets, etc.
• Change the parameter or what it does and you will need a version for filters for backward compatibility.
• Provide autocomplete functions through filters, possibly using a user’s filter history to report completion.

The above will be somewhat more difficult to satisfy if you are serializing binary / string data where you need to parse the result and then process it.

If you can use NoSql DB, then you will get all the advantages of sql storage and you will be able to model well an "arbitrary number of key / value pairs".

Answer 7

We thought about using profiles. This is a built-in mechanism for storing user information. From your description of your problem, this seems appropriate.

Profiles in ASP.NET 2.0

I have to admit that the M $ implementation is a bit outdated, but there is nothing wrong with that. If you want to roll on your own, there is a lot of good thinking in their API.