Using ASP.NET and MVC 3, how can I create hidden fields so that a List with an array as a value of each item in the list binds correctly?

Question

I have a list of query statements that need to be posted back to the MVC controller when another statement needs to be added on to the end. For the test I am trying to create right now, the page starts with a list of filters. When doing this example, the page will create the field for the filters as this:

<input id="filters_0__PropertyName" name="filters[0].PropertyName" type="hidden" value="State">
<input id="filters_0__Operator" name="filters[0].Operator" type="hidden" value="=">
<input id="filters_0__Value" name="filters[0].Value" type="hidden" value="CA">

But when I see the form actually posting back to the controller, the list comes back as:

PropertyName = "State"
Operator = "="
Value= "new string[1]" // The value comes back in the first index of the array

I have to cast the Value parameters as an Array and take the first index to be able to get the value out. This is alright but not ideal. The main problem comes when the FilterField contains an array of integers or strings for the Value property. When that happens, the HTML comes out as:

<input id="filters_2__PropertyName" name="filters[3].PropertyName" type="hidden" value="Schedule_Num">
<input id="filters_2__Operator" name="filters[3].Operator" type="hidden" value="IN">
<input id="filters_2__Value" name="filters[3].Value" type="hidden" value="System.Int32[]">

The value then contains the object type and no values at all. So the modelbinder gets confused and everything breaks. Is there an easier way to tie this list of values to the View?

FilterField.cs:

public class FilterField
{
    public string PropertyName { get; set; }
    public string Operator { get; set; }
    public object Value { get; set; }

    public FilterOutput GetOutput()
    {
        return new FilterOutput(PropertyName, Operator, Value);
    }
}

FilterOutput.cs

public class FilterOutput
{
    private List<QueryProperty> _properties = new List<QueryProperty>();
    private string _propertyName;
    private string _op;
    private object _value;

    public string Sql { get; set; }
    public QueryProperty[] Properties { get; set; }

    public FilterOutput(string propertyName, string op, object value)
    {
        var sql = "[{0}] {1} {2}";
        Sql = string.Format(sql, propertyName, op, FormatParameter(propertyName, op, value));
        Properties = _properties.ToArray();
    }

    private string FormatParameter(string propertyName, string op, object value)
    {
        _properties.Clear();
        var sb = new StringBuilder();
        switch (op.ToUpper())
        {
            case "IN":
                {
                    var values = value as Array;
                    sb.Append("{");
                    var inCount = 0;
                    foreach (var v in values)
                    {
                        var pName = propertyName + inCount;
                        if (inCount == 0)
                            sb.Append("@" + pName);
                        else
                            sb.Append(",@" + pName);
                        _properties.Add(new QueryProperty { Name = pName, Value = v });
                        inCount++;
                    }
                    sb.Append("}");
                }
                break;
            case "LIKE":
                if (value.ToString().Contains("_"))
                    sb.Append("@" + propertyName);
                else
                    sb.Append("'%' + @" + propertyName + " + '%'");
                _properties.Add(new QueryProperty { Name = propertyName, Value = value });
                break;
            case "BETWEEN":
                var range = value as Array;
                var betweenCount = 0;
                foreach (var r in range)
                {
                    if (betweenCount > 0)
                        sb.Append(" AND ");
                    sb.Append("@" + propertyName + betweenCount);
                    _properties.Add(new QueryProperty { Name = propertyName + betweenCount, Value = r });
                    betweenCount++;
                }
                break;
            default:
                sb.Append("@" + propertyName);
                _properties.Add(new QueryProperty { Name = propertyName, Value = value });
                break;
        }
        return sb.ToString();
    }

    public string ConvertToSql()
    {
        var filterOutput = this;
        var output = filterOutput.Properties.Aggregate(filterOutput.Sql, (current, p) => current.Replace("@" + p.Name, FormatObjectToString(p.Value)));
        output = output
            .Replace("[", "t.").Replace("]", "") // Convert [text] to t.text
            .Replace("{", "(").Replace("}", ")") // Convert {'text1','text2'} to ('text1','text2')
            .Replace("'%' + '", "'%").Replace("' + '%'", "%'"); // Convert '%' + text + '%' to '%text%'
        return " AND " + output;
    }

