Querying dynamic in asp.NET with SQL server

asp.net-mvc dynamic-linq json linq

Question

(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. enter image description here

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?

1
1
3/8/2016 8:08:19 AM

Accepted Answer

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);
           }

       }
       .
       .
  }
1
3/8/2016 9:50:40 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow