‫فیلتر کردن مجموعه‌های بزرگ در EF Core 10: واکاوی رفتار در مرز ۲۱۰۰ پارامتر در SQL Server

چکیده: فیلتر کردن پرس‌وجوها با مجموعه‌ای از شناسه‌ها با متد Contains یکی از الگوهای متداول در برنامه‌های مبتنی بر Entity Framework Core است. در ارتباط با SQL Server، همواره محدودیت تاریخی ۲۱۰۰ پارامتر مطرح بوده است. برخلاف تصور عمومی، EF Core 10 با عبور از این مرز خطایی صادر نمی‌کند؛ بلکه استراتژی ترجمه پرس‌وجو را به‌صورت خودکار تغییر می‌دهد. این تغییر رفتار اگرچه از بروز استثنا جلوگیری می‌کند، اما در بازه مقادیر نزدیک به مرز مجاز پدیده‌ای موسوم به افت ناگهانی کارایی (Performance Cliff) ایجاد می‌کند؛ به‌گونه‌ای که یک پرس‌وجو با ۲۰۹۸ شناسه زمان اجرایی معادل ۳۴.۶ میلی‌ثانیه ثبت می‌کند، درحالی‌که با اضافه شدن تنها یک شناسه (۲۰۹۹ شناسه)، زمان اجرا به ۴.۰ میلی‌ثانیه کاهش می‌یابد (تقریباً ۸ برابر سریع‌تر). این مقاله با تکیه بر بنچ‌مارک‌های استاندارد BenchmarkDotNet روی جدولی با ۱ میلیون رکورد، سازوکار داخلی ترجمه در EF Core 10، دسته‌بندی پارامترها (Parameter Bucketing)، محدودیت واقعی ۲۰۹۸ پارامتر در هماهنگی با sp_executesql، اثرات کش نقشه اجرای پرس‌وجو (Plan Cache)، و راهکارهای کارآمد برای کلیدهای ترکیبی (Composite Keys) را منحصراً بر پایه قابلیت‌های توکار فریم‌ورک و رویکردهای Native دات‌نت بررسی می‌کند.

var ids = new List<int> { 1, 2, 3 };

var matched = await context.Products
    .AsNoTracking()
    .Where(p => ids.Contains(p.Id))
    .ToListAsync();

SELECT [p].[Id], [p].[Name], ...
FROM [Products] AS [p]
WHERE [p].[Id] IN (@ids1, @ids2, @ids3)

نکته نام‌گذاری: پیشوند سنتی @__ids_0 در EF Core 10 حذف شده و نام‌ها به فرمت تمیزتر @ids1 تغییر یافته‌اند. این تغییر موجب بی‌اعتبار شدن نقشه‌های اجرایی کش‌شده قبلی (Plan Cache Invalidation) در زمان ارتقای نسخه و بار کاری موقت کامپایل در سرور پایگاه‌داده خواهد شد.

protected override int CalculateParameterBucketSize(int count, RelationalTypeMapping elementTypeMapping)
{
    if (count <= 5) return 1;
    if (count <= 150) return 10;
    if (count <= 750) return 50;
    if (count <= 2000) return 100;
    if (count <= 2070) return 10;
    if (count <= MaxParameterCount) return 1; // عدم Padding بین 2070 تا سقف نهایی
    return 200;
}

SELECT COUNT(*)
FROM [Products] AS [p]
WHERE [p].[Id] IN (
    SELECT [__openjson0].[Value]
    FROM OPENJSON(@ids) WITH ([Value] int '$') AS [__openjson0]
)

• ۲۰۹۸ شناسه (استراتژی پیش‌فرض پارامتری): زمان اجرا ۳۴.۶ میلی‌ثانیه | حافظه تخصیص‌داده‌شده: ۳,۳۹۳ کیلوبایت
• ۲۰۹۹ شناسه (استراتژی OPENJSON): زمان اجرا ۴.۰ میلی‌ثانیه | حافظه تخصیص‌داده‌شده: ۷۴۳ کیلوبایت

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString, sqlOptions =>
    {
        // تغییر رفتار پیش‌فرض به استراتژی تک‌پارامتری JSON
        sqlOptions.UseParameterizedCollectionMode(ParameterTranslationMode.Parameter);
    });
}

نکته مهاجرت: متدهای قدیمی‌تر مانند TranslateParameterizedCollectionsToParameters در EF Core 10 منسوخ (Obsolete) شده و با متد فوق جایگزین شده‌اند.

// ۱. پیش‌فرض نگارش ۱۰: ارسال پارامترهای اسکالر به تعداد عناصر (با اعمال Padding)
.Where(p => EF.MultipleParameters(ids).Contains(p.Id))

// ۲. رفتار نگارش‌های ۸ و ۹: یک پارامتر آرایه JSON همراه با OPENJSON
.Where(p => EF.Parameter(ids).Contains(p.Id))

// ۳. جای‌گذاری مقادیر به صورت صریح در متن SQL (Inlined Constants)
.Where(p => EF.Constant(ids).Contains(p.Id))

نکته امنیتی و لاگینگ: در EF Core 10 مقادیر درون‌خطی (EF.Constant) برای اهداف امنیتی به‌صورت پیش‌فرض در خروجی لاگ‌ها سانسور می‌شوند (IN (?, ?, ?))؛ مگر آنکه گزینه EnableSensitiveDataLogging() در زمان پیکربندی فعال شده باشد.

• افت در مجاورت سقف: در مرز ۲۰۹۸ شناسه، متد پیش‌فرض ۳۴.۶ میلی‌ثانیه زمان برده در حالی که استفاده از EF.Parameter آن را به ۴.۳ میلی‌ثانیه کاهش داده است (بهبودی نزدیک به ۸ برابر تنها با یک تغییر کلمه در LINQ).
• ناکارآمدی الگوی Chunking: قطعه‌قطعه کردن لیست به دسته‌های ۲۰۰۰تایی (که پاسخی سنتی در پروژه‌ها بود) بدترین کارایی را ثبت کرده است. در ۱۰۰,۰۰۰ رکورد، این الگو ۵۰ رفت‌وبرگشت به دیتابیس (Round-trip) تحمیل کرده و بیش از ۱.۳ ثانیه زمان برده است.
• ریزش شدید EF.Constant: تا مرز ۲۰۰۰ آیتم عملکرد مناسبی دارد، اما در ۵۰۰۰ آیتم با افت چشمگیر مواجه شده و در مقیاس ۱۰۰,۰۰۰ آیتم، موتور بهینه‌سازی SQL Server با خطای کمبود منابع داخلی در تولید Query Plan متوقف می‌شود (۲۰.۵ ثانیه نگه‌داشتن اتصال پیش از لغو).
• برتری جدول موقت در مقیاس بسیار بزرگ: تا ۱۰,۰۰۰ آیتم، راهکارهای توکار دات‌نت به مراتب سریع‌تر از تولید جدول موقت هستند. تنها در ۱۰۰,۰۰۰ آیتم است که ساخت جدول موقت دستی با ۱۸۷ میلی‌ثانیه گوی سبقت را می‌رباید.

// تمام این الگوها خطای ترجمه (Translation Failure) صادر می‌کنند:
.Where(i => keys.Contains(new { i.TenantId, i.ProductId }))
.Where(i => keys.Any(k => k.TenantId == i.TenantId && k.ProductId == i.ProductId))

var keys = pairs.Select(p => $"{p.TenantId}-{p.ProductId}").ToList();

var matched = await context.Inventory
    .Where(i => keys.Contains(i.TenantId + "-" + i.ProductId))
    .ToListAsync();

• پیامد: ستون‌های کلیدی داخل تابع تبدیل (CAST) قرار می‌گیرند. این امر موجب از بین رفتن خاصیت SARGability و ناکارآمد شدن ایندکس می‌شود.
• دیتابیس برای تمامی رکوردهای جدول، تبدیل و الحاق رشته‌ای را ارزیابی می‌کند. در آزمون ۱۰۰۰ جفت‌کلید، این روش به اتمام مهلت فرمان (Command Timeout - ۳۰ ثانیه) برخورد کرد.

WHERE ([TenantId] = @p0 AND [ProductId] = @p1)
   OR ([TenantId] = @p2 AND [ProductId] = @p3) ...

• فروپاشی پردازه در ۴۹۰ شرط: ایجاد زنجیره با الحاق مستقیم در حلقه، یک درخت عبارت با عمق زیاد (Left-Deep Expression Tree) می‌سازد. در زمان تحلیل بازگشتی این درخت در لایه داخلی EF Core، پردازه دات‌نت در ۴۹۰ شرط با خطای بحرانی 0xC00000FD (STATUS_STACK_OVERFLOW) بسته می‌شود؛ خطایی که قابل رهگیری با try-catch نیست و به خاموشی ناگهانی پردازه منجر می‌گردد.
• درخت متعادل (Balanced Tree): در صورت بازنویسی الگوریتم به‌صورت درخت دودویی متعادل، مشکل سرریز پشته برطرف می‌شود، اما با رسیدن به ۱۰۴۹ جفت‌کلید (معادل ۲۰۹۸ پارامتر اسکالر)، خطای واقعی Too many parameters رخ می‌دهد؛ زیرا عبارت OR ساختار آرایه‌ای ندارد و از مکانیزم نجات‌بخش OPENJSON بهره‌مند نمی‌شود.

// الگوریتم ساخت درخت متعادل برای جلوگیری از Stack Overflow
static Expression BuildBalancedOrTree(IReadOnlyList<Expression> expressions)
{
    var current = expressions;
    while (current.Count > 1)
    {
        var next = new List<Expression>((current.Count + 1) / 2);
        for (int i = 0; i < current.Count; i += 2)
        {
            next.Add(i + 1 < current.Count 
                ? Expression.OrElse(current[i], current[i + 1]) 
                : current[i]);
        }
        current = next;
    }
    return current[0];
}

public async Task<List<Product>> FilterByLargeKeysAsync(
    AppDbContext context, 
    DataTable keysDataTable, 
    CancellationToken ct = default)
{
    var connection = (SqlConnection)context.Database.GetDbConnection();
    await context.Database.OpenConnectionAsync(ct);

    try
    {
        // ۱. ساخت جدول موقت
        await using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = @"
                CREATE TABLE #TempKeys (
                    TenantId INT NOT NULL,
                    ProductId INT NOT NULL,
                    PRIMARY KEY (TenantId, ProductId)
                );";
            await cmd.ExecuteNonQueryAsync(ct);
        }

        // ۲. درج فوق سریع مقادیر با SqlBulkCopy
        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "#TempKeys";
            bulkCopy.ColumnMappings.Add("TenantId", "TenantId");
            bulkCopy.ColumnMappings.Add("ProductId", "ProductId");
            await bulkCopy.WriteToServerAsync(keysDataTable, ct);
        }

        // ۳. اجرای پرس‌وجو با اتصال (Join) روی جدول موقت در بستر EF Core
        var result = await context.Inventory
            .FromSqlRaw(@"
                SELECT i.* 
                FROM [Inventory] AS i
                INNER JOIN #TempKeys AS k 
                    ON i.[TenantId] = k.[TenantId] 
                   AND i.[ProductId] = k.[ProductId]")
            .AsNoTracking()
            .ToListAsync(ct);

        return result;
    }
    finally
    {
        await context.Database.CloseConnectionAsync();
    }
}

-- یک‌بار در دیتابیس ایجاد می‌شود
CREATE TYPE [dbo].[IntIdList] AS TABLE (
    [Id] INT PRIMARY KEY
);

var table = new DataTable();
table.Columns.Add("Id", typeof(int));
foreach (var id in ids) table.Rows.Add(id);

var parameter = new SqlParameter("@IdList", SqlDbType.Structured)
{
    TypeName = "dbo.IntIdList",
    Value = table
};

var matched = await context.Products
    .FromSqlRaw("SELECT p.* FROM [Products] p INNER JOIN @IdList t ON p.[Id] = t.[Id]", parameter)
    .AsNoTracking()
    .ToListAsync();

public sealed class ParameterCountDiagnosticsInterceptor : DbCommandInterceptor
{
    private readonly ILogger<ParameterCountDiagnosticsInterceptor> _logger;

    public ParameterCountDiagnosticsInterceptor(ILogger<ParameterCountDiagnosticsInterceptor> logger)
    {
        _logger = logger;
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result)
    {
        // ثبت هشدار در صورت ورود به بازه هزینه‌بر
        if (command.Parameters.Count >= 1000 && command.Parameters.Count <= 2098)
        {
            _logger.LogWarning(
                "Query executed in the expensive parameter band. Parameter Count: {Count}. Command: {Sql}",
                command.Parameters.Count,
                command.CommandText);
        }

        return base.ReaderExecuting(command, eventData, result);
    }
}

• مرز عملیاتی مفید پارامترها با در نظر گرفتن sp_executesql برابر با ۲۰۹۸ است نه ۲۱۰۰.
• استراتژی پیش‌فرض برای مقادیر بین ۱,۰۰۰ تا ۲,۰۹۸ شناسه نامناسب‌ترین بهره‌وری را دارد؛ در این بازه استفاده صریح از EF.Parameter توصیه می‌شود.
• در مقیاس‌های کلان یا ساختارهای پیچیده مانند کلیدهای ترکیبی، نباید به لایه ترجمه خودکار اتکا کرد؛ بلکه باید از الگوهای مبتنی بر مجموعه‌های جدولی موقت (مانند Table Types یا Temp Tables با Bulk Copy) استفاده نمود.