使用Angular 6 Ag-grid以及ASP.NET和EF Core 2.1进行服务器端过滤 [英] Server-side filtering with Angular 6 ag-grid and ASP.NET and EF Core 2.1
问题描述
我正在尝试在ag-grid(无限滚动模式)中实现服务器端过滤.
I am trying to implement server-side filtering in ag-grid (infinite scrolling mode).
问题是-关于filterModel的文档非常晦涩，我正在慢慢地使用console.log发现东西，因为filterModel可以提供不同的信息，这令人沮丧，这也使得映射到服务器端类非常繁琐.有没有人找到关于filterModel的适当文档?
Problem is - documentation about filterModel is very obscure and I am slowly discovering things using console.log which is getting frustrating because of different information filterModel can provide which also makes mapping to server side classes very tedious. Has anyone found proper documentation about filterModel?
还有，没有人找到ASP.NET Core和EF Core的助手方法来应用此filterModel吗?似乎需要做很多工作才能涵盖所有可能的情况，而我目前的方法需要System.DynamicLinq(不确定这是否是最佳解决方案).
Also, has anyone found helper methods for ASP.NET Core and EF Core to apply this filterModel? It seems like A LOT of work to cover every possible scenario and my current approach requires System.DynamicLinq (not sure if this is optimal solution).
谢谢，马里奥(Mario)
Thanks, Mario
推荐答案
我已经对此进行了排序，所以如果有人需要它，就在这里.
I got this sorted, so if anyone needs it, here it is.
无限行模型需要我在onGridReady事件中定义的数据源，如下所示:
Infinite row model requires data source which I defined in onGridReady event like this:
const dataSource = {
rowCount: null,
getRows: (params) => {
this.svc.GetDrivingData(params.startRow, params.endRow, params.sortModel, params.filterModel)
.subscribe((d) => {
// console.log(JSON.stringify(d, null, 4));
params.successCallback(d, null);
});
}
};
然后GetDrivingData调用Web Api:
Then GetDrivingData calls Web Api:
GetDrivingData(startRow: number, endRow: number,
sortModel: any, filterModel: any): Observable<DrivingData[]>
{
const body = {
startRow,
endRow,
sortModel,
filterModel
};
return this.httpClient.post<DrivingData[]>(`${this.baseUrl}/api/carfleet/DrivingDataPocoLo/GetDrivingData`, body);
}
最后，在服务器端需要对filterModel和sortModel进行一些处理.以下代码根本没有优化，它是filterModel不同值的演示.例如，如果您在ag-grid中选择第二个逻辑运算符，则JSON会更改，并包含带有logicOperator参数的condition1和condition2对象.该代码可能包含错误，因为我没有测试所有可能的组合.此外，该代码使用System.DynamicLinq.
Finally, on server side it takes some processing of filterModel and sortModel. Following code is not optimised at all, it is demonstration of different values of filterModel. For instance, if you select second logic operator in ag-grid, JSON changes and includes condition1 and condition2 objects with logicOperator parameter. This code could contain bugs because I did not test all possible combinations. Also, the code uses System.DynamicLinq.
[HttpPost("[action]")]
public IActionResult GetDrivingData([FromBody] GridOperationsModel gom)
{
var query = ctx.DrivingData.AsQueryable();
Func<string, FilterModel, List<object>, string> getConditionFromModel =
(string colName, FilterModel model, List<object> values) =>
{
string modelResult = "";
switch (model.filterType)
{
case "text":
switch (model.type)
{
case "equals":
modelResult = $"{colName} = \"{model.filter}\"";
break;
case "notEqual":
modelResult = $"{colName} = \"{model.filter}\"";
break;
case "contains":
modelResult = $"{colName}.Contains(@{values.Count})";
values.Add(model.filter);
break;
case "notContains":
modelResult = $"!{colName}.Contains(@{values.Count})";
values.Add(model.filter);
break;
case "startsWith":
modelResult = $"{colName}.StartsWith(@{values.Count})";
values.Add(model.filter);
break;
case "endsWith":
modelResult = $"!{colName}.StartsWith(@{values.Count})";
values.Add(model.filter);
break;
}
break;
case "number":
switch (model.type)
{
case "equals":
modelResult = $"{colName} = {model.filter}";
break;
case "notEqual":
modelResult = $"{colName} <> {model.filter}";
break;
case "lessThan":
modelResult = $"{colName} < {model.filter}";
break;
case "lessThanOrEqual":
modelResult = $"{colName} <= {model.filter}";
break;
case "greaterThan":
modelResult = $"{colName} > {model.filter}";
break;
case "greaterThanOrEqual":
modelResult = $"{colName} >= {model.filter}";
break;
case "inRange":
modelResult = $"({colName} >= {model.filter} AND {colName} <= {model.filterTo})";
break;
}
break;
case "date":
values.Add(model.dateFrom);
switch (model.type)
{
case "equals":
modelResult = $"{colName} = @{values.Count - 1}";
break;
case "notEqual":
modelResult = $"{colName} <> @{values.Count - 1}";
break;
case "lessThan":
modelResult = $"{colName} < @{values.Count - 1}";
break;
case "lessThanOrEqual":
modelResult = $"{colName} <= @{values.Count - 1}";
break;
case "greaterThan":
modelResult = $"{colName} > @{values.Count - 1}";
break;
case "greaterThanOrEqual":
modelResult = $"{colName} >= @{values.Count - 1}";
break;
case "inRange":
values.Add(model.dateTo);
modelResult = $"({colName} >= @{values.Count - 2} AND {colName} <= @{values.Count - 1})";
break;
}
break;
}
return modelResult;
};
foreach (var f in gom.filterModel)
{
string condition, tmp;
List<object> conditionValues = new List<object>();
if (!string.IsNullOrWhiteSpace(f.Value.logicOperator))
{
tmp = getConditionFromModel(f.Key, f.Value.condition1, conditionValues);
condition = tmp;
tmp = getConditionFromModel(f.Key, f.Value.condition2, conditionValues);
condition = $"{condition} {f.Value.logicOperator} {tmp}";
}
else
{
tmp = getConditionFromModel(f.Key, f.Value, conditionValues);
condition = tmp;
}
if (conditionValues.Count == 0) query = query.Where(condition);
else query = query.Where(condition, conditionValues.ToArray());
}
foreach (var s in gom.sortModel)
{
switch (s.sort)
{
case "asc":
query = query.OrderBy(s.colId);
break;
case "desc":
query = query.OrderBy($"{s.colId} descending");
break;
};
};
if (gom.sortModel.Count() == 0)
{
query = query.OrderBy(x => x.Oid);
}
query = query
.Include(dd => dd.CarNavigation)
.Include(dd => dd.DriverNavigation)
.Skip(gom.startRow)
.Take(gom.endRow - gom.startRow);
var result = query
.AsNoTracking()
.ToArray();
return Ok(result);
}
这篇关于使用Angular 6 Ag-grid以及ASP.NET和EF Core 2.1进行服务器端过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
