commit 5e9102bf6a46763768e47b8454037413dfeddac7
Author: novickii.sergei.nure@gmail.com <novickii.sergei.nure@gmail.com>
Date: Fri, 26 Apr 2024 18:43:04 +0300
Init
Diffstat:
7 files changed, 376 insertions(+), 0 deletions(-)
diff --git a/.gitignore b/.gitignore
@@ -0,0 +1,4 @@
+.vs/*
+QueryExpressionBuilder/bin/*
+QueryExpressionBuilder/obj/*
+QueryExpressionBuilder/Properties/*
+\ No newline at end of file
diff --git a/QueryExpressionBuilder.sln b/QueryExpressionBuilder.sln
@@ -0,0 +1,25 @@
+
+Microsoft Visual Studio Solution File, Format Version 12.00
+# Visual Studio Version 17
+VisualStudioVersion = 17.9.34511.98
+MinimumVisualStudioVersion = 10.0.40219.1
+Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "QueryExpressionBuilder", "QueryExpressionBuilder\QueryExpressionBuilder.csproj", "{C6B925B3-E450-4830-9B40-305102A14309}"
+EndProject
+Global
+ GlobalSection(SolutionConfigurationPlatforms) = preSolution
+ Debug|Any CPU = Debug|Any CPU
+ Release|Any CPU = Release|Any CPU
+ EndGlobalSection
+ GlobalSection(ProjectConfigurationPlatforms) = postSolution
+ {C6B925B3-E450-4830-9B40-305102A14309}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+ {C6B925B3-E450-4830-9B40-305102A14309}.Debug|Any CPU.Build.0 = Debug|Any CPU
+ {C6B925B3-E450-4830-9B40-305102A14309}.Release|Any CPU.ActiveCfg = Release|Any CPU
+ {C6B925B3-E450-4830-9B40-305102A14309}.Release|Any CPU.Build.0 = Release|Any CPU
+ EndGlobalSection
+ GlobalSection(SolutionProperties) = preSolution
+ HideSolutionNode = FALSE
+ EndGlobalSection
+ GlobalSection(ExtensibilityGlobals) = postSolution
+ SolutionGuid = {7E174301-9FE3-4552-830F-D0AEB96012A0}
+ EndGlobalSection
+EndGlobal
diff --git a/QueryExpressionBuilder/Attributes.cs b/QueryExpressionBuilder/Attributes.cs
@@ -0,0 +1,72 @@
+namespace QueryExpressionBuilder.Attributes
+{
+ /// <summary>
+ /// Attribute to filtration strings
+ /// </summary>
+ public class String
+ {
+
+ /// <summary>
+ /// Filtration func StartWith
+ /// </summary>
+ [AttributeUsage(AttributeTargets.Property)]
+ public class StartWithAttribute : BasePredicateAttribute
+ {
+ /// <summary>
+ /// Add attribute func StartWith
+ /// </summary>
+ /// <param name="propertyName">Name of property in DB</param>
+ public StartWithAttribute(string propertyName) : base(propertyName)
+ {
+ }
+ }
+ }
+
+ /// <summary>
+ /// Attribute to filtration numbers and DateTime
+ /// </summary>
+ public class Numbers
+ {
+ /// <summary>
+ /// Filtration func >=
+ /// </summary>
+ [AttributeUsage(AttributeTargets.Property)]
+ public class GreaterOrEqualAttribute : BasePredicateAttribute
+ {
+ /// <summary>
+ /// Add attribute func >=
+ /// </summary>
+ public GreaterOrEqualAttribute(string propertyName) : base(propertyName)
+ {
+ }
+ }
+
+ /// <summary>
+ /// Filtration func <=
+ /// </summary>
+ [AttributeUsage(AttributeTargets.Property)]
+ public class LessOrEqualAttribute : BasePredicateAttribute
+ {
+ /// <summary>
+ /// Add attribute func <=
+ /// </summary>
+ public LessOrEqualAttribute(string propertyName) : base(propertyName)
+ {
+ }
+ }
+ }
+
+ /// <summary>
+ /// Base filtration class
+ /// </summary>
+ [AttributeUsage(AttributeTargets.Property)]
+ public class BasePredicateAttribute : Attribute
+ {
+ public string PropertyName { get; set; }
+
+ public BasePredicateAttribute(string propertyName)
+ {
+ PropertyName = propertyName;
+ }
+ }
+}
diff --git a/QueryExpressionBuilder/ExpressionBuilder.cs b/QueryExpressionBuilder/ExpressionBuilder.cs
@@ -0,0 +1,143 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Linq.Expressions;
+using System.Reflection;
+using System.Text;
+using System.Threading.Tasks;
+using static QueryExpressionBuilder.Attributes.Numbers;
+using static QueryExpressionBuilder.Attributes.String;
+
+namespace QueryExpressionBuilder
+{
+ /// <summary>
+ /// Class converter from Query model with attributes to predicate func
+ /// </summary>
+ public static class ExpressionBuilder
+ {
+ /// <summary>
+ /// Extension method
+ /// </summary>
+ /// <typeparam name="TDB">Type in DB</typeparam>
+ /// <typeparam name="TQE">Type of Query object</typeparam>
+ /// <param name="queryParams">Dictionary</param>
+ /// <returns>Predicate func</returns>
+ public static Expression<Func<TDB, bool>>? ToPredicate<TDB, TQE>(this Dictionary<string, string> queryParams)
+ {
+ return GetPredicateFromDictionary<TDB, TQE>(queryParams);
+ }
+
+ /// <summary>
+ /// Make predicate from dictionary
+ /// </summary>
+ /// <typeparam name="TDB">Type in DB</typeparam>
+ /// <typeparam name="TQE">Type of Query object</typeparam>
+ /// <param name="queryParams">Dictionary</param>
+ /// <returns>Predicate func</returns>
+ public static Expression<Func<TDB, bool>>? GetPredicateFromDictionary<TDB, TQE>(Dictionary<string, string> queryParams)
+ {
+ var query = GetQueryObject<TQE>(queryParams);
+ return GetPredicate<TDB, TQE>(query);
+ }
+
+ /// <summary>
+ /// Make Query instance
+ /// </summary>
+ /// <typeparam name="T">Type of Query</typeparam>
+ /// <param name="queryParams">Dictionary</param>
+ /// <returns>Instance</returns>
+ public static T GetQueryObject<T>(Dictionary<string, string> queryParams)
+ {
+ Type type = typeof(T);
+ T queryObject = Activator.CreateInstance<T>();
+
+ foreach (var property in type.GetProperties())
+ {
+ if (queryParams.ContainsKey(property.Name))
+ {
+ object value;
+ Type propertyType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
+
+ if (propertyType == typeof(string) && string.IsNullOrEmpty(queryParams[property.Name]))
+ {
+ value = null;
+ }
+ else
+ {
+ value = Convert.ChangeType(queryParams[property.Name], propertyType);
+ }
+
+ property.SetValue(queryObject, value);
+ }
+ }
+
+ return queryObject;
+ }
+
+ /// <summary>
+ /// Make new predicate func
+ /// </summary>
+ /// <typeparam name="TDB">Type in DB</typeparam>
+ /// <typeparam name="TQE">Query type</typeparam>
+ /// <returns>Predicate func</returns>
+ public static Expression<Func<TDB, bool>>? GetPredicate<TDB, TQE>(TQE query)
+ {
+ var userParameter = Expression.Parameter(typeof(TDB), typeof(TDB).Name.ToLower());
+ List<Expression> conditions = new List<Expression>();
+
+ //Только те свойства которые помечены атрибутами
+ var T_pr_props = typeof(TQE).GetProperties().Where(prop => prop.GetCustomAttribute<LessOrEqualAttribute>() != null ||
+ prop.GetCustomAttribute<GreaterOrEqualAttribute>() != null || prop.GetCustomAttribute<StartWithAttribute>() != null).ToArray();
+
+ //Проходимся по всем параметрам класса БД
+ foreach (var p in typeof(TDB).GetProperties())
+ {
+ var yslovia = T_pr_props.Where(x => x.Name.Contains(p.Name));
+ foreach (var y in yslovia)
+ {
+ if (y.GetCustomAttribute<StartWithAttribute>() != null)
+ {
+ var propertyY = Expression.Property(userParameter, y.Name);
+ var propertyP = Expression.Property(userParameter, p.Name);
+ var containsCall = Expression.Call(propertyP, "Contains", null, Expression.Constant(y.GetValue(query), typeof(string)));
+ var obj = y.GetValue(query);
+ if (obj != null)
+ {
+ conditions.Add(containsCall);
+ }
+ }
+ else if (y.GetCustomAttribute<GreaterOrEqualAttribute>() != null)
+ {
+ var propertyName = y.GetCustomAttribute<GreaterOrEqualAttribute>().PropertyName;
+ var propertyY = Expression.Property(userParameter, propertyName);
+ var obj = y.GetValue(query);
+ if (obj != null)
+ {
+ var value = Convert.ChangeType(obj, p.PropertyType);
+ var constantValue = Expression.Constant(value, p.PropertyType);
+ var condition = Expression.GreaterThanOrEqual(propertyY, constantValue);
+ conditions.Add(condition);
+ }
+ }
+ else if (y.GetCustomAttribute<LessOrEqualAttribute>() != null)
+ {
+ var propertyName = y.GetCustomAttribute<LessOrEqualAttribute>().PropertyName;
+ var propertyY = Expression.Property(userParameter, propertyName);
+ var obj = y.GetValue(query);
+ if (obj != null)
+ {
+ var value = Convert.ChangeType(obj, p.PropertyType);
+ var constantValue = Expression.Constant(value, p.PropertyType);
+ var condition = Expression.LessThanOrEqual(propertyY, constantValue);
+ conditions.Add(condition);
+ }
+ }
+ }
+ }
+
+ var body = conditions.Aggregate(Expression.AndAlso);
+ var predicate = Expression.Lambda<Func<TDB, bool>>(body, userParameter);
+ return predicate;
+ }
+ }
+}
diff --git a/QueryExpressionBuilder/QueryExpressionBuilder.csproj b/QueryExpressionBuilder/QueryExpressionBuilder.csproj
@@ -0,0 +1,9 @@
+<Project Sdk="Microsoft.NET.Sdk">
+
+ <PropertyGroup>
+ <TargetFramework>net8.0</TargetFramework>
+ <ImplicitUsings>enable</ImplicitUsings>
+ <Nullable>enable</Nullable>
+ </PropertyGroup>
+
+</Project>
diff --git a/QueryExpressionBuilder/QueryExpressionBuilder.csproj.user b/QueryExpressionBuilder/QueryExpressionBuilder.csproj.user
@@ -0,0 +1,6 @@
+<?xml version="1.0" encoding="utf-8"?>
+<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
+ <PropertyGroup>
+ <_LastSelectedProfileId>P:\MyEcoSpace\Libraries\Helpers\QueryExpressionBuilder\QueryExpressionBuilder\Properties\PublishProfiles\FolderProfile.pubxml</_LastSelectedProfileId>
+ </PropertyGroup>
+</Project>
+\ No newline at end of file
diff --git a/README.md b/README.md
@@ -0,0 +1,114 @@
+##QueryExpressionBuilder
+Библиотека для генирации функций-предикатов для фильтрирования запросов в БД.
+
+#Описание
+Данная библиотека помогает создать функцию-предикат для фильтрирования запросов в БД на основе модели.
+
+#Инструкция
+Для работы библиотеки необходимо создать модель с свойствами для фильтрации.<br>
+Свойства в модели необходимо пометить атрибутами.<br><br>
+
+На данный момент существует 3 атрибута:<br><br>
+
+Пространство имен QueryExpressionBuilder.Attributes.String - атрибуты для свойств типа String<br>
+StartWithAttribute - Означает, что дял свойства будет использоватся фильтр с аналогом функции System.String.StartWith()<br><br>
+
+Пространство имен QueryExpressionBuilder.Attributes.Numbers - атрибут для всех свойств которые ввляются числовыми, в том числе DateTime<br>
+GreaterOrEqualAttribute - Означает, что для свойства будет использоваться фильтр с аналогом условного выражения >=<br>
+LessOrEqualAttribute - Означает, что для свойства будет использоваться фильтр с аналогом условного выражения <=<br>
+
+В конструктор атрибута необходимо передать название свойства из класса представляющий сущьность в БД.<br><br>
+
+#Примеры
+Предположим у нас есть сущьность User, которая являеться сущьностью в БД.
+```csharp
+ 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 int Age { get; set; }
+ public float Amount { get; set; }
+ }
+```
+Для создания Query фильтра нам необходимо создать query-модель
+```csharp
+ 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("Age")]
+ public int? FromAge { get; set; }
+
+ [QueryExpressionBuilder.Attributes.Numbers.LessOrEqual("Age")]
+ public int? ToAge { get; set; }
+
+ [QueryExpressionBuilder.Attributes.Numbers.GreaterOrEqual("Amount")]
+ public float? FromAmount { get; set; }
+
+ [QueryExpressionBuilder.Attributes.Numbers.LessOrEqual("Amount")]
+ public float? ToAmount { get; set; }
+ }
+```
+И теперь в методе контроллера, который представляет конечную точку, нужно просто передать обьект UserQuery в класс ExpressionBuilder, который вернет функцию-предикат.
+```csharp
+ [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>
+ /// Метод получения юзеров
+ /// </summary>
+ /// <param name="queryParams">Объект содержащий параметры фильтрации</param>
+ /// <returns>Возвращает список юзеров</returns>
+ [HttpGet("[controller]/GetUsers")]
+ public async Task<IEnumerable<User>> GetUsers([FromQuery] UserQuery queryParams)
+ {
+ //Генерируем предикат на основе параметров
+ var predicate = ExpressionBuilder.GetPredicate<User, UserQuery>(queryParams);
+ //Передаем предикат в сервис и возвращаем результат
+ var result = await userService.GetUsers(predicate);
+ return result;
+ }
+ }
+```
+Теперь отправляя запрос
+https://localhost:7001/GetUsers?Name=S&FromBirthDate=2010-01-01&ToBirthDate=2040-01-01&FromAge=20
+в БД мы получаес SQL запрос
+```sql
+SELECT "u"."Id", "u"."Age", "u"."Amount", "u"."BirthDate", "u"."Email", "u"."Name", "u"."PasswodHash", "u"."RegistrationDate", "u"."Surname"
+ FROM "Users" AS "u"
+ WHERE instr("u"."Name", 'S') > 0 AND "u"."BirthDate" >= '2010-01-01 00:00:00' AND "u"."BirthDate" <= '2040-01-01 00:00:00' AND "u"."Age" >= 20
+```
+\ No newline at end of file
