Pitasoft.DataAccess 1.0.1

Pitasoft.DataAccess

English | Castellano

English

---

Pitasoft.DataAccess (English)

Extensions library for dynamic filtering, sorting, and paging over IQueryable<T>, compatible with Entity Framework Core and any LINQ provider.

---

Packages

Package	Purpose
Pitasoft.DataAccess	Implementation of IQueryable extensions (ApplyFilter, ApplySort, ToResultPaged, etc.). Depends on Pitasoft.DataAccess.Abstractions and System.Linq.Dynamic.Core.
Pitasoft.DataAccess.Abstractions	Interfaces (IFilterSpecification) and builders (FilterBuilder). No dependencies on EF Core or dynamic LINQ. Ideal for Domain/Service layers.

---

Installation

# Main package (includes Abstractions)
dotnet add package Pitasoft.DataAccess

# Only abstractions (from NuGet)
dotnet add package Pitasoft.DataAccess.Abstractions

Namespace

using Pitasoft.DataAccess;
using Pitasoft.DataAccess.Abstractions.Helpers; // For FilterBuilder

---

ApplyFilter — Dynamic Filtering

Basic Syntax

property.operation(value)

Multiple filters separated by comma are equivalent to an implicit AND:

Name.contains(laptop),Price.le(500)

Available Operations

Without value

Operation	Description
property.empty	Null or empty
property.notempty	Not null nor empty
property.null	Is null
property.notnull	Is NOT null
property.nil	No filter (ignored)

With value

Operation	Description
property.eq(value)	Equal to
property.ne(value)	Not equal to
property.gt(value)	Greater than
property.ge(value)	Greater than or equal to
property.lt(value)	Less than
property.le(value)	Less than or equal to
property.contains(value)	Contains text
property.notcontains(value)	Does not contain text
property.starts(value)	Starts with
property.ends(value)	Ends with
property.in(v1;v2;v3)	Equal to any (separated by ;)
property.between(min;max)	Within range [min, max]

Logical Operators (URL-friendly syntax)

// AND
query.ApplyFilter<Product>("Name.contains(Pro).and.Price.gt(100)");

// OR
query.ApplyFilter<Product>("Price.gt(1000).or.Price.lt(30)");

// NOT
query.ApplyFilter<Product>("not.Name.contains(Pro)");

// Grouping with parentheses
query.ApplyFilter<Product>("(Price.lt(100).or.Price.gt(1000)).and.Name.contains(Pro)");

// NOT on group
query.ApplyFilter<Product>("not.(Price.lt(100).or.Price.gt(1000))");

Properties Whitelist and Conditions Limit

// Only allows filtering by Name and Price; maximum 3 conditions
query.ApplyFilter<Product>(
    "Name.contains(Pro).and.Price.gt(100)",
    allowedProperties: ["Name", "Price"],
    maxConditions: 3);

---

ApplySort — Dynamic Sorting

// Ascending
query.ApplySort<Product>("asc(Price)");

// Descending
query.ApplySort<Product>("desc(Price)");

// Multiple fields
query.ApplySort<Product>("asc(Status),desc(Price)");

---

ApplyPaging — Paging

// Page 2, 10 items per page
var paged = query.ApplyPaging(page: 2, pageSize: 10);

---

ToResultPaged — Paging with Metadata

// Returns Pitasoft.Result.ResultPaged<T>
var result = query.ToResultPaged(page: 1, pageSize: 10);

Console.WriteLine(result.TotalCount);    // Total items count
Console.WriteLine(result.TotalPages);    // Total pages count
Console.WriteLine(result.HasNextPage);   // Is there a next page?
Console.WriteLine(result.HasPreviousPage); // Is there a previous page?

foreach (var item in result.Items) { ... }

---

ApplySelect — Dynamic Projection

Useful for REST APIs with ?fields=Id,Name,Price parameter:

IQueryable projected = query.ApplySelect<Product>("Id,Name,Price");

---

ApplySearch — Global Search

Searches for a term across multiple text properties (implicit OR):

// Searches for "laptop" in Name or Description
query.ApplySearch<Product>("laptop", "Name", "Description");

---

IFilterSpecification — Specification Pattern

Encapsulates reusable filters as objects:

public class ActiveProductsSpec : IFilterSpecification<Product>
{
    public string ToFilterString() => "Status.eq(Active).and.Price.gt(0)";
}

// Usage:
var result = query.ApplyFilter(new ActiveProductsSpec()).ToList();

---

FilterBuilder — Fluent API

Builds filter strings in a chained and type-safe manner:

// Simple filter
var filter = FilterBuilder<Product>.Create()
    .Where(p => p.Name).Contains("Pro")
    .And()
    .Where(p => p.Price).GreaterThan(100)
    .Build();
// => "Name.contains(Pro).and.Price.gt(100)"

// With inline OrGroup
var filter = FilterBuilder<Product>.Create()
    .Where(p => p.Status).EqualTo(Status.Active)
    .AndGroup(g => g
        .Where(p => p.Price).LessThan(100)
        .Or()
        .Where(p => p.Price).GreaterThan(1000))
    .Build();
// => "Status.eq(Active).and.(Price.lt(100).or.Price.gt(1000))"

// With negation
var filter = FilterBuilder<Product>.Create()
    .Not()
    .Group(g => g
        .Where(p => p.Price).LessThan(100)
        .Or()
        .Where(p => p.Price).GreaterThan(1000))
    .Build();
// => "not.(Price.lt(100).or.Price.gt(1000))"

// Apply directly on IQueryable
var result = FilterBuilder<Product>.Create()
    .Where(p => p.Name).Contains("Pro")
    .Apply(dbContext.Products)
    .ToList();

Clone and Merge

Reuse base filters and combine them without modifying the original:

var baseFilter = FilterBuilder<Product>.Create()
    .Where(p => p.Status).EqualTo(Status.Active);

// Clone and extend
var expensiveFilter = baseFilter.Clone()
    .And()
    .Where(p => p.Price).GreaterThan(500);

// Merge with AND
var combined = baseFilter.Merge(expensiveFilter);

// Merge with OR
var either = baseFilter.MergeOr(expensiveFilter);

Available Operators in PropertyFilterBuilder

Method	Generated Filter
.EqualTo(v)	prop.eq(v)
.NotEqualTo(v)	prop.ne(v)
.GreaterThan(v)	prop.gt(v)
.GreaterOrEqualTo(v)	prop.ge(v)
.LessThan(v)	prop.lt(v)
.LessOrEqualTo(v)	prop.le(v)
.Contains(v)	prop.contains(v)
.NotContains(v)	prop.notcontains(v)
.StartsWith(v)	prop.starts(v)
.EndsWith(v)	prop.ends(v)
.In(v1, v2, ...)	prop.in(v1;v2;...)
.Between(min, max)	prop.between(min;max)
.IsNull()	prop.null
.IsNotNull()	prop.notnull
.IsEmpty()	prop.empty
.IsNotEmpty()	prop.notempty
.Nil()	prop.nil

---

ASP.NET Core Usage (query string)

A common way to use the library in an API is to define a class for the query parameters:

public record ProductQuery(
    [FromQuery] string? Filter,
    [FromQuery] string? Sort,
    [FromQuery] int Page = 1,
    [FromQuery] int PageSize = 10);

[HttpGet]
public IActionResult GetProducts([FromQuery] ProductQuery query)
{
    var result = _dbContext.Products
        .ApplyFilter<Product>(query.Filter, allowedProperties: ["Name", "Price", "Status"], maxConditions: 5)
        .ApplySort<Product>(query.Sort)
        .ToResultPaged(query.Page, query.PageSize);

    return Ok(result);
}

Example URL: GET /api/products?filter=Name.contains(Pro).and.Price.gt(100)&sort=desc(Price)&page=1&pageSize=10

---

Supported Types in Automatic Conversion

bool, sbyte, char, byte, short, int, long, ushort, uint, ulong, float, double, decimal, DateOnly, DateTime, DateTimeOffset, TimeOnly, TimeSpan, Guid, Enum (and their nullable T? versions).

---

License

MIT

---

Contributing

1. Fork the repository.
2. Create a new branch (git checkout -b feature/your-feature).
3. Commit your changes (git commit -m 'Add some feature').
4. Push to the branch (git push origin feature/your-feature).
5. Open a Pull Request.

---

Castellano

Pitasoft.DataAccess (Castellano)

Librería de extensiones para filtrado, ordenamiento y paginación dinámica sobre IQueryable<T>, compatible con Entity Framework Core y cualquier proveedor LINQ.

---

Paquetes

