本项目采用EF作为数据访问框架，根据EF支持lamda表达式的特点扩展出来的查询方法
我相信大家很熟悉下面的代码：
if (user.Name != null)
query.Where(x => x.Name == user.Name);
if (user.Age != null)
query.Where(x => x.Age == user.Age);
判断参数是否为空构造的查询方法，这种方式也是一种很好的查询方法，但是缺点就是太不灵活，如果查询参数一旦很多，这种方式将写一长串，下面我介绍一种利用MVC的特性扩展出来的查询方法。
先来看一段代码：
[FilterAction]
public ActionResult ReplyDataList(GridAction filterAction, int newId)
{
filterAction.filters.Add(new FilterField() { propertyName = "NewId", propertyType = "equal", propertyValue = newId });
var data = _mysqlSearchService.Search<FishReplyEntity>(filterAction).ToList();
var total = _searchService.Count<FishNewsEntity>(filterAction).Count();
Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("total", total);
dic.Add("rows", data);
return Json(dic);
}
解释一下,本项目后台使用的EasyUI框架，所以一些固定参数都是根据EasyUI框架来的， FilterAction 是一个属性，集成自MVC的 ActionFilterAttribute属性，看到这里大家应该明白了，就是在访问MVC方法之前干点什么事，来看看FilterAction方法里有什么
public class FilterActionAttribute:ActionFilterAttribute
{
public FilterActionAttribute()
{
filterParameterName = "filterAction";
}
public string filterParameterName { get; set; }
public override void OnActionExecuting(ActionExecutingContext filterContext)
{
var parameters = filterContext.ActionParameters;
bool found = false;
if (parameters.ContainsKey(filterParameterName))
found = true;
if (found)
{
GridAction action = new GridAction();
action.pageIndex = filterContext.Controller.ValueOf<int>("page");
action.pageSize = filterContext.Controller.ValueOf<int>("rows");
action.orderName = filterContext.Controller.ValueOf<string>("sort");
action.orderType = filterContext.Controller.ValueOf<string>("order");
action.filters = FilterFactory.Create(filterContext.Controller.ValueOf<string>("filter"));
filterContext.ActionParameters[filterParameterName] = action;
}
base.OnActionExecuting(filterContext);
}
}
filterParameterName ：查询参数名称，用过EasyUI的一看就应该知道page、rows、sort等这些都是EasyUI查询方法需要用的参数，在这方法里用到了两个类FilterFactory和GridAction，
先来看看FilterFactory 类：
public class FilterFactory
{
public static List<FilterField> Create(string searchFilter)
{
List<FilterField> list = new List<FilterField>();
if (string.IsNullOrEmpty(searchFilter))
return list;
string[] propertys = searchFilter.Split('|');
if (propertys.Length > 0)
{
foreach (var item in propertys)
{
string[] fields = item.Split('~');
if (fields.Length != 4)
break;
if (fields.Length > 0)
{
//格式Id~equal~1~int|Name~contains~yu~string
FilterField filter = new FilterField();
filter.propertyName = fields[0];
filter.propertyType = fields[1];
filter.propertyValue = GetValue(fields[3], fields[2]);
// filter.Type = GetType(fields[3]);
list.Add(filter);
}
}
}
return list;
}
private static object GetValue(string type,string value)
{
object obj = null;
switch (type)
{
case "int":
int num;
int.TryParse(value, out num);
obj = num;
break;
case "string":
obj = value;
break;
case "datetime":
DateTime time;
DateTime.TryParse(value, out time);
obj = time;
break;
case "bool":
bool success;
Boolean.TryParse(value, out success);
obj = success;
break;
default:
obj = value;
break;
}
return obj;
}
}
这是一个把查询参数转换为特定的格式，以方便查询使用，再来看看GridAction类：
public class GridAction
{
public GridAction()
{
filters = new List<FilterField>();
}
public int pageIndex { get; set; }
public int pageSize { get; set; }
public string orderName { get; set; }
public string orderType { get; set; }
public List<FilterField> filters { get; set; }
/// <summary>
/// 多列排序 对应为 key=字段名 value=排序方式
/// </summary>
public Dictionary<string, string> dicOrder { get; set; }
}
很简单，就是保存一些使用到的参数，在这个类里又出现了个 FilterField类，千万不要认为麻烦，嵌套的类多，这都是必须的，仔细看品味一下代码，就晓得其实还是很好理解的，为了以后方便，现在麻烦点是可以接受的，来看看FilterField类：
public class FilterField
{
public string propertyName { get; set; }
public object propertyValue { get; set; }
public string propertyType { get; set; }
//查询类型值为：or,and，不填则默认为and
public string qureyType{ get; set; }
}
这个类就更简单了，就是保存一些值，以便后续需要，到这里查询所传递的参数告一段落。别急到了这里就已经完成一半了，参数格式化了总得有个查询方法吧。
看查询方法代码：
public class MySqlSearchService:IMySqlSearchService
{
public IQueryable<T> Search<T>(GridAction filterAction)
where T : class,new()
{
if (filterAction == null)
throw new ArgumentNullException("filterAction is null");
var repository = DependencyResolver.Current.GetService<IMySqlRepository<T>>();
if (repository == null)
throw new ArgumentNullException("repository is null");
//Repository<T> repository = new Repository<T>();
IQueryable<T> query = repository.Query.AsNoTracking();
if (filterAction.filters.Any())
{
var filter = filterAction.CommandFunc<T>();
if (filter != null)
query = query.Where(filter);
}
query = query.CommandOrder<T>(filterAction.orderName, filterAction.orderType);
if (filterAction.dicOrder != null)
{
foreach (var item in filterAction.dicOrder)
{
query = query.CommandThenOrder(item.Key, item.Value);
}
}
int skipNum = (filterAction.pageIndex - 1) * filterAction.pageSize;
return query.Skip(skipNum).Take(filterAction.pageSize);
}
public IQueryable<T> Count<T>(GridAction filterAction)
where T : class,new()
{
if (filterAction == null)
throw new ArgumentNullException("filterAction is null");
var repository = DependencyResolver.Current.GetService<IMySqlRepository<T>>();
if (repository == null)
throw new ArgumentNullException("repository is null");
IQueryable<T> query = repository.Query.AsNoTracking();
if (filterAction.filters.Any())
{
var filter = filterAction.CommandFunc<T>();
if (filter != null)
query = query.Where(filter);
}
return query;
}
public IQueryable<T> ExtensionSearch<T>(GridAction filterAction)
where T : class,new()
{
if (filterAction == null)
throw new ArgumentNullException("filterAction is null");
var repository = DependencyResolver.Current.GetService<IMySqlRepository<T>>();
if (repository == null)
throw new ArgumentNullException("repository is null");
IQueryable<T> query = repository.Query.AsNoTracking();
if (filterAction.filters.Any())
{
var filter = filterAction.CommandFunc<T>();
if (filter != null)
query = query.Where(filter);
}
query = query.CommandOrder<T>(filterAction.orderName, filterAction.orderType);
if (filterAction.dicOrder != null)
{
foreach (var item in filterAction.dicOrder)
{
query = query.CommandThenOrder(item.Key, item.Value);
}
}
return query;
}
}
利用泛型来做到通用，这里就不讲的那么细了，需要对表达式树有一定了解，简单介绍下三个查询方法：
Search<T>(GridAction filterAction) ：分页查询返回的数据
Count<T>(GridAction filterAction) ： 返回总条数
ExtensionSearch<T>(GridAction filterAction) ： 查询所有满足条件的数据
到这里扩展查询方法讲解告一段落，下面放一个实例代码，大家自行研究