    public override string ToString()
    {
        var filterOutput = this;
        return filterOutput.Properties.Aggregate(filterOutput.Sql, (current, p) => current.Replace("@" + p.Name, FormatObjectToString(p.Value)).Replace("'%' + '", "'%").Replace("' + '%'", "%'"));
    }

    private string FormatObjectToString(object value)
    {
        if (value is int)
            return value.ToString();
        return String.Format("'{0}'", value);
    }
}

HomeController.cs

public ActionResult TestQuery(DateTime date)
{
    var builder = new QueryBuilder(_repo, "INFO", date);

    builder.AddFilters(
            new FilterField
                {
                    PropertyName = "State",
                    Operator = "=",
                    Value = "CA"
                },
            new FilterField
                {
                    PropertyName = "Schedule_Num",
                    Operator = "IN",
                    Value = new[] {2, 6}
                });

    var result = builder.Build();
    return View(result);
 }

 [HttpPost]
 public ActionResult TestPost(QueryResult result)
 {
    var builder = new QueryBuilder(_repo, "INFO", date);

    foreach (var f in result.Filters)
    {
        builder.AddFilters(new FilterField
                                   {
                                       PropertyName = f.PropertyName,
                                       Operator = f.Operator,
                                       Value = ((Array)f.Value).GetValue(0)
                                   });
    }

    builder.AddFilters(
            new FilterField
                {
                    PropertyName = "Gender",
                    Operator = "BETWEEN",
                    Value = new[] {"A", "G"}
                });

    var newResult = builder.Build();
    return View("TestQuery", newResult);
 }

TestQuery.cshtml

@model Models.QueryResult

@using (Html.BeginForm("TestPost", "Home"))
{
    @Html.HiddenFor(m => m.Date)
    for (var i = 0; i < Model.Filters.Count(); i++)
    {
        @Html.Hidden("filters[" + i + "].PropertyName", Model.Filters[i].PropertyName)
        @Html.Hidden("filters[" + i + "].Operator", Model.Filters[i].Operator)
        @Html.Hidden("filters[" + i + "].Value", Model.Filters[i].Value)
    }
    <div class="formArea">
        <p>
            <input type="submit" value="Submit" id="btnSubmit" />
        </p>
    </div>
}

QueryResult.cs

public class QueryResult
{
    public DateTime Date { get; set; }
    public ObjectQuery<EntityObject> Objects { get; set; }
    public string SqlStatement { get; set; }
    public ObjectParameter[] Parameters { get; set; }
    public AdjustResult AdjustResult { get; set; }
    public IList<FilterField> Filters { get; set; }

    public QueryResult()
    {
        Filters = new List<FilterField>();
    }

    public void AddFilter(FilterField filter)
    {
        Filters.Add(filter);
    }

    public string ParsedSqlStatement()
    {
        var output = Parameters.Aggregate(SqlStatement, (current, p) => current.Replace("@" + p.Name, FormatObjectToString(p.Value)));
        return Filters.Aggregate(output, (current, filter) => current + filter.ConvertToSql());
    }

    private string FormatObjectToString(object value)
    {
        if (value is int)
            return value.ToString();
        return String.Format("'{0}'", value);
    }
}

QueryBuilder.cs

public class QueryBuilder
{
    public IList<FilterField> Filters { get; set; }

    private IDynamicRepository _repo;
    private string _tablePrefix;
    private DateTime _date;
    private QueryResult _base;

    public QueryBuilder(IDynamicRepository repository, string tablePrefix, DateTime date)
    {
        _repo = repository;
        _tablePrefix = tablePrefix;
        _date = date;
        _base = _repo.GetAll(tablePrefix, date);
        Filters = new List<FilterField>();
    }

    public void AddFilters(params FilterField[] filters)
    {
        foreach (var f in filters)
        {
           Filters.Add(f);
        }
    }

    public void RemoveFilter(FilterField filter)
    {
        Filters.Remove(filter);
    }

    public QueryResult Build()
    {
        return _base.Where(Filters.ToArray());
    }
}

Extensions.cs

public static QueryResult Where(this QueryResult result, string predicate, params QueryProperty[] properties)
{
    result.Objects = result.Objects.Where(predicate.ReplaceIdentifier(), properties.Select(p => new ObjectParameter(p.Name, p.Value)).ToArray());
    return result;
}

