Purlin.PData.Search 2.1.4

Dynamic sql query generator from condition model

PData is giving functionality for creating predicate expression from specified condition models for using EF Core. It also has paging functionality with ordering by given property name and direction.

Definition

public enum Operator
    {
        /// <summary>
        /// Equal operator
        /// </summary>
        Eq,
        /// <summary>
        /// Greater than operator
        /// </summary>
        Gt,
        /// <summary>
        /// Greater than or equal operator
        /// </summary>
        Ge,
        /// <summary>
        /// Less than operator
        /// </summary>
        Lt,
        /// <summary>
        /// Less than or equal operator
        /// </summary>
        Le,
        /// <summary>
        /// In Operator
        /// </summary>
        In,
        /// <summary>
        /// Starts with function for string
        /// </summary>
        Sw,
        /// <summary>
        /// Ends with function for string
        /// </summary>
        Ew,
        /// <summary>
        /// Contains function for string
        /// </summary>
        Like,
        /// <summary>
        /// Null check operator
        /// </summary>
        IsNull,
        /// <summary>
        /// Any operator for collections
        /// </summary>
        Any,
        /// <summary>
        /// All operator for collections
        /// </summary>
        All
    }

    public enum Condition
    {
        /// <summary>
        /// And condition
        /// </summary>
        And,
        /// <summary>
        /// Or condition
        /// </summary>
        Or
    }

     public class ConditionModel
    {
        /// <summary>
        /// Gets or sets the operator
        /// </summary>
        public virtual Operator Operator { get; set; }
        /// <summary>
        /// Gets or sets the value to be filtered
        /// </summary>
        public virtual string Value { get; set; }
        /// <summary>
        /// Gets or sets the property name for filtering
        /// </summary>
        public virtual string Property { get; set; }
        /// <summary>
        /// Gets or sets the another property name for filtering instead of using constant value
        /// </summary>
        public virtual string NextProperty { get; set; }
        /// <summary>
        /// Gets or sets the negate flag
        /// </summary>
        public virtual bool IsNot { get; set; }
        /// <summary>
        /// Gets or sets the sub conditions
        /// </summary>
        public virtual List<ConditionModel> SubConditions { get; } = new();
        /// <summary>
        /// Gets or sets the condition to be combined with sub conditions
        /// </summary>
        public virtual Condition? SubCondition { get; set; }
        /// <summary>
        /// Gets or sets the condition to be combined with next same level conditions
        /// </summary>
        public virtual Condition? NextCondition { get; set; }
    }

   public class OrderingModel
    {
        /// <summary>
        /// Gets or sets ordering property
        /// </summary>
        public string Property { get; set; }
        /// <summary>
        /// Gets or sets ordering asc/desc direction
        /// </summary>
        public bool IsDescending { get; set; }
    }

    public class Query
    {
        public bool Count { get; set; }
        public string Filter { get; set; }
        public List<ConditionModel> Conditions { get; set; }
        public List<OrderingModel> Orderings { get; set; }
        public int? Skip { get; set; }
        public int? Take { get; set; }
        public List<string> Fields { get; set; } = new();
    }

Register

Add this line of code somewhere you are registering DbContext

 ...
 services.AddDbContextPool<AppDbContext>(o => o.UseSqlServer(ConnectionString).UsePData());

This will read all configurations you've made in your context class when configuring entities.

Usage

Assume we have this three classes in our DbContext class

public class Order
{   
    public long Id { get; set; }
    public decimal Price { get; set; }
    public int PersonId { get; set; }
   
    public Person Person { get; set; }
}

public class Country
{   
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<Person> Persons { get; set; }
}

public class Person
{
    public int Id { get; set; }
    public int Age { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
     
    public int CountryId { get; set; }
    public Country Country { get; set; }
    public ICollection<Order> Orders { get; set; }
}

Inject or create instance of your context class somewhere you want.

public class PersonManager
{
    public async Task<Person> GetPersons()
    {
            var query = new Query
            {
                Count = true
                Skip = 10,
                Take = 5,
            };

            using var context = new AppContext();
            var persons = context.Persons.ApplyQuery(query, out var totalCountTask);
            var totalCount = await totalCountTask;
            var page = query.ToListAsync();
    }
}

This query will be translated to sql as shown where @__p_0 is 0 and @__p_1 is 10.

      SELECT [p].[Id], [p].[Age], [p].[Name], [p].[Surname]
      FROM [Persons] AS [p]
      ORDER BY (SELECT 1)
      OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

Be careful await the totalCountTask before starting any other query on database to avoid from "A second operation started on this context before a previous operation completed" InvalidOperationException. Please use ApplyOptions after all 'where' statements because total count may be changed because of adding new condition.

In case of when you want also order the data use Orderings.

    public async Task<Person> GetPersons()
    {
            var query = new Query
            {
                Count = true
                Skip = 10,
                Take = 5,
                Orderings = new List<OrderingModel>{ new OrderingModel { Property = "Id" , IsDecending = true}}
            };

            using var context = new AppContext();
            var persons = context.Persons.ApplyQuery(query, out var totalCountTask);
            var totalCount = await totalCountTask;
            var page = query.ToListAsync();
    }

This query will be translated to sql as

      SELECT [p].[Id], [p].[Age], [p].[Name], [p].[Surname]
      FROM [Persons] AS [p]
      ORDER BY [p].[Id] DESC
      OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

In case of when you want also filter the data add condition models or use more user friendly syntax.

    public async Task<Person> GetPersons()
    {
            var query = new Query
            {
                Count = true
                Skip = 10,
                Take = 5,
                Filter = "Age eq \"15\"", //Filter is equivalnt to Conditions = new List<ConditionModel>{ new ConditionModel { Property = "Age", Operator = Operator.Eq, Value = "15"}}
                Orderings = new List<OrderingModel>{ new OrderingModel { Property = "Id" , IsDecending = true}}
            };

            using var context = new AppContext();
            var persons = context.Persons.ApplyQuery(query, out var totalCountTask);
            var totalCount = await totalCountTask;
            var page = query.ToListAsync();
    }

This query will be translated to sql as shown where @__Parse_0 is 15.

           SELECT [p].[Id], [p].[Age], [p].[Name], [p].[Surname]
           FROM [Persons] AS [p]
           WHERE [p].[Age] = @__Parse_0
           ORDER BY [p].[Id] DESC
           OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

Property can be used with '.' (dots) for example "Country.Name" this will join related table and order or filter data by related table column.

For using two properties in expression there is NextProperty in ConditionModel that has higher priority "Value" will be ignored.

Example for multiple conditions combined by and/or operators and parenthesis` Assume there is condition such as

a eq \"10\" and b eq \"5\" and (c eq \"1\" or d eq \"2\") and (e eq \"2\" or (f eq \"1\" and (g eq \"10\" or h eq \"22\") and (p eq \"9\" or q eq \"7\"))) and m not in [\"a\",\"b\"]

this is equivalent to this json array which items is conditionModels

[
  {
    "Operator": 0,
    "Value": "10",
    "Property": "a",
    "IsNot": false,
    "SubConditions": [],
    "SubCondition": null,
    "NextCondition": 0
  },
  {
    "Operator": 0,
    "Value": "5",
    "Property": "b",
    "IsNot": false,
    "SubConditions": [],
    "SubCondition": null,
    "NextCondition": 0
  },
  {
    "Operator": 0,
    "Value": "1",
    "Property": "c",
    "IsNot": false,
    "SubConditions": [
      {
        "Operator": 0,
        "Value": "2",
        "Property": "d",
        "IsNot": false,
        "SubConditions": [],
        "SubCondition": null,
        "NextCondition": null
      }
    ],
    "SubCondition": 1,
    "NextCondition": 0
  },
  {
    "Operator": 0,
    "Value": "2",
    "Property": "e",
    "IsNot": false,
    "SubConditions": [
      {
        "Operator": 0,
        "Value": "1",
        "Property": "f",
        "IsNot": false,
        "SubConditions": [
          {
            "Operator": 0,
            "Value": "10",
            "Property": "g",
            "IsNot": false,
            "SubConditions": [
              {
                "Operator": 0,
                "Value": "22",
                "Property": "h",
                "IsNot": false,
                "SubConditions": [],
                "SubCondition": null,
                "NextCondition": null
              }
            ],
            "SubCondition": 1,
            "NextCondition": 0
          },
          {
            "Operator": 0,
            "Value": "9",
            "Property": "p",
            "IsNot": false,
            "SubConditions": [
              {
                "Operator": 0,
                "Value": "7",
                "Property": "q",
                "IsNot": false,
                "SubConditions": [],
                "SubCondition": null,
                "NextCondition": null
              }
            ],
            "SubCondition": 1,
            "NextCondition": null
          }
        ],
        "SubCondition": 0,
        "NextCondition": null
      }
    ],
    "SubCondition": 1,
    "NextCondition": 0
  },
  {
    "Operator": 5,
    "Value": "[\"a\",\"b\"]",
    "Property": "m",
    "IsNot": true,
    "SubConditions": [],
    "SubCondition": null,
    "NextCondition": null
  }
]

If we will use 'a gt b' instead of 'a gt "b"', 'b' will assumed as another property of that type and will must have same type which has 'a'

  {
    "Operator": 1,
    "Value": null,
    "Property": "a",
    "NextProperty": "b",
    "IsNot": false,
    "SubConditions": [],
    "SubCondition": null,
    "NextCondition": null
  }

Any and All operators have special syntax, assume we need filter persons that have any order price above 1000. In this case we have to specify 'value' as condition such as

  {
    "Operator": 0,
    "Value": "Price gt \"1000\"",
    "Property": "Orders",
    "IsNot": false,
    "SubConditions": [],
    "SubCondition": null,
    "NextCondition": null
  }

IsNull operator doesn't require any value. Value part must be skipped in filter - Name isNull/ Name not isNull for string usage or json below for model usage.

  {
    "Operator": 9,
    "Value": null ,
    "Property": "Name",
    "IsNot": false,
    "SubConditions": [],
    "SubCondition": null,
    "NextCondition": null
  }