Paquete	Propósito
Pitasoft.DataAccess	Implementación de las extensiones de IQueryable (ApplyFilter, ApplySort, ToResultPaged, etc.). Depende de Pitasoft.DataAccess.Abstractions y System.Linq.Dynamic.Core.
Pitasoft.DataAccess.Abstractions	Interfaces (IFilterSpecification) y builders (FilterBuilder). Sin dependencias de EF Core o LINQ dinámico. Ideal para capas de Dominio/Servicios.

---

Instalación

# Paquete principal (incluye Abstractions)
dotnet add package Pitasoft.DataAccess

# Solo abstracciones (desde NuGet)
dotnet add package Pitasoft.DataAccess.Abstractions

Namespace

using Pitasoft.DataAccess;
using Pitasoft.DataAccess.Abstractions.Helpers; // Para FilterBuilder

---

ApplyFilter — Filtrado dinámico

Sintaxis básica

propiedad.operacion(valor)

Múltiples filtros separados por coma equivalen a AND implícito:

Name.contains(laptop),Price.le(500)

Operaciones disponibles

Sin valor

Operación	Descripción
propiedad.empty	Nula o vacía
propiedad.notempty	No nula ni vacía
propiedad.null	Es null
propiedad.notnull	No es null
propiedad.nil	Sin filtro (ignorado)

Con valor

Operación	Descripción
propiedad.eq(valor)	Igual a
propiedad.ne(valor)	Distinto de
propiedad.gt(valor)	Mayor que
propiedad.ge(valor)	Mayor o igual que
propiedad.lt(valor)	Menor que
propiedad.le(valor)	Menor o igual que
propiedad.contains(valor)	Contiene el texto
propiedad.notcontains(valor)	No contiene el texto
propiedad.starts(valor)	Empieza con
propiedad.ends(valor)	Termina con
propiedad.in(v1;v2;v3)	Igual a cualquiera (separados por ;)
propiedad.between(min;max)	En el rango [min, max]

Operadores lógicos (sintaxis URL-friendly)

// AND
query.ApplyFilter<Product>("Name.contains(Pro).and.Price.gt(100)");

// OR
query.ApplyFilter<Product>("Price.gt(1000).or.Price.lt(30)");

// NOT
query.ApplyFilter<Product>("not.Name.contains(Pro)");

// Agrupación con paréntesis
query.ApplyFilter<Product>("(Price.lt(100).or.Price.gt(1000)).and.Name.contains(Pro)");

// NOT sobre grupo
query.ApplyFilter<Product>("not.(Price.lt(100).or.Price.gt(1000))");

Whitelist de propiedades y límite de condiciones

// Solo permite filtrar por Name y Price; máximo 3 condiciones
query.ApplyFilter<Product>(
    "Name.contains(Pro).and.Price.gt(100)",
    allowedProperties: ["Name", "Price"],
    maxConditions: 3);

---

ApplySort — Ordenamiento dinámico

// Ascendente
query.ApplySort<Product>("asc(Price)");

// Descendente
query.ApplySort<Product>("desc(Price)");

// Múltiples campos
query.ApplySort<Product>("asc(Status),desc(Price)");

---

ApplyPaging — Paginación

// Página 2, 10 elementos por página
var paged = query.ApplyPaging(page: 2, pageSize: 10);

---

ToResultPaged — Paginación con metadatos

// Devuelve Pitasoft.Result.ResultPaged<T>
var result = query.ToResultPaged(page: 1, pageSize: 10);

Console.WriteLine(result.TotalCount);    // Total de elementos
Console.WriteLine(result.TotalPages);    // Total de páginas
Console.WriteLine(result.HasNextPage);   // ¿Hay página siguiente?
Console.WriteLine(result.HasPreviousPage); // ¿Hay página anterior?

foreach (var item in result.Items) { ... }

---

ApplySelect — Proyección dinámica

Útil para APIs REST con parámetro ?fields=Id,Name,Price:

IQueryable projected = query.ApplySelect<Product>("Id,Name,Price");

---

ApplySearch — Búsqueda global

Busca un término en varias propiedades de texto (OR implícito):

// Busca "laptop" en Name o Description
query.ApplySearch<Product>("laptop", "Name", "Description");

---

IFilterSpecification — Patrón Specification

Encapsula filtros reutilizables como objetos:

public class ActiveProductsSpec : IFilterSpecification<Product>
{
    public string ToFilterString() => "Status.eq(Active).and.Price.gt(0)";
}

// Uso:
var result = query.ApplyFilter(new ActiveProductsSpec()).ToList();

---

FilterBuilder — API fluida

Construye cadenas de filtro de forma encadenada y type-safe:

// Filtro simple
var filter = FilterBuilder<Product>.Create()
    .Where(p => p.Name).Contains("Pro")
    .And()
    .Where(p => p.Price).GreaterThan(100)
    .Build();
// => "Name.contains(Pro).and.Price.gt(100)"

// Con OrGroup inline
var filter = FilterBuilder<Product>.Create()
    .Where(p => p.Status).EqualTo(Status.Active)
    .AndGroup(g => g
        .Where(p => p.Price).LessThan(100)
        .Or()
        .Where(p => p.Price).GreaterThan(1000))
    .Build();
// => "Status.eq(Active).and.(Price.lt(100).or.Price.gt(1000))"

// Con negación
var filter = FilterBuilder<Product>.Create()
    .Not()
    .Group(g => g
        .Where(p => p.Price).LessThan(100)
        .Or()
        .Where(p => p.Price).GreaterThan(1000))
    .Build();
// => "not.(Price.lt(100).or.Price.gt(1000))"

// Aplicar directamente sobre IQueryable
var result = FilterBuilder<Product>.Create()
    .Where(p => p.Name).Contains("Pro")
    .Apply(dbContext.Products)
    .ToList();

Clone y Merge

Reutiliza filtros base y combínalos sin modificar el original:

var baseFilter = FilterBuilder<Product>.Create()
    .Where(p => p.Status).EqualTo(Status.Active);

// Clonar y extender
var expensiveFilter = baseFilter.Clone()
    .And()
    .Where(p => p.Price).GreaterThan(500);

// Merge con AND
var combined = baseFilter.Merge(expensiveFilter);

// Merge con OR
var either = baseFilter.MergeOr(expensiveFilter);

Operadores disponibles en PropertyFilterBuilder

Método	Filtro generado
.EqualTo(v)	prop.eq(v)
.NotEqualTo(v)	prop.ne(v)
.GreaterThan(v)	prop.gt(v)
.GreaterOrEqualTo(v)	prop.ge(v)
.LessThan(v)	prop.lt(v)
.LessOrEqualTo(v)	prop.le(v)
.Contains(v)	prop.contains(v)
.NotContains(v)	prop.notcontains(v)
.StartsWith(v)	prop.starts(v)
.EndsWith(v)	prop.ends(v)
.In(v1, v2, ...)	prop.in(v1;v2;...)
.Between(min, max)	prop.between(min;max)
.IsNull()	prop.null
.IsNotNull()	prop.notnull
.IsEmpty()	prop.empty
.IsNotEmpty()	prop.notempty
.Nil()	prop.nil

---

Uso en ASP.NET Core (query string)

Una forma común de utilizar la librería en una API es definir una clase para los parámetros de consulta:

public record ProductQuery(
    [FromQuery] string? Filter,
    [FromQuery] string? Sort,
    [FromQuery] int Page = 1,
    [FromQuery] int PageSize = 10);

[HttpGet]
public IActionResult GetProducts([FromQuery] ProductQuery query)
{
    var result = _dbContext.Products
        .ApplyFilter<Product>(query.Filter, allowedProperties: ["Name", "Price", "Status"], maxConditions: 5)
        .ApplySort<Product>(query.Sort)
        .ToResultPaged(query.Page, query.PageSize);

    return Ok(result);
}

Ejemplo de URL: GET /api/products?filter=Name.contains(Pro).and.Price.gt(100)&sort=desc(Price)&page=1&pageSize=10

---

Tipos soportados en conversión automática

bool, sbyte, char, byte, short, int, long, ushort, uint, ulong, float, double, decimal, DateOnly, DateTime, DateTimeOffset, TimeOnly, TimeSpan, Guid, Enum (y sus versiones nullable T?).

---

Licencia

MIT

---

Contribuir

1. Haz un fork del repositorio.
2. Crea una nueva rama (git checkout -b feature/nueva-funcionalidad).
3. Confirma tus cambios (git commit -m 'Añadir nueva funcionalidad').
4. Empuja a la rama (git push origin feature/nueva-funcionalidad).
5. Abre un Pull Request.

---