public static QueryResult Where(this QueryResult result, FilterField filter)
{
    var filterOutput = filter.GetOutput();
    result.Objects = result.Objects.Where(filterOutput.Sql.ReplaceIdentifier(), filterOutput.Properties.Select(p => new ObjectParameter(p.Name, p.Value)).ToArray());
    result.AddFilter(filter);
    return result;
}

public static QueryResult Where(this QueryResult result, params FilterField[] filters)
{
    return filters.Aggregate(result, Where);
}

Since some of you want to know more information, here are some more details of how everything ties together. Basically, the controller gets a list of filters from the UI that boil down to SQL statements after the WHERE. So one filter would turn into FIELD = VALUE or FIELD IN (VALUE1, VALUE2). The querybuilder creates the base of the SQL statement using Entity.CreateQuery("SELECT * FROM TABLE"). Once the the method Build() on querybuilder is ran, it creates a QueryResult model that has the date of the query and all of the EntityObjects of the query with the attached filters turned into WHERE statements for use with the View. I went ahead and added some more classes to show how those tie together.

Solution 1

In your view, you don't need to be setting the name like that, as you can use HiddenFor to do that for you. Change your for loop in your View to be:

for (var i = 0; i < Model.Filters.Count(); i++)
{
    @Html.HiddenFor(m => m.Filters[i].PropertyName)
    @Html.HiddenFor(m => m.Filters[i].Operator)
    @Html.HiddenFor(m => m.Filters[i].Value)
}

That should give you the correct markup, which in turn should help the default model binder send the Filters to your QueryResult in the HttpPost method :).

**EDIT: As you're binding multiple values or single values (int or string, either could be an Array), you're best of changing the Value property in the FilterField class to be a List<string>.

So, in the FilterField class replace this:

public object Value { get; set; }

With this:

public List<string> Values { get; set; }

Then change your markup to be:

for (var i = 0; i < Model.Filters.Count(); i++)
{
    @Html.HiddenFor(m => m.Filters[i].PropertyName)
    @Html.HiddenFor(m => m.Filters[i].Operator)
    for (var j = 0; j < Model.Filters[i].Values.Count; j++)
    {
        @Html.HiddenFor(m => m.Filters[i].Values[j])
    }
}

That way, although it may not look like prettiest piece of code in the world, it saves you the headache of having to hack away at the model binding to work how you want it, it'll just bind by default.

Solution 2

I think you could adapt the idea from this article.

Each item in your case will be FilterItems instead of GiftItems...

Comments

[snoluet]

Unfortunately I get the same results when I try it this way. But thanks for the shorthand of writing it.

[brightgarden]

I'm not saying this is the most elegant solution. But changing the type of "Value" from object to an array of object might make the simpler Html.HiddenFor(...) work as expected. But I don't have 100% faith in this observation.

[mattytommo]

@snoulet No problems :). Out of interest though how come your Value is an object? That's the thing causing you major headaches. Is it going to vary greatly between types or just going to be either a string or an int?

[snoluet]

It's going to be either a string, an int, an array of strings, or an array of ints. Because the "IN" clause can have multiple values, I have to be able to bring in an array of values to accommodate that.

[mattytommo]

Okay that's fair enough, would it be fair to assume that if we used strings they could just be manipulated to ints on the server if need be? (I ask because it'll make your life a LOT easier)

[snoluet]

You mean bring in a string of say "1,6,8" and then split and convert to ints on the server? Or do you mean bring in an array of string values instead of an array of int values? I guess anything is possible, I was just trying to have the most elegant code in the controller and it made more sense to have an array of the value type you wanted to check for.

[mattytommo]

@snoulet Not exactly, check my edit. If you use List<string> for your values, you can use that for one or many values and if they're of type int you can just parse them and do whatever you like with them (would you even need to?) That's far nicer than hacking away at model binding in my opinion, which is your only other alternative.

[snoluet]

That worked. It's not the most elegant and it's hard to distinguish if the user does in fact want an integer or string value to compare, but it works. Thanks again for your help. It keeps me out of messing with the model binder and for that I am gracious.

[snoluet]

This doesn't answer the original question of getting an array into an object, but this does help my next task greatly. Thank you for the input. I was just about to start working on the UI portion and I will use this guide to help me do that.

[mattytommo]

@snoluet No problems, I agree it's not the most elegant solution, but to write a custom model binder to deal with what essentially is dynamic in type would have been nothing short of a nightmare :)

[Romias]

Well, nice to be helpful... I thought that allowing your users to dynamically add filter options could be nice!