(If the title isn't representative for this question please say so, I couldn't come up with a good short description)
I'm trying to implement an advanced search function for my web application. This search has to be able to search on a lot of possible combinations.
Like shown in the picture every group (AND or OR) can have another group or conditions that can be equal or not equal. The picture should result in a where clause that look like this:
WHERE (Application like '%User%' AND Host not like '%fjzhykjety%') OR (REMOTE_HOST like '%uykirlyy%' AND REMOTE_PORT not like '%55555%')
Application, Host and REMOTE_HOST are strings, REMOTE_PORT is a nullable int. I also have a GUID that has to be searchable.
At the moment this frontend search is written in angular and gives me following json (the json depends on the nested groups and conditions) for the example:
{
"group": {
"operator": "OR",
"rules": [
{
"group": {
"operator": "AND",
"rules": [
{
"condition": "=",
"field": {
"name": "Application",
"type": "select"
},
"data": "User"
},
{
"condition": "<>",
"field": {
"name": "Host",
"type": "text"
},
"data": "fjzhykjety"
}
]
}
},
{
"group": {
"operator": "AND",
"rules": [
{
"condition": "=",
"field": {
"name": "REMOTE_HOST",
"type": "text"
},
"data": "uykirlyy"
},
{
"condition": "<>",
"field": {
"name": "REMOTE_PORT",
"type": "number"
},
"data": 55555
}
]
}
}
]
}
}
I need a way to sent this data to my .NET application (using a REST API) and query it in the database. I looked into dynamic linq for making the query but because the format of the json file isn't always the same it's hard to make a class in C# for it and build a dynamic linq query for this search.
What is the best way to implement this advanced search?
Here is an example for dynamic query. Also you can combine with your query linqkit is good predicate builder.
http://www.albahari.com/nutshell/linqkit.aspx https://github.com/scottksmith95/LINQKit
public List<CustomerPointsDetail> GetCustomerPointsDetails(
int customerId,
int? catalogRewardId = null,
long? couponId = null,
long? transactionId = null,
CustomerPointsDetailStatus? inStatus = null,
CustomerPointsDetailStatus? notInStatus = null,
bool? isPointsGreaterThanZero = null,
bool? isRemainingPointsGreaterThanZero = null,
CustomerPointsDetailOperationType? operationType = null,
DateTime? startDate = null,
DateTime? endDate = null,
bool isExpiredRecordsIncluded = false)
{
var query = this.customerPointsDetailRepository.Table.Where(cpd => cpd.CustomerId == customerId);
if (catalogRewardId.HasValue)
{
query = query.Where(cpd => cpd.CatalogRewardId == catalogRewardId);
}
if (couponId.HasValue)
{
query = query.Where(cpd => cpd.CouponId == couponId);
}
if (transactionId.HasValue)
{
query = query.Where(cpd => cpd.TransactionId == transactionId);
}
if (inStatus.HasValue)
{
query = query.Where(cpd => cpd.Status == inStatus);
}
if (notInStatus.HasValue)
{
query = query.Where(cpd => cpd.Status != notInStatus);
}
if (isRemainingPointsGreaterThanZero.HasValue)
{
if (isRemainingPointsGreaterThanZero.GetValueOrDefault())
{
query = query.Where(cpd => cpd.RemainingPoints.HasValue && cpd.RemainingPoints > 0);
}
else
{
query = query.Where(cpd => cpd.RemainingPoints.HasValue && cpd.RemainingPoints < 0);
}
}
if (isPointsGreaterThanZero.HasValue)
{
if (isPointsGreaterThanZero.GetValueOrDefault())
{
query = query.Where(cpd => cpd.Points > 0);
}
else
{
query = query.Where(cpd => cpd.Points < 0);
}
}
if (operationType.HasValue)
{
query = query.Where(cpd => cpd.OperationType == operationType);
}
if (!isExpiredRecordsIncluded)
{
query = query.Where(cpd => !cpd.PointsExpireDate.HasValue
|| (cpd.PointsExpireDate.HasValue && cpd.PointsExpireDate > DateTime.Now));
}
if (startDate.HasValue)
{
query = query.Where(cpd => cpd.CreateDate >= startDate);
}
if (endDate.HasValue)
{
query = query.Where(cpd => cpd.CreateDate <= endDate);
}
query = query.OrderBy(cpd => cpd.PointsExpireDate);
return query.ToList();
}
Linqkit sample here
IQueryable<Product> SearchProducts (params string[] keywords)
{
var predicate = PredicateBuilder.False<Product>();
foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or (p => p.Description.Contains (temp));
}
return dataContext.Products.Where (predicate);
}
Edit
You need to an expression give your method and . You can combine with Linqkit.
Expression<Func<Product, bool>> e1 = DynamicExpression.ParseLambda<Product, bool>("Type= \"Type_A\"");
OR
Expression<Func<Product, bool>> e1 = p => p.Type="Type_A";
Before call you SearchProduct in Program
var expressions = new Dictionary<Expression<Func<Product, bool>>, bool>();
Expression<Func<Product, bool>> e1 = p => p.Type="Type_A";
Expression<Func<Product, bool>> e2 = p => p.Type="Type_B";
expressions.Add(e1,true);
expressions.Add(e2,true);
SearchProducts(expressions); //send expression list for evoluate
IQueryable<Product> SearchProducts (Dictionary<Expression<Func<Product, bool>>, bool> expressionList)
{
var predicate = PredicateBuilder.False<Product>();
foreach(expression in expressionList)
{
if(expression.Value) // AND
{
predicate = predicate.And(expression.Key);
}else // OR
{
predicate = predicate.Or(expression.Key);
}
}
.
.
}