我有一个查询语句列表，当需要在末尾添加另一个语句时，需要将这些查询语句发回 MVC 控制器。对于我现在尝试创建的测试，页面以过滤器列表开头。执行此示例时，页面将为过滤器创建字段，如下所示：
<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">
但是当我看到表单实际发回控制器时，列表返回为：
PropertyName = "State"
Operator = "="
Value= "new string[1]" // The value comes back in the first index of the array
我必须将值参数转换为数组并采用第一个索引才能获取值。这还可以，但并不理想。当 FilterField 包含 Value 属性的整数或字符串数组时，主要问题就出现了。当这种情况发生时，HTML 会显示为：
<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[]">
然后，该值包含对象类型，但根本不包含任何值。所以模型绑定器会变得混乱并且一切都会崩溃。有没有更简单的方法将此值列表绑定到视图？
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);
}
}
过滤器输出.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);
}
测试查询.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>
}
查询结果.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);
}
}
查询生成器.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());
}
}
扩展.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);
}
由于有些人想了解更多信息，这里有一些有关所有内容如何联系在一起的更多详细信息。基本上，控制器从 UI 获取过滤器列表，这些过滤器可归结为 WHERE 之后的 SQL 语句。因此，一个过滤器将变成 FIELD = VALUE 或 FIELD IN (VALUE1, VALUE2)。查询生成器使用 Entity.CreateQuery("SELECT * FROM TABLE") 创建 SQL 语句的基础。一旦运行了 querybuilder 上的 Build() 方法，它就会创建一个 QueryResult 模型，其中包含查询的日期以及带有附加过滤器的查询的所有 EntityObjects，这些实体对象将转换为与视图一起使用的 WHERE 语句。我继续添加了更多的类来展示它们是如何联系在一起的。
