Pandatech.EFCoreQueryMagic
2.0.0
We have switch to Gridify which is much more mature. For easy migration we have Pandatech.GridifyExtensions.
dotnet add package Pandatech.EFCoreQueryMagic --version 2.0.0
NuGet\Install-Package Pandatech.EFCoreQueryMagic -Version 2.0.0
<PackageReference Include="Pandatech.EFCoreQueryMagic" Version="2.0.0" />
paket add Pandatech.EFCoreQueryMagic --version 2.0.0
#r "nuget: Pandatech.EFCoreQueryMagic, 2.0.0"
// Install Pandatech.EFCoreQueryMagic as a Cake Addin #addin nuget:?package=Pandatech.EFCoreQueryMagic&version=2.0.0 // Install Pandatech.EFCoreQueryMagic as a Cake Tool #tool nuget:?package=Pandatech.EFCoreQueryMagic&version=2.0.0
Pandatech.EFCoreQueryMagic
Overview
Pandatech.EFCoreQueryMagic
is a powerful .NET library designed to simplify dynamic filtering, ordering, and
aggregation
of data in Entity Framework Core. It provides a RESTful API experience similar to GraphQL, enabling front-end developers
to build complex data queries easily. This package is especially useful for applications with advanced filtering and
data presentation needs, such as those using the beautiful-react-table
for rich table functionalities.
Features
- Dynamic Filtering: Apply complex filters dynamically based on user input.
- Ordering: Easily sort data by multiple columns.
- Pagination: Efficiently paginate data sets to improve performance.
- Distinct Values: Retrieve distinct values for a column, useful for preparing filter options.
- Aggregation: Perform aggregate operations like sum, average, count, etc.
Getting Started
Installation
To install the Pandatech.EFCoreQueryMagic package, run the following command:
dotnet add package Pandatech.EFCoreQueryMagic
Note: Our NuGet package has 90% + test coverage.
Configuration
To make an entity filterable, you need to create a separate filter model class and map it to your entity class. Entity Example:
[FilterModel(typeof(UserEntityFilterModel))]
public class UserEntity
{
public long Id { get; set; }
public long CounterpartyId { get; set; }
public Guid? ProfilePictureId { get; set; }
public bool IsHuman { get; set; }
public UserStatusType Status { get; set; }
public bool ForceToChangePassword { get; set; }
public byte[] PasswordHash { get; set; } = null!;
public DateTime? PasswordExpirationDate { get; set; }
public byte[]? Phone { get; set; }
public bool PhoneIsVerified { get; set; }
public byte[]? Email { get; set; }
public bool EmailIsVerified { get; set; }
public string FirstName { get; set; } = null!;
public string LastName { get; set; } = null!;
public string? MiddleName { get; set; }
public byte[]? TotpSecret { get; set; }
public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
public DateTime UpdatedAt { get; set; } = DateTime.UtcNow;
}
Filter Model Example:
public class UserEntityFilterModel
{
[MappedToProperty(nameof(UserEntity.Id))]
public long Id { get; set; }
[MappedToProperty(nameof(UserEntity.CreatedAt))]
public DateTime CreationDate { get; set; }
[MappedToProperty(nameof(UserEntity.CounterpartyId), ConverterType = typeof(FilterPandaBaseConverter))]
public long CounterpartyId { get; set; }
[MappedToProperty(nameof(UserEntity.PasswordExpirationDate))]
public DateTime PasswordExpirationDate { get; set; }
[MappedToProperty(nameof(UserEntity.FirstName))]
[Order(1))]
public string FirstName { get; set; } = null!;
[Order(2)]
[MappedToProperty(nameof(UserEntity.LastName))]
public string LastName { get; set; } = null!;
[MappedToProperty(nameof(UserEntity.MiddleName))]
public string? MiddleName { get; set; }
[MappedToProperty(nameof(UserEntity.Phone), Encrypted = true)]
public string? PhoneNumber { get; set; }
[MappedToProperty(nameof(UserEntity.Email), Encrypted = true)]
public string? EmailAddress { get; set; }
}
Usage
1. Retrieve Available Filters
public async Task<List<FilterInfo>> GetFiltersAsync()
{
var tableName = $"{typeof(UserEntityFilterModel).Name}";
return await FilterExtenders.GetFiltersAsync(typeof(UserEntity).Assembly, tableName);
}
2. Apply Filters and Ordering
public Task<List<UserEntity>> GetUsersAsync(string filterQuery)
{
return _dbContext
.Users
.FilterAndOrder(filterQuery)
.ToListAsync();
}
3. Apply Filters, Ordering, and Pagination
public Task<PagedResponse<UserEntity>> GetUsersAsync(this IQueryable<UserEntity> query, PageQueryRequest pageQueryRequest, CancellationToken cancellationToken = default)
{
return _dbContext
.Users
.FilterOrderPaginateAsync(pageQueryRequest, cancellationToken);
}
4. Get Distinct Column Values
public Task<List<ColumnDistinctValues>> GetDistinctColumnValuesAsync(ColumnDistinctValueQueryRequest query, CancellationToken cancellationToken = default)
{
return _dbContext
.Users
.GetDistinctColumnValuesAsync(query, cancellationToken);
}
5. Perform Aggregations
public Task<Object?> AggregateAsync(AggregateQueryRequest query, CancellationToken cancellationToken = default)
{
return _dbContext
.Users
.AggregateAsync(query, cancellationToken);
}
Request Models
FilterQueryRequest
public class FilterQueryRequest
{
public string FilterQuery { get; set; } = "{}";
}
PageQueryRequest
public class PageQueryRequest : FilterQueryRequest
{
public int Page { get; set; } = 1;
public int PageSize { get; set; } = 10;
}
ColumnDistinctValueQueryRequest
public class ColumnDistinctValueQueryRequest : PageQueryRequest
{
public string ColumnName { get; init; }
}
AggregateQueryRequest
public class AggregateQueryRequest : FilterQueryRequest
{
public AggregateType AggregateType { get; init; }
public string ColumnName { get; init; }
}
Response Models
PagedResponse
public record PagedResponse<T>(List<T> Data, int Page, int PageSize, long TotalCount);
ColumnDistinctValues
public class ColumnDistinctValues
{
public List<object> Values { get; set; } = new();
public long TotalCount { get; set; }
}
Enum Definitions
ComparisonType
[JsonConverter(typeof(JsonStringEnumConverter))]
public enum ComparisonType
{
Equal,
NotEqual,
GreaterThan,
GreaterThanOrEqual,
LessThan,
LessThanOrEqual,
Contains,
StartsWith,
EndsWith,
In,
NotIn,
IsNotEmpty,
IsEmpty,
Between,
NotContains,
HasCountEqualTo,
HasCountBetween,
IsTrue,
IsFalse
}
AggregateType
[JsonConverter(typeof(JsonStringEnumConverter))]
public enum AggregateType
{
Sum,
Average,
Count,
Min,
Max
}
MagicQuery Class - Client-Side String Representation
using System.Text.Json;
public class MagicQuery
{
public List<FilterQuery> Filters { get; set; } = new();
public Ordering Order { get; set; } = new();
}
Exception Handling
Pandatech.EFCoreQueryMagic provides several exceptions to handle various error scenarios during filtering, ordering, and
pagination operations. These exceptions allow you to catch specific errors and handle them accordingly. Additionally, we
recommend using the Pandatech.ResponseCrafter
NuGet package for a comprehensive response crafting solution.
namespace EFCoreQueryMagic.Exceptions
{
public abstract class FilterException : Exception
{
protected FilterException(string message) : base(message) {}
protected FilterException() {}
}
public class ComparisonNotSupportedException : FilterException
{
public ComparisonNotSupportedException(string message) : base(message) {}
public ComparisonNotSupportedException() {}
}
public class DefaultOrderingViolation : FilterException {}
public class MappingException : FilterException
{
public MappingException(string message) : base(message) {}
}
public class NoOrderingFoundException : FilterException {}
public class PaginationException : FilterException
{
public PaginationException(string message) : base(message) {}
}
public class PropertyNotFoundException : FilterException
{
public PropertyNotFoundException(string message) : base(message) {}
}
public class UnsupportedFilterException : FilterException
{
public UnsupportedFilterException(string message) : base(message) {}
}
public class UnsupportedValueException : FilterException
{
public UnsupportedValueException(string message) : base(message) {}
}
}
Contributing
We welcome contributions from the community.
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
-
net8.0
- Microsoft.EntityFrameworkCore.Relational (>= 8.0.6)
- PandaTech.BaseConverter (>= 3.0.6)
- Pandatech.Crypto (>= 2.3.2)
- System.Linq.Dynamic.Core (>= 1.3.14)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
1.1.0 | 112 | 5/28/2024 |
1.0.12 | 120 | 5/24/2024 |
1.0.11 | 90 | 5/24/2024 |
1.0.10 | 115 | 5/16/2024 |
1.0.9 | 157 | 5/8/2024 |
1.0.8 | 122 | 5/7/2024 |
1.0.7 | 155 | 4/24/2024 |
1.0.6 | 112 | 4/24/2024 |
1.0.5 | 125 | 4/24/2024 |
1.0.4 | 113 | 4/15/2024 |
1.0.3 | 120 | 4/8/2024 |
1.0.2 | 100 | 4/8/2024 |
1.0.1 | 89 | 4/8/2024 |
1.0.0 | 148 | 4/3/2024 |
Total reimagination of public API