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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

In my asp.net project, how can I utilize dynamic LINQ?

Well, I'll first say that dynamic LINQ is ...usually... overkill, but sometimes useful. But you should just be able to reference the dll and away you go. If no intellisense is appearing, but it ...compiles..., then check where (physically) the dynamic linq dll is located. Ensure the "foo.dll.xml" file is located next to "foo.dll" (where "foo" is th...
.net c# dynamic-linq linq visual-studio
asked by Marc Gravell

Does dynamic GroupBy in dynamic LINQ work with dynamic Where?

Found it, ...db.Table1.AsQueryable()... fixed it, so ...db.Table1.AsQueryable().Where("it.FieldA>10").GroupBy("it.FieldB", "it").Select("key") ... works without exception....The problem was that Microsoft Dynamic Linq library extends ...IQueryable<Table1>..., so it does not take effect on... ObjectQuery<Table1>... which is the type of ...db.Table1....
.net dynamic dynamic-linq linq linq-to-entities
asked by alpav

LINQ-to-SQL dynamic table name LINQ-to-SQL

Looks like ...this... is the link you need. ...Update.... Some details: you can use the SearchType method from the post to obtain a type using the table name and then use ...Dynamic LINQ... to write queries you like.
dynamic-linq linq-to-sql
asked by Devart

Perform a query on an object having members of type "dynamic" using Dynamic Linq.

Are ...dynamic... members incompatible with Dynamic Linq queries?, or is there another way of constructing expressions that would evaluate properly when dealing with members of type ...dynamic...?...Both can work together. Just do a conversion to Int32 before doing the comparison like so:...IEnumerable<DataItem> result = repository.AsQu...
c# dynamic-linq expression-trees lambda
asked by afrischke

In ASP.NET 4.0, troubleshoot a dynamic linq query.

In case if your CLients is a list, can you try... Clients.AsQueryable().Where ("EmailReturnUndeliverable = false Or ClientBadStanding_TF = false") ...If I am not mistaken DynamicLibrary.cs has functions which can we applied to IQueryable
dynamic-linq linq
asked by Avani Vadera

When utilizing the Dynamic Expressions API, you may validate the action parameters of an ASP.NET MVC 3 controller.

I'm not very familiar with Dymaic LINQ but you can do the following:...var customerSummary = CustomerManager.CustomerRepository.GetQuery(); if ("desc".Equals(sortDir, StringComparison.CurrentCultureIgnoreCase)) customerSummary = customerSummary.OrderByDescending(sort); else customerSummary = customerSummary.OrderBy(sort); var pageNumber = p...
asp.net-mvc-3 c# dynamic-linq
asked by ILya

Dynamic Linq/ Dynamic Query: cannot get data for jqGrid

OK, found the solution here:...Strange Exception thrown using Dynamic Linq Entity Framework Query...This works:...var s = context.testdata.OrderBy("it.id asc");
asp.net-mvc dynamic-linq entity-framework jqgrid linq
asked by Oliver Kötter

How to construct dynamic LINQ query string be for a query that looks like *abc*def*

It looks like what you need is ...IndexOf.... This returns the starting index of the substring, if it exists, otherwise -1....var.Contains("abc") && var.Contains("def") && var.IndexOf("abc") > var.IndexOf("def") ...For example, if we use the string ...XabcXdefXabc..., ...var.IndexOf("abc")... will yield ...1... and ...var.IndexOf("def")... will...
c# dynamic-linq linq regex
asked by gnalck

Cast ITable to IEnuerable<object> - Dynamic LINQ to SQL Query with no DataTable information

Short Answer...NO....Long Answer...ITable... has this signature:...public interface ITable : IQueryable, IEnumerable ...If you have an instance of a type which implements that, like below:...public class MyTable : System.Data.Linq.ITable { // Implementation ... } ...And you do this:...var myTable = new MyTable(); var iterator = myTable as IEnum...
c# dynamic-linq linq linq-to-sql
asked by CodingYoshi

Dynamic library (System.Linq.Dynamic) SQL LIKE Operator

Since there is no LIKE operator in Dynamic Linq library, I have created it my own version. The code is not pretty, but it does work. Hopefully someone can optimize it or suggest a better way to do this....public string ParseWildcardInParameterString(string parameter, string propertyName) { string stringWithWildcard = parameter; if (paramete...
c# dynamic-linq linq sql
asked by Kevin Man

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

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