Querying dynamic in asp.NET with SQL server

Question

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.

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?

A. Meheus

View more on Stack Overflow

Sercan TimoÃ§in · Accepted Answer · 3/8/2016 9:50:40 AM

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

       }
       .
       .
  }

Querying dynamic in asp.NET with SQL server

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator