‫کشف تقلب در تراکنش‌های مالی: ۶ الگوی کاربردی با SQL

public class Cardholder
{
    public long Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public bool IsWhitelisted { get; set; } // برای الگوی ۱ (مدیریت مثبت کاذب)

    // رابطه یک به چند با تراکنش‌ها
    public ICollection<Transaction> Transactions { get; set; } = new List<Transaction>();
}

public class Merchant
{
    public long Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Category { get; set; } = string.Empty; // مثلاً پوشاک، پمپ بنزین

    // رابطه یک به چند با تراکنش‌ها
    public ICollection<Transaction> Transactions { get; set; } = new List<Transaction>();
}

using NetTopologySuite.Geometries;

public class Transaction
{
    public long Id { get; set; }
    public long CardholderId { get; set; }
    public long MerchantId { get; set; }
    
    public decimal Amount { get; set; }
    public DateTime Timestamp { get; set; }
    
    // استفاده از Point برای ذخیره طول و عرض جغرافیایی (الگوی ۲)
    public Point Location { get; set; } = default!; 

    // Navigation Properties
    public Cardholder Cardholder { get; set; } = default!;
    public Merchant Merchant { get; set; } = default!;
}

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Transaction> Transactions => Set<Transaction>();
    public DbSet<Cardholder> Cardholders => Set<Cardholder>();
    public DbSet<Merchant> Merchants => Set<Merchant>();

    protected override void OnModelCreating(ModelCreatingCollection modelBuilder)
    {
        // پیکربندی تراکنش‌ها
        modelBuilder.Entity<Transaction>(entity =>
        {
            entity.HasKey(e => e.Id);
            
            entity.Property(e => e.Amount)
                  .HasPrecision(18, 2); // دقت استاندارد مبالغ مالی

            // تعریف ایندکس ترکیبی حیاتی برای الگوهای ۱، ۲، ۵ و ۶ (Velocity & Off-Hours)
            entity.HasIndex(e => new { e.CardholderId, e.Timestamp })
                  .HasDatabaseName("IX_Transactions_Cardholder_Timestamp");

            // تعریف ایندکس ترکیبی برای الگوی ۴ (Suspicious Merchants)
            entity.HasIndex(e => new { e.MerchantId, e.Timestamp })
                  .HasDatabaseName("IX_Transactions_Merchant_Timestamp");

            // روابط
            entity.HasOne(d => d.Cardholder)
                  .WithMany(p => p.Transactions)
                  .HasForeignKey(d => d.CardholderId)
                  .OnDelete(DeleteBehavior.Restrict);

            entity.HasOne(d => d.Merchant)
                  .WithMany(p => p.Transactions)
                  .HasForeignKey(d => d.MerchantId)
                  .OnDelete(DeleteBehavior.Restrict);
        });
    }
}

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("DefaultConnection"),
        x => x.UseNetTopologySuite() // فعال‌سازی قابلیت‌های جغرافیایی
    ));

var suspiciousTravels = await context.Transactions
    .AsNoTracking()
    .Select(t => new
    {
        t.CardholderId,
        t.Timestamp,
        t.Location,
        // واکشی تراکنش قبلی با کمک لید/لگ تعبیه‌شده در EF Core
        PreviousTimestamp = context.Transactions
            .Where(prev => prev.CardholderId == t.CardholderId && prev.Timestamp < t.Timestamp)
            .OrderByDescending(prev => prev.Timestamp)
            .Select(prev => (DateTime?)prev.Timestamp)
            .FirstOrDefault(),
        PreviousLocation = context.Transactions
            .Where(prev => prev.CardholderId == t.CardholderId && prev.Timestamp < t.Timestamp)
            .OrderByDescending(prev => prev.Timestamp)
            .Select(prev => prev.Location)
            .FirstOrDefault()
    })
    .Where(x => x.PreviousTimestamp != null && 
                // محاسبه فاصله مستقیم بر اساس متر در سمت دیتابیس
                x.Location.Distance(x.PreviousLocation) > 500000) // به عنوان مثال بیش از ۵۰۰ کیلومتر
    .ToListAsync();

نکته کارایی: برای کوئری‌های پیچیده توابع پنجره‌ای و زنجیره‌ای (الگوی ۶)، اگر نگاشت فرمول‌های سنگین به LINQ خوانایی کد را کاهش داد، پیشنهاد می‌شود از قابلیت FromSqlRaw در EF Core استفاده کرده و کوئری SQL بهینه ارائه شده در مقاله را مستقیماً اجرا و خروجی را به این مدل‌ها Map کنید.

using Microsoft.EntityFrameworkCore;

DateTime thirtyDaysAgo = DateTime.UtcNow.AddDays(-30);

var highVelocityBuckets = await context.Transactions
    .AsNoTracking()
    .Where(t => t.Timestamp >= thirtyDaysAgo)
    // بررسی وضعیت فهرست مجاز برای حذف مثبت‌های کاذب
    .Where(t => !t.Cardholder.IsWhitelisted) 
    .GroupBy(t => new
    {
        t.CardholderId,
        // گروه‌بندی بر اساس تاریخ و ساعت (حذف دقیقه‌ها و ثانیه‌ها)
        HourBucket = new DateTime(t.Timestamp.Year, t.Timestamp.Month, t.Timestamp.Day, t.Timestamp.Hour, 0, 0)
    })
    .Where(g => g.Count() > 10) // شرط HAVING در SQL
    .Select(g => new
    {
        g.Key.CardholderId,
        g.Key.HourBucket,
        TransactionCount = g.Count(),
        FirstTransaction = g.Min(t => t.Timestamp),
        LastTransaction = g.Max(t => t.Timestamp)
    })
    .OrderByDescending(p => p.TransactionCount)
    .ToListAsync();

public class VelocityAlertDto
{
    public long CardholderId { get; set; }
    public DateTime Timestamp { get; set; }
    public int TxInLast5Min { get; set; }
}

using Microsoft.EntityFrameworkCore;

// تعریف پارامتر برای جلوگیری از SQL Injection
var thresholdDate = DateTime.UtcNow.AddDays(-30); 

var slidingWindowAlerts = await context.Database
    .SqlQuery<VelocityAlertDto>($"""
        SELECT 
            cardholder_id AS CardholderId, 
            timestamp AS Timestamp, 
            COUNT(*) OVER (
                PARTITION BY cardholder_id 
                ORDER BY timestamp 
                RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
            ) AS TxInLast5Min
        FROM transactions
        WHERE timestamp >= {thresholdDate}
        QUALIFY TxInLast5Min >= 5
        ORDER BY cardholder_id, timestamp
    """)
    .ToListAsync();

نکته مهم برای دیتابیس SQL Server: از آنجا که SQL Server از دستور QUALIFY پشتیبانی نمی‌کند، اگر دیتابیس شما SQL Server است، EF Core دستور بالا را معادل لایه بیرونی (CTE) ترجمه خواهد کرد. فرمت بهینه آن در SqlQuery برای SQL Server به این صورت خواهد بود:

var sqlServerAlerts = await context.Database
    .SqlQuery<VelocityAlertDto>($"""
        WITH RankedTransactions AS (
            SELECT 
                CardholderId, 
                Timestamp, 
                COUNT(*) OVER (
                    PARTITION BY CardholderId 
                    ORDER BY Timestamp 
                    -- تبدیل بازه به فرمت معادل در SQL Server
                    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW 
                ) AS TxInLast5Min
            FROM Transactions
            WHERE Timestamp >= {thresholdDate}
        )
        SELECT CardholderId, Timestamp, TxInLast5Min 
        FROM RankedTransactions 
        WHERE TxInLast5Min >= 5
    """)
    .ToListAsync();

using Microsoft.EntityFrameworkCore;

// تعریف بازه زمانی برای بهینه‌سازی (مثلاً بررسی تراکنش‌های ۴۸ ساعت گذشته)
DateTime filterStartDate = DateTime.UtcNow.AddDays(-2);
double maxSpeedMetersPerSecond = 268.0; // معادل ~۶۰۰ مایل بر ساعت یا ۹۶۵ کیلومتر بر ساعت

var impossibleTravelAlerts = await context.Transactions
    .AsNoTracking()
    .Where(t => t.Timestamp >= filterStartDate)
    .Select(current => new
    {
        CurrentTx = current,
        // یافتن دقیقاً اولین تراکنش قبلیِ همین کاربری که تراکنش فعلی را زده است
        PrevTx = context.Transactions
            .Where(p => p.CardholderId == current.CardholderId && p.Timestamp < current.Timestamp)
            .OrderByDescending(p => p.Timestamp)
            .FirstOrDefault()
    })
    // فیلتر کردن مواردی که تراکنش قبلی وجود دارد و موقعیت مکانی تغییر کرده است
    .Where(x => x.PrevTx != null && x.CurrentTx.Location != x.PrevTx.Location)
    .Select(x => new
    {
        x.CurrentTx.CardholderId,
        FirstTxTime = x.PrevTx!.Timestamp,
        SecondTxTime = x.CurrentTx.Timestamp,
        // محاسبه فاصله به متر در سمت دیتابیس
        DistanceInMeters = x.CurrentTx.Location.Distance(x.PrevTx.Location),
        // محاسبه اختلاف زمان به ثانیه
        TimeApartInSeconds = EF.Functions.DateDiffSecond(x.PrevTx.Timestamp, x.CurrentTx.Timestamp)
    })
    // فیلتر ثانویه: بررسی سرعت جابه‌جایی (سرعت = مسافت / زمان)
    .Where(x => x.TimeApartInSeconds > 0 && 
                (x.DistanceInMeters / x.TimeApartInSeconds) > maxSpeedMetersPerSecond)
    .Select(x => new
    {
        x.CardholderId,
        x.FirstTxTime,
        x.SecondTxTime,
        DistanceInKilometers = x.DistanceInMeters / 1000.0,
        MinutesApart = x.TimeApartInSeconds / 60.0,
        CalculatedSpeedKmh = (x.DistanceInMeters / 1000.0) / (x.TimeApartInSeconds / 3600.0)
    })
    .ToListAsync();

public class ImpossibleTravelAlertDto
{
    public long CardholderId { get; set; }
    public DateTime FirstTx { get; set; }
    public DateTime SecondTx { get; set; }
    public double MinutesApart { get; set; }
    public double MilesApart { get; set; }
}

using Microsoft.EntityFrameworkCore;

DateTime queryThreshold = DateTime.UtcNow.AddDays(-7);

var rawSqlAlerts = await context.Database
    .SqlQuery<ImpossibleTravelAlertDto>($"""
        WITH OrderedTx AS (
            SELECT
                CardholderId,
                Timestamp,
                Location,
                LAG(Timestamp) OVER (PARTITION BY CardholderId ORDER BY Timestamp) AS PrevTs,
                LAG(Location)  OVER (PARTITION BY CardholderId ORDER BY Timestamp) AS PrevLoc
            FROM Transactions
            WHERE Timestamp >= {queryThreshold}
        )
        SELECT
            CardholderId,
            PrevTs AS FirstTx,
            Timestamp AS SecondTx,
            DATEDIFF(SECOND, PrevTs, Timestamp) / 60.0 AS MinutesApart,
            -- در SQL Server متد STDistance فاصله را محاسبه می‌کند
            Location.STDistance(PrevLoc) / 1609.344 AS MilesApart -- تبدیل متر به مایل
        FROM OrderedTx
        WHERE PrevTs IS NOT NULL
          AND PrevLoc.STEquals(Location) = 0
          -- شرط سرعت بالای ۶۰۰ مایل بر ساعت
          AND (Location.STDistance(PrevLoc) / 1609.344) / 
              NULLIF(DATEDIFF(SECOND, PrevTs, Timestamp) / 3600.0, 0) > 600
    """)
    .ToListAsync();

• استفاده از EF.Functions.DateDiffSecond: در روش اول، استفاده از عملیات جبری ساده روی خط زمانی در LINQ همیشه به دستورات بهینه در SQL (مثل DATEDIFF) تبدیل نمی‌شود. متد کمک‌کننده EF.Functions این اطمینان را ایجاد می‌کند که فرآیند کاملاً در سمت دیتابیس هندل شود.
• بررسی نوع فیلد جغرافیایی: توجه داشته باشید که در SQL Server، نوع داده GEOGRAPHY محاسبات را بر حسب متر انجام می‌دهد در حالی که GEOMETRY بر حسب واحدِ سیستم مختصاتِ نقشه (مختصات مسطح) محاسبه می‌کند. مدل ارائه شده در بخش قبل بر اساس استاندارد کروی (GEOGRAPHY) در نظر گرفته شده است.

using Microsoft.EntityFrameworkCore;

// تعریف بازه زمانی برای بالا بردن سرعت کوئری (به عنوان مثال، بررسی تراکنش‌های یک هفته گذشته)
DateTime scanThresholdDate = DateTime.UtcNow.AddDays(-7);

// لیست مبالغ رند و کوچک جهت شناسایی فرآیند تست کارت (Card Testing)
var roundCardTestAmounts = new List<decimal> { 1.00m, 5.00m, 10.00m };

var suspiciousAmountAlerts = await context.Transactions
    .AsNoTracking()
    .Where(t => t.Timestamp >= scanThresholdDate)
    .Where(t => 
        // ۱. مبالغ در آستانه سقف‌های روانی یا قانونی (مثلاً زیر ۱۰۰ دلار/یورو)
        (t.Amount >= 99.50m && t.Amount < 100.00m) ||
        
        // ۲. مبالغ در آستانه سقف‌های برداشت روزانه دیتابیس یا خودپرداز (مثلاً زیر ۵۰۰ دلار/یورو)
        (t.Amount >= 499.50m && t.Amount < 500.00m) ||
        
        // ۳. بررسی مبالغ رندِ تست کارت با استفاده از دستور معادل IN در اس‌کیوال
        roundCardTestAmounts.Contains(t.Amount)
    )
    .Select(t => new
    {
        t.CardholderId,
        t.MerchantId,
        t.Amount,
        t.Timestamp,
        // غنی‌سازی خروجی: مشخص کردن نوع شبهه برای تیم تحلیل تقلب
        AnomalyType = roundCardTestAmounts.Contains(t.Amount) 
            ? "Card Testing (Round Amount)" 
            : "Threshold Evading (Just Below Limit)"
    })
    .OrderByDescending(t => t.Timestamp)
    .ToListAsync();

• پسوند m برای مقادیر مبالغ: از آنجا که ستون Amount در مدل مطلع از ساختار مالی با نوع داده decimal تعریف شده است، حتماً باید در کدهای سی‌شارپ از پسوند m (مانند 99.50m) استفاده کنید تا جلوی خطاهای عدم تطابق نوع داده (Data Type Mismatch) و تبدیل‌های ضمنی (Implicit Conversions) در سمت دیتابیس گرفته شود.
• استفاده از .Contains(): متد .Contains() روی لیست‌های محلی در LINQ، توسط EF Core به دستور بسیار بهینه IN (...) در SQL ترجمه می‌شود که از نظر پرفورمنس کاملاً استاندارد است.
• پشتیبانی از ایندکس: این کوئری به طور کامل از ایندکس ترکیبی که در مرحله اول روی جداول ساختیم (IX_Transactions_Cardholder_Timestamp یا ایندکس‌های مشابه بر پایه زمان) استفاده می‌کند؛ چرا که فیلتر اصلی آن روی ستون زمان (Timestamp) اعمال شده و حجم داده‌های ورودی به شروطِ مبلغ را به شدت کاهش می‌دهد.

using Microsoft.EntityFrameworkCore;

DateTime now = DateTime.UtcNow;
DateTime sevenDaysAgo = now.AddDays(-7);
DateTime sixtyDaysAgo = now.AddDays(-60);

var suspiciousMerchantAlerts = await context.Transactions
    .AsNoTracking()
    // ۱. ابتدا فیلتر بازه زمانی نزدیک برای بررسی جهش (Spike)
    .Where(t => t.Timestamp >= sevenDaysAgo)
    .GroupBy(t => new
    {
        t.MerchantId,
        // دسته‌بندی بر اساس پنجره‌های یک ساعته
        HourBucket = new DateTime(t.Timestamp.Year, t.Timestamp.Month, t.Timestamp.Day, t.Timestamp.Hour, 0, 0)
    })
    .Select(g => new
    {
        g.Key.MerchantId,
        g.Key.HourBucket,
        // تعداد کارت‌های منحصربه‌فرد در این ساعت خاص
        UniqueCardsCount = g.Select(t => t.CardholderId).Distinct().Count(),
        
        // محاسبه بیس‌لاین: میانگین تعداد کاربران منحصربه‌فرد در هر ساعت برای این پذیرنده در ۶۰ روز گذشته
        RollingAvgCards = context.Transactions
            .Where(t => t.MerchantId == g.Key.MerchantId && t.Timestamp >= sixtyDaysAgo && t.Timestamp < sevenDaysAgo)
            .GroupBy(t => new DateTime(t.Timestamp.Year, t.Timestamp.Month, t.Timestamp.Day, t.Timestamp.Hour, 0, 0))
            .Select(subGroup => subGroup.Select(t => t.CardholderId).Distinct().Count())
            // استفاده از Cast برای جلوگیری از خطای تهی بودن خروجی میانگین
            .Average()
    })
    // ۲. فیلتر پذیرنده‌هایی که تعداد کارت‌هایشان بیش از ۳ برابر میانگین خودشان شده است
    .Where(x => x.RollingAvgCards > 0 && x.UniqueCardsCount > (x.RollingAvgCards * 3))
    .Select(x => new
    {
        x.MerchantId,
        x.HourBucket,
        x.UniqueCardsCount,
        x.RollingAvgCards,
        SpikeRatio = (double)x.UniqueCardsCount / x.RollingAvgCards
    })
    .OrderByDescending(x => x.SpikeRatio)
    .ToListAsync();

public class SuspiciousMerchantDto
{
    public long MerchantId { get; set; }
    public DateTime HourBucket { get; set; }
    public int UniqueCards { get; set; }
    public double RollingAvgCards { get; set; }
    public double SpikeRatio { get; set; }
}

using Microsoft.EntityFrameworkCore;

DateTime historyThreshold = DateTime.UtcNow.AddDays(-60);

var alerts = await context.Database
    .SqlQuery<SuspiciousMerchantDto>($"""
        WITH MerchantHourly AS (
            SELECT
                MerchantId,
                DATEADD(hour, DATEDIFF(hour, 0, Timestamp), 0) AS HourBucket,
                COUNT(DISTINCT CardholderId) AS UniqueCards
            FROM Transactions
            WHERE Timestamp >= {historyThreshold}
            GROUP BY MerchantId, DATEADD(hour, DATEDIFF(hour, 0, Timestamp), 0)
        ),
        WithBaseline AS (
            SELECT
                *,
                AVG(CAST(UniqueCards AS FLOAT)) OVER (
                    PARTITION BY MerchantId
                    ORDER BY HourBucket
                    ROWS BETWEEN 168 PRECEDING AND 1 PRECEDING
                ) AS RollingAvgCards
            FROM MerchantHourly
        )
        SELECT 
            MerchantId,
            HourBucket,
            UniqueCards,
            RollingAvgCards,
            (UniqueCards / NULLIF(RollingAvgCards, 0)) AS SpikeRatio
        FROM WithBaseline
        -- فیلتر جهش‌های بالای ۳ برابر و اعمال محدودیت نمایش برای ۷ روز اخیر در لایه نهایی
        WHERE UniqueCards > (RollingAvgCards * 3)
          AND HourBucket >= DATEADD(day, -7, GETUTCDATE())
        ORDER BY SpikeRatio DESC
    """)
    .ToListAsync();

• نقش حیاتی ایندکس دوم: این کوئری به شدت وابسته به ایندکس ترکیبی است که در بخش پیکربندی ساختیم: IX_Transactions_Merchant_Timestamp. بدون این ایندکس، اسکن کل جدول تراکنش‌ها دیتابیس را قفل خواهد کرد.
• برتری روش دوم در تراکنش‌های میلیونی: در پروژه‌های واقعی با بیش از ۵۰۰ هزار تراکنش، روش دوم (SqlQuery) به شدت توصیه می‌شود؛ زیرا مکانیزم ROWS BETWEEN 168 PRECEDING دیتابیس را مأمور می‌کند تا محاسبات را در سطح ردیف‌های پیش‌فرض حافظه موقت (Buffer) انجام دهد، در حالی که روش اول ممکن است باعث تولید کدهای بسیار طولانی Nested Loops در لایه اجرای SQL شود.

using Microsoft.EntityFrameworkCore;

DateTime now = DateTime.UtcNow;
DateTime ninetyDaysAgo = now.AddDays(-90);
DateTime checkPeriodStart = now.AddDays(-1); // برای مثال، بررسی تراکنش‌های مشکوک ۲۴ ساعت گذشته

// ۱. استخراج الگوی ساعات نرمال هر کاربر (ساعاتی که حداقل ۲ بار در آن‌ها تراکنش داشته است)
var cardholderNormalHours = context.Transactions
    .AsNoTracking()
    .Where(t => t.Timestamp >= ninetyDaysAgo)
    .GroupBy(t => new { t.CardholderId, HourOfDay = t.Timestamp.Hour })
    .Where(g => g.Count() >= 2) // فیلتر غنی‌سازی: فقط ساعاتی که به "عادت" تبدیل شده‌اند
    .GroupBy(g => g.Key.CardholderId)
    .Select(g => new
    {
        CardholderId = g.Key,
        EarliestHour = g.Min(x => x.Key.HourOfDay),
        LatestHour = g.Max(x => x.Key.HourOfDay)
    });

// ۲. یافتن تراکنش‌های اخیری که خارج از محدوده ساعت نرمال کاربر رخ داده‌اند
var offHoursAlerts = await context.Transactions
    .AsNoTracking()
    .Where(t => t.Timestamp >= checkPeriodStart)
    // پیوند تراکنش‌های اخیر با جدول الگوهای ساعتی کاربران
    .Join(cardholderNormalHours,
          t => t.CardholderId,
          n => n.CardholderId,
          (t, n) => new { Transaction = t, NormalPattern = n })
    // شرط اصلی: ساعت تراکنش خارج از بازه [EarliestHour, LatestHour] باشد
    .Where(x => x.Transaction.Timestamp.Hour < x.NormalPattern.EarliestHour || 
                x.Transaction.Timestamp.Hour > x.NormalPattern.LatestHour)
    .Select(x => new
    {
        x.Transaction.CardholderId,
        x.Transaction.Id,
        x.Transaction.Amount,
        x.Transaction.Timestamp,
        TransactionHour = x.Transaction.Timestamp.Hour,
        AllowedRange = $"{x.NormalPattern.EarliestHour}:00 to {x.NormalPattern.LatestHour}:00"
    })
    .OrderByDescending(x => x.Timestamp)
    .ToListAsync();

public class OffHoursAlertDto
{
    public long CardholderId { get; set; }
    public long TransactionId { get; set; }
    public decimal Amount { get; set; }
    public DateTime Timestamp { get; set; }
    public int TransactionHour { get; set; }
}

using Microsoft.EntityFrameworkCore;

DateTime historyLimit = DateTime.UtcNow.AddDays(-90);
DateTime scanLimit = DateTime.UtcNow.AddDays(-1);

var rawSqlAlerts = await context.Database
    .SqlQuery<OffHoursAlertDto>($"""
        WITH CardholderHourPattern AS (
            SELECT
                CardholderId,
                DATEPART(HOUR, Timestamp) AS HourOfDay,
                COUNT(*) AS TxCount
            FROM Transactions
            WHERE Timestamp >= {historyLimit}
            GROUP BY CardholderId, DATEPART(HOUR, Timestamp)
        ),
        CardholderNormal AS (
            SELECT
                CardholderId,
                MIN(CASE WHEN TxCount >= 2 THEN HourOfDay END) AS EarliestHour,
                MAX(CASE WHEN TxCount >= 2 THEN HourOfDay END) AS LatestHour
            FROM CardholderHourPattern
            GROUP BY CardholderId
        )
        SELECT 
            t.Id AS TransactionId,
            t.CardholderId AS CardholderId,
            t.Amount AS Amount,
            t.Timestamp AS Timestamp,
            DATEPART(HOUR, t.Timestamp) AS TransactionHour
        FROM Transactions t
        INNER JOIN CardholderNormal cn ON t.CardholderId = cn.CardholderId
        WHERE t.Timestamp >= {scanLimit}
          AND (DATEPART(HOUR, t.Timestamp) < cn.EarliestHour OR DATEPART(HOUR, t.Timestamp) > cn.LatestHour)
        ORDER BY t.Timestamp DESC
    """)
    .ToListAsync();

public class ChainedSignalDto
{
    public long CardholderId { get; set; }
    public long TransactionId { get; set; }
    public decimal Amount { get; set; }
    public long MerchantId { get; set; }
    public int? TimeSinceLastSeconds { get; set; }
    public string MerchantChange { get; set; } = string.Empty;
    public decimal Running24hTotal { get; set; }
    public long TxOfDay { get; set; }
}

using Microsoft.EntityFrameworkCore;

// محدود کردن پنجره بررسی اولیه برای حفظ پرفورمنس سرور (مثلاً تراکنش‌های ۳ روز گذشته)
DateTime analysisThreshold = DateTime.UtcNow.AddDays(-3);

var fraudAlerts = await context.Database
    .SqlQuery<ChainedSignalDto>($"""
        WITH TxWithWindows AS (
            SELECT
                CardholderId,
                Id AS TransactionId,
                Amount,
                MerchantId,
                
                -- ۱. محاسبه فاصله زمانی با تراکنش قبلی بر حسب ثانیه
                DATEDIFF(SECOND, LAG(Timestamp) OVER (PARTITION BY CardholderId ORDER BY Timestamp), Timestamp) AS TimeSinceLastSeconds,
                
                -- ۲. بررسی تغییر پذیرنده نسبت به تراکنش قبلی
                CASE 
                    WHEN MerchantId <> LAG(MerchantId) OVER (PARTITION BY CardholderId ORDER BY Timestamp) THEN 'changed' 
                    ELSE 'same' 
                END AS MerchantChange,
                
                -- ۳. مجموع تراکنش‌های ۲۴ ساعت گذشته کاربر (شبیه‌سازی لغزان در SQL Server)
                SUM(Amount) OVER (
                    PARTITION BY CardholderId
                    ORDER BY Timestamp
                    RANGE BETWEEN INTERVAL '24' HOUR PRECEDING AND CURRENT ROW
                ) AS Running24hTotal,
                
                -- ۴. شمارنده تعداد تراکنش‌های امروزِ این کاربر
                ROW_NUMBER() OVER (
                    PARTITION BY CardholderId, CAST(Timestamp AS DATE)
                    ORDER BY Timestamp
                ) AS TxOfDay
            FROM Transactions
            WHERE Timestamp >= {analysisThreshold}
        )
        -- اعمال فرضیه تقلب: کارت‌تستینگ با سرعت بالا و مکرر در فروشگاه‌های مختلف
        SELECT *
        FROM TxWithWindows
        WHERE TxOfDay >= 5                       -- حداقل ۵ تراکنش در امروز ثبت شده باشد
          AND TimeSinceLastSeconds < 60          -- فاصله تراکنش فعلی با قبلی کمتر از ۱ دقیقه باشد
          AND MerchantChange = 'changed'        -- پذیرنده فوراً تغییر کرده باشد
        ORDER BY CardholderId, TimeSinceLastSeconds
    """)
    .ToListAsync();

// نمونه‌ای از تغییر فرضیه تقلب بدون دستکاری توابع پنجره‌ای بالا:
var newHypothesisAlerts = fraudAlerts
    .Where(x => x.Running24hTotal > 10000000m && x.TxOfDay > 4)
    .ToList();

• فیلتر کردن پیش از پنجره: شرط WHERE Timestamp >= {analysisThreshold} دقیقاً درون بخش CTE قرار گرفته است. این کار باعث می‌شود دیتابیس ابتدا حجم داده‌ها را کاهش دهد و سپس توابع سنگین SUM OVER و LAG را اعمال کند که سرعت اجرای کوئری را تا ۱۰ برابر افزایش می‌دهد.
• ایندکس ترکیبی: این الگو نیز مانند الگوهای قبلی به طور کامل از ایندکس ترکیبی IX_Transactions_Cardholder_Timestamp بهره می‌برد.