DotTrend 1.0.0

DotTrend

A .NET library for generating time-series trend data from Entity Framework Core queries with support for different database providers. DotTrend makes it easy to create trend reports by date intervals (minute, hour, day, week, month, year) with zero-filling for missing periods.

Features

• 📊 Generate time-series trend data from your database
• 📅 Group by different time intervals (minute, hour, day, week, month, year)
• 📈 Aggregate data using various functions (count, sum, average, min, max)
• 🗄️ Support for multiple database providers:
  • SQL Server
  • MySQL
  • PostgreSQL
  • SQLite
• 🧩 Fluent API with multiple usage patterns for flexibility
• 0️⃣ Automatic zero-filling for missing periods

Installation

Install the package via NuGet:

dotnet add package DotTrend

Quick Start

using DotTrend;
using System;
using System.Linq;

// Get daily order counts for January 2025
var dailyOrderCounts = Trend<Order>
    .Query(dbContext.Orders)
    .Between(new DateTime(2025, 1, 1), new DateTime(2025, 1, 31))
    .PerDay()
    .Count();

// Generate monthly sales report for 2025
var monthlySales = Trend<Order>
    .Query(dbContext.Orders)
    .Between(new DateTime(2025, 1, 1), new DateTime(2025, 12, 31))
    .PerMonth()
    .Sum(o => o.Amount);

// Display the results
foreach (var point in monthlySales)
{
    Console.WriteLine($"{point.Date:yyyy-MM}: ${point.Aggregate}");
}

Usage Patterns

DotTrend supports multiple usage patterns to fit your coding style:

1. Traditional approach with Query method

var result = Trend<Order>
    .Query(dbContext.Orders)
    .Between(startDate, endDate)
    .PerDay()
    .Count();

2. Extension method on IQueryable

var result = dbContext.Orders
    .Trend()
    .Between(startDate, endDate)
    .PerDay() 
    .Count();

3. Static Between method (requires context registration)

First, register your DbContext:

// In your application startup
dbContext.UseTrend();

Then use the static approach:

var result = Trend<Order>
    .Between(startDate, endDate)
    .PerDay()
    .Count();

Time Interval Options

// Per minute (e.g., for real-time dashboards)
var minuteData = Trend<Event>.Query(dbContext.Events)
    .Between(startDate, endDate)
    .PerMinute()
    .Count();

// Per hour
var hourlyData = Trend<Event>.Query(dbContext.Events)
    .Between(startDate, endDate)
    .PerHour()
    .Count();

// Per day (default)
var dailyData = Trend<Event>.Query(dbContext.Events)
    .Between(startDate, endDate)
    .PerDay()
    .Count();

// Per week
var weeklyData = Trend<Event>.Query(dbContext.Events)
    .Between(startDate, endDate)
    .PerWeek()
    .Count();

// Per month
var monthlyData = Trend<Event>.Query(dbContext.Events)
    .Between(startDate, endDate)
    .PerMonth()
    .Count();

// Per year
var yearlyData = Trend<Event>.Query(dbContext.Events)
    .Between(startDate, endDate)
    .PerYear()
    .Count();

Aggregation Functions

// Count (default)
var orderCount = Trend<Order>.Query(dbContext.Orders)
    .Between(startDate, endDate)
    .PerDay()
    .Count();

// Sum
var totalSales = Trend<Order>.Query(dbContext.Orders)
    .Between(startDate, endDate)
    .PerDay()
    .Sum(o => o.Amount);

// Average
var averageOrderValue = Trend<Order>.Query(dbContext.Orders)
    .Between(startDate, endDate)
    .PerDay()
    .Average(o => o.Amount);

// Minimum
var minOrderValue = Trend<Order>.Query(dbContext.Orders)
    .Between(startDate, endDate)
    .PerDay()
    .Min(o => o.Amount);

// Maximum
var maxOrderValue = Trend<Order>.Query(dbContext.Orders)
    .Between(startDate, endDate)
    .PerDay()
    .Max(o => o.Amount);

Database-Specific Adapters

// SQL Server (default)
var sqlServerTrend = Trend<Order>.Query(
    dbContext.Orders,
    new SqlServerAdapter()
);

// MySQL
var mysqlTrend = Trend<Order>.Query(
    dbContext.Orders,
    new MySqlAdapter()
);

// PostgreSQL
var postgresTrend = Trend<Order>.Query(
    dbContext.Orders,
    new PostgresAdapter()
);

// SQLite
var sqliteTrend = Trend<Order>.Query(
    dbContext.Orders,
    new SqliteAdapter()
);

// Auto-detect database type from context
var autoAdapter = dbContext.GetTrendAdapter();
var autoTrend = Trend<Order>.Query(dbContext.Orders, autoAdapter);

Custom Date Column

// Use a different date column instead of the default "CreatedAt"
var ordersByProcessedDate = Trend<Order>.Query(dbContext.Orders)
    .Between(startDate, endDate)
    .DateColumn("ProcessedAt")
    .PerDay()
    .Count();

Result Format

The result of any trend calculation is a list of TrendValue objects, which have these properties:

• Date: The DateTime representing the period
• Aggregate: The decimal value of the aggregation for that period

public class TrendValue
{
    public DateTime Date { get; set; }
    public decimal Aggregate { get; set; }
}

License

This project is licensed under the MIT License - see the LICENSE file for details.