使用 Linq to Sql OrderBy 进行分页 API 调用,并带有字符串参数和反射
本文关键字:字符串 反射 参数 调用 API Sql to Linq OrderBy 分页 使用 | 更新日期: 2023-09-27 18:36:22
我正在尝试推广API的分页调用。 如果我有一个从网页传递的过滤器,我希望能够根据页码和页面大小返回一系列项目。 如果 OrderBy 使用设置参数或可能为每个调用使用丑陋的 switch 语句,这很容易做到。
目前,我正在尝试使用表达式和反射(我没有经验)来扩展IQueryable,我找到了几个示例可以开始,如果我在根类中使用简单类型的属性(传递字符串"FixValidatedCount"),它可以工作。 如果我尝试按嵌套类属性排序,Island.Name 下面的示例,我无法让它工作。
有没有办法更新我的表达式以接受嵌套类/属性? 或者有更好的方法可以做到这一点吗?
[Route("api/issues/paged")]
[HttpPost]
public HttpResponseMessage GetIssuesPage(EntityPageFilter filter)
{
//THE BELOW COMMENTED OUT OBJECT IS THE PARAMETER
//var filter = new EntityPageFilter
//{
// PageSize = 5,
// PageNumber = 1,
// OrderBy = "Island.Name",
// OrderByAscending = true
//};
var query = _issueService.GetIssues()
.OrderByField(filter.OrderBy, filter.OrderByAscending)
//.OrderBy(i => i.Island.Name) THIS WORKS, BUT HOW DO I DO THIS WITH A STRING
.Skip(filter.Skip).Take(filter.PageSize);
return Request.CreateResponse(HttpStatusCode.OK, new PagedEntity<IssueSummary>
{
PageNumber = filter.PageNumber,
PageSize = filter.PageSize,
ItemCount = _issueService.GetIssues().Count(),
Data = query
});
}
问题摘要是查询的返回类型。
public class Issue
{
public int Id { get; set; }
public Island Island { get; set; }
public Type Type { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime? FixedAt { get; set; }
}
public class IssueSummary: Issue
{
public int FixCount { get; set; }
public int FixValidatedCount { get; set; }
}
public class Island
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
IQueryable的扩展方法
public static IQueryable<T> OrderByField<T>(this IQueryable<T> q, string sortField, bool ascending)
{
var properties = sortField.Split('.').ToList();
var xType = typeof(T);
for (var i = 0; i < properties.Count - 1; i++)
{
xType = xType.GetProperty(properties[i]).PropertyType;
}
var param = Expression.Parameter(xType, String.Empty);
var prop = Expression.Property(param, properties.Last());
var exp = Expression.Lambda(prop, param);
var method = ascending ? "OrderBy" : "OrderByDescending";
//var types = new[] { q.ElementType, exp.Body.Type };
var types = new[] { xType, exp.Body.Type };
var mce = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);
return q.Provider.CreateQuery<T>(mce);
}
获取具有嵌套支持的属性/字段访问器表达式的最简单方法是这样的:
var root = Expression.Parameter(typeof(T), "x");
var member = sortField.Split('.').Aggregate((Expression)root, Expression.PropertyOrField);
var selector = Expression.Lambda(member, root);
这是完整的扩展方法:
public static IQueryable<T> OrderByField<T>(this IQueryable<T> source, string sortField, bool ascending)
{
var root = Expression.Parameter(typeof(T), "x");
var member = sortField.Split('.').Aggregate((Expression)root, Expression.PropertyOrField);
var selector = Expression.Lambda(member, root);
var method = ascending ? "OrderBy" : "OrderByDescending";
var types = new[] { typeof(T), member.Type };
var mce = Expression.Call(typeof(Queryable), method, types,
source.Expression, Expression.Quote(selector));
return source.Provider.CreateQuery<T>(mce);
}