QueryExpressionBuilder 1.0.0

QueryExpressionBuilder

Library for generating predicate functions for filtering database queries.

Description

This library helps create a predicate function for filtering database queries based on a model. Link on GitHub

Instruction

To use the library, you need to create a model with properties for filtering. Properties in the model need to be marked with attributes.

Currently, there are 3 attributes:<br>

Namespace QueryExpressionBuilder.Attributes.String - attributes for properties of type String

StartWithAttribute - Indicates that a filter equivalent to the System.String.StartWith() function will be used for the property.

Namespace QueryExpressionBuilder.Attributes.Numbers - attribute for all properties that are numeric, including DateTime

GreaterOrEqualAttribute - Indicates that a filter equivalent to the >= conditional expression will be used for the property.

LessOrEqualAttribute - Indicates that a filter equivalent to the ⇐ conditional expression will be used for the property.

You need to pass the property name from the class representing the entity in the database to the attribute constructor.

Examples

Suppose we have an entity User, which is an entity in the database.

    public class User : IEntity
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Surname { get; set; }
        public string Email { get; set; }
        public DateTime BirthDate { get; set; }
        public DateTime RegistrationDate { get; set; }
        public string PasswodHash { get; set; }
        public float Amount { get; set; }
    }

To create a Query filter, we need to create a query model.

    public class UserQuery
    {
        [QueryExpressionBuilder.Attributes.String.StartWith("Name")]
        public string? Name { get; set; }

        [QueryExpressionBuilder.Attributes.String.StartWith("Surname")]
        public string? Surname { get; set; }

        [QueryExpressionBuilder.Attributes.String.StartWith("Email")]
        public string? Email { get; set; }

        [QueryExpressionBuilder.Attributes.Numbers.GreaterOrEqual("BirthDate")]
        public DateTime? FromBirthDate { get; set; }

        [QueryExpressionBuilder.Attributes.Numbers.LessOrEqual("BirthDate")]
        public DateTime? ToBirthDate { get; set; }

        [QueryExpressionBuilder.Attributes.Numbers.GreaterOrEqual("RegistrationDate")]
        public DateTime? FromRegistrationDate { get; set; }

        [QueryExpressionBuilder.Attributes.Numbers.LessOrEqual("RegistrationDate")]
        public DateTime? ToRegistrationDate { get; set; }

        [QueryExpressionBuilder.Attributes.Numbers.GreaterOrEqual("Amount")]
        public float? FromAmount { get; set; }

        [QueryExpressionBuilder.Attributes.Numbers.LessOrEqual("Amount")]
        public float? ToAmount { get; set; }
    }

And now, in the controller method, which represents the endpoint, simply pass the UserQuery object to the ExpressionBuilder class, which will return the predicate function.

    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {
        private readonly ILogger<WeatherForecastController> _logger;
        private readonly IUserService userService;

        public WeatherForecastController(ILogger<WeatherForecastController> logger, IUserService _userService)
        {
            userService = _userService;
            _logger = logger;
        }

        /// <summary>
        /// Method for get users
        /// </summary>
        /// <param name="queryParams">Object containing filtering parameters</param>
        /// <returns>Returns a list of users</returns>
        [HttpGet("[controller]/GetUsers")]
        public async Task<IEnumerable<User>> GetUsers([FromQuery] UserQuery queryParams)
        {
            //Generating a predicate based on parameters
            var predicate = ExpressionBuilder.GetPredicate<User, UserQuery>(queryParams);
            //We pass the predicate to the service and return the result
            var result = await userService.GetUsers(predicate);
            return result;
        }
    }

Now, when sending a request to the database<br> https://localhost:7001/GetUsers?Name=M&FromBirthDate=2000-01-01&ToBirthDate=2040-01-01&FromAmount=1000<br> We get an SQL query.

SELECT "u"."Id", "u"."Amount", "u"."BirthDate", "u"."Email", "u"."Name", "u"."PasswodHash", "u"."RegistrationDate", "u"."Surname"
      FROM "Users" AS "u"
      WHERE instr("u"."Name", 'M') > 0 AND "u"."BirthDate" >= '2000-01-01 00:00:00' AND "u"."BirthDate" <= '2040-01-01 00:00:00' AND "u"."Amount" >= 1000

License

This project is distributed under the MIT license, which allows free use, modification, and distribution of the code in accordance with the terms of the MIT license.