LINQ → SQL Çeviri Rehberi

LINQ → SQL: Ne Zaman SQL'ü Tetiklersin?

Temel Kural: .ToList(), .First(), .Count() gibi "materialization" metodlarını mümkün olduğunca geç çağır. Her .Where(), .Select(), .OrderBy() çağrısı sorguyu zenginleştirir ama DB'ye gitmez — ta ki materialization tetiklenene kadar.

Aggregate (Toplama) Fonksiyonları

// Count
var count = await context.Products.CountAsync(p => p.IsActive);

// Sum, Average, Min, Max
var totalStock = await context.Products.SumAsync(p => p.Stock);
var avgPrice = await context.Products.AverageAsync(p => p.Price);
var cheapest = await context.Products.MinAsync(p => p.Price);
var mostExpensive = await context.Products.MaxAsync(p => p.Price);

SELECT COUNT(*) FROM [Products] WHERE [IsActive] = 1;
SELECT SUM([Stock]) FROM [Products];
SELECT AVG([Price]) FROM [Products];
SELECT MIN([Price]) FROM [Products];
SELECT MAX([Price]) FROM [Products];

SELECT COUNT(*) FROM products WHERE is_active = TRUE;
SELECT SUM(stock) FROM products;
SELECT AVG(price) FROM products;
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;

GroupBy

// Kategoriye göre grupla — ürün sayısı ve ortalama fiyat
var stats = await context.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new
    {
        CategoryId = g.Key,
        ProductCount = g.Count(),
        AvgPrice = g.Average(p => p.Price),
        MaxPrice = g.Max(p => p.Price)
    })
    .ToListAsync();

SELECT [p].[CategoryId],
       COUNT(*) AS [ProductCount],
       AVG([p].[Price]) AS [AvgPrice],
       MAX([p].[Price]) AS [MaxPrice]
FROM [Products] AS [p]
GROUP BY [p].[CategoryId];

SELECT p.category_id,
       COUNT(*) AS product_count,
       AVG(p.price) AS avg_price,
       MAX(p.price) AS max_price
FROM products AS p
GROUP BY p.category_id;

// GroupBy + Having (filtrelenmiş grup)
var popularCategories = await context.Products
    .GroupBy(p => p.CategoryId)
    .Where(g => g.Count() > 5)           // ← HAVING
    .Select(g => new { g.Key, Count = g.Count() })
    .ToListAsync();

SELECT [p].[CategoryId] AS [Key], COUNT(*) AS [Count]
FROM [Products] AS [p]
GROUP BY [p].[CategoryId]
HAVING COUNT(*) > 5;

SELECT p.category_id AS key, COUNT(*) AS count
FROM products AS p
GROUP BY p.category_id
HAVING COUNT(*) > 5;

Inner Join

// Inner Join — navigation property YOKSA kullanılır
var result = await context.Products
    .Join(context.Categories,
        product => product.CategoryId,
        category => category.Id,
        (product, category) => new
        {
            ProductName = product.Name,
            CategoryName = category.Name
        })
    .ToListAsync();

SELECT [p].[Name] AS [ProductName], [c].[Name] AS [CategoryName]
FROM [Products] AS [p]
INNER JOIN [Categories] AS [c] ON [p].[CategoryId] = [c].[Id];

SELECT p.name AS product_name, c.name AS category_name
FROM products AS p
INNER JOIN categories AS c ON p.category_id = c.id;

Navigation property varsa Join yazma! EF Core Include veya Select(p => p.Category.Name) ile otomatik JOIN üretir. Manuel Join() sadece navigation olmayan senaryolar için.

Nasıl çalışır?

EF Core senin Join() çağrını alıp SQL'e çevirir:

• 1. parametre (inner sequence) → hangi tabloyla join yapılacak
• 2. parametre (outer key) → sol tablonun eşleşme sütunu → ON koşulunun sol tarafı
• 3. parametre (inner key) → sağ tablonun eşleşme sütunu → ON koşulunun sağ tarafı
• 4. parametre (result selector) → SELECT listesi

Birden fazla sütunla eşleşme yapacaksan anonymous type kullan: .Join(ctx.OrderItems, o => new { o.Id, o.Year }, oi => new { oi.OrderId, oi.Year }, ...)

Ne Zaman Inner Join Kullanmalısın?

/ Sık Yapılan Hata:

// ❌ YANLIŞ: Navigation property varken manuel Join
var result = await context.Products
    .Join(context.Categories,
        p => p.CategoryId, c => c.Id,
        (p, c) => new { p.Name, Category = c.Name })
    .ToListAsync();

// ✅ DOĞRU: Navigation property ile doğrudan erişim
var result = await context.Products
    .Select(p => new { p.Name, Category = p.Category.Name })
    .ToListAsync();
// İkisi de aynı SQL'i üretir — ama navigation versiyonu daha okunur ve refactor-safe

Left Join

// Left Join — EF Core 9 ve öncesi (GroupJoin + SelectMany + DefaultIfEmpty)
var result = await context.Products
    .GroupJoin(context.Reviews,
        p => p.Id,
        r => r.ProductId,
        (product, reviews) => new { product, reviews })
    .SelectMany(
        x => x.reviews.DefaultIfEmpty(),
        (x, review) => new
        {
            Product = x.product.Name,
            Review = review != null ? review.Comment : "Yorum yok"
        })
    .ToListAsync();

// ✅ EF Core 10+ — Aynı sorgu çok daha temiz:
var result = await context.Products
    .LeftJoin(
        context.Reviews,
        p => p.Id,
        r => r.ProductId,
        (product, review) => new
        {
            Product = product.Name,
            Review = review != null ? review.Comment : "Yorum yok"
        })
    .ToListAsync();

SELECT [p].[Name] AS [Product],
       COALESCE([r].[Comment], N'Yorum yok') AS [Review]
FROM [Products] AS [p]
LEFT JOIN [Reviews] AS [r] ON [p].[Id] = [r].[ProductId];

SELECT p.name AS product,
       COALESCE(r.comment, 'Yorum yok') AS review
FROM products AS p
LEFT JOIN reviews AS r ON p.id = r.product_id;

EF Core'da Left Join nasıl yazılır?

EF Core 9 öncesinde Left Join yazmak karmaşıktı (GroupJoin + SelectMany + DefaultIfEmpty). EF Core 10 ile artık tek satırda yazabilirsin:

// EF Core 10 — temiz syntax
var result = await context.Products
    .LeftJoin(
        context.Reviews,
        p => p.Id,
        r => r.ProductId,
        (product, review) => new { product.Name, review.Comment })
    .ToListAsync();

Left Join'de sağ taraf null gelebilir! DTO'daki property'yi nullable yap veya COALESCE / ?? kullan.

Ne Zaman Left Join Kullanmalısın?

/ Sık Yapılan Hata:

// ❌ YANLIŞ: GroupJoin sonucu düzleştirmeden kullanma
var wrong = await context.Products
    .GroupJoin(context.Reviews, p => p.Id, r => r.ProductId, (p, reviews) => new { p, reviews })
    .ToListAsync(); // N+1 riski! reviews her ürün için ayrı sorgu olabilir

// ✅ DOĞRU: SelectMany + DefaultIfEmpty ile düzleştir (veya LeftJoin kullan)
var correct = await context.Products
    .GroupJoin(context.Reviews, p => p.Id, r => r.ProductId, (p, reviews) => new { p, reviews })
    .SelectMany(x => x.reviews.DefaultIfEmpty(), (x, review) => new
    {
        Product = x.p.Name,
        Review = review != null ? review.Comment : "Yorum yok"
    })
    .ToListAsync();

Right Join

// RightJoin — EF Core 10+ 🆕
var result = await context.OrderItems
    .RightJoin(
        context.Products,
        oi => oi.ProductId,
        p => p.Id,
        (orderItem, product) => new
        {
            product.Name,
            Sold = orderItem != null
        })
    .ToListAsync();

SELECT [p].[Name], CASE WHEN [o].[Id] IS NOT NULL THEN 1 ELSE 0 END AS [Sold]
FROM [OrderItems] AS [o]
RIGHT JOIN [Products] AS [p] ON [o].[ProductId] = [p].[Id];

SELECT p.name, CASE WHEN o.id IS NOT NULL THEN TRUE ELSE FALSE END AS sold
FROM order_items AS o
RIGHT JOIN products AS p ON o.product_id = p.id;

🔬 Right Join Ne Zaman Gerekir?

Right Join, mantıksal olarak Left Join'in ayna görüntüsüdür. Çoğu senaryoda tabloların sırasını değiştirip Left Join kullanabilirsin. Ancak şu durumlarda Right Join tercih edilir:

• Mevcut bir sorgu pipeline'ını bozmadan "diğer tarafı da dahil et" istediğinde
• Raw SQL raporlarında okunabilirlik açısından "ana tablo sağda" daha doğal geldiğinde

Uyarı: Right Join, EF Core 9 öncesinde desteklenmiyordu. Eski projelerde Left Join + tablo sırası değişikliği kullan.

Ne Zaman Right Join Kullanmalısın?

Full Outer Join

EF Core'da native FullJoin() operatörü yoktur. Aşağıdaki iki yoldan biriyle elde edilir:

// Yol 1: LeftJoin + RightJoin + Union (EF Core 10+)
var left = context.Students
    .LeftJoin(context.Departments,
        s => s.DepartmentId, d => d.Id,
        (s, d) => new { Student = s.Name, Department = (string?)d.Name });

var right = context.Students
    .RightJoin(context.Departments,
        s => s.DepartmentId, d => d.Id,
        (s, d) => new { Student = (string?)s.Name, Department = d.Name });

var fullJoin = await left.Union(right).ToListAsync();

// Yol 2: Raw SQL
var result = await context.Database
    .SqlQueryRaw<StudentDeptDto>("""
        SELECT s.Name AS Student, d.Name AS Department
        FROM Students s
        FULL OUTER JOIN Departments d ON s.DepartmentId = d.Id
    """)
    .ToListAsync();

-- Yol 1: UNION yaklaşımı
SELECT [s].[Name] AS [Student], [d].[Name] AS [Department]
FROM [Students] AS [s]
LEFT JOIN [Departments] AS [d] ON [s].[DepartmentId] = [d].[Id]
UNION
SELECT [s].[Name] AS [Student], [d].[Name] AS [Department]
FROM [Students] AS [s]
RIGHT JOIN [Departments] AS [d] ON [s].[DepartmentId] = [d].[Id];

-- Yol 2: Doğrudan FULL OUTER JOIN
SELECT s.Name AS Student, d.Name AS Department
FROM Students s
FULL OUTER JOIN Departments d ON s.DepartmentId = d.Id;

-- Yol 1: UNION yaklaşımı
SELECT s.name AS student, d.name AS department
FROM students AS s
LEFT JOIN departments AS d ON s.department_id = d.id
UNION
SELECT s.name AS student, d.name AS department
FROM students AS s
RIGHT JOIN departments AS d ON s.department_id = d.id;

-- Yol 2: Doğrudan FULL OUTER JOIN
SELECT s.name AS student, d.name AS department
FROM students s
FULL OUTER JOIN departments d ON s.department_id = d.id;

EF Core 9 ve öncesi → GroupJoin + SelectMany + DefaultIfEmpty (LEFT JOIN) — Right Join için tabloları ters çevirmeniz gerekir.
EF Core 10+ → LeftJoin() / RightJoin() tek satır. C# query syntax (from...select) henüz desteklemiyor.

Full Outer Join Ne Zaman Kullanmalısın?

Any / All / Contains

// Any — en az bir kayıt var mı?
var hasExpensive = await context.Products.AnyAsync(p => p.Price > 100_000);

// All — tüm kayıtlar koşulu sağlıyor mu?
var allActive = await context.Products.AllAsync(p => p.IsActive);

// Contains — IN operatörü
var categoryIds = new[] { 1, 3, 5 };
var filtered = await context.Products
    .Where(p => categoryIds.Contains(p.CategoryId))
    .ToListAsync();

-- Any
SELECT CASE WHEN EXISTS (
    SELECT 1 FROM [Products] WHERE [Price] > 100000) THEN 1 ELSE 0 END;

-- All
SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM [Products] WHERE [IsActive] = 0) THEN 1 ELSE 0 END;

-- Contains → IN
SELECT * FROM [Products]
WHERE [CategoryId] IN (1, 3, 5);

-- Any
SELECT EXISTS (
    SELECT 1 FROM products WHERE price > 100000);

-- All
SELECT NOT EXISTS (
    SELECT 1 FROM products WHERE is_active = FALSE);

-- Contains → IN (veya = ANY)
SELECT * FROM products
WHERE category_id = ANY(ARRAY[1, 3, 5]);

🆕 EF Core 10 (GA) — Parameterized Collection iyileştirmesi:

int[] ids = [1, 2, 3];
var products = context.Products.Where(p => ids.Contains(p.Id)).ToListAsync();

EF Sürümü	Üretilen SQL	Avantaj/Dezavantaj
EF Core ≤7	WHERE Id IN (1, 2, 3)	Her farklı liste = farklı SQL = plan cache şişer
EF Core 8-9	WHERE Id IN (SELECT value FROM OPENJSON(@ids))	Tek plan ama cardinality bilgisi yok
EF Core 10	WHERE Id IN (@p1, @p2, @p3)	Tek plan + cardinality bilgisi var

EF 10 ayrıca "padding" yapar (8 elemanlı liste → 10 parametre gönderir) böylece farklı boyutlardaki listeler de aynı plan'ı kullanır.

// Strateji kontrolü (EF Core 10):
options.UseSqlServer(conn, o => o.UseParameterizedCollectionMode(ParameterTranslationMode.Constant));
// Veya sorgu bazlı:
var result = context.Products.Where(p => EF.Constant(ids).Contains(p.Id)).ToListAsync();

Distinct

var cities = await context.Customers
    .Select(c => c.City)
    .Distinct()
    .ToListAsync();

SELECT DISTINCT [c].[City] FROM [Customers] AS [c];

SELECT DISTINCT c.city FROM customers AS c;

Union / UnionAll

// Union — tekrar eden satırları eler (UNION)
var allNames = await context.Products.Select(p => p.Name)
    .Union(context.Categories.Select(c => c.Name))
    .ToListAsync();

// UnionAll — tekrarları korur (UNION ALL)
var allWithDuplicates = await context.Products.Select(p => p.Name)
    .Concat(context.Categories.Select(c => c.Name))    // Concat = UNION ALL
    .ToListAsync();

-- Union (tekrarsız)
SELECT [p].[Name] FROM [Products] AS [p]
UNION
SELECT [c].[Name] FROM [Categories] AS [c];

-- Union All (tekrarlı)
SELECT [p].[Name] FROM [Products] AS [p]
UNION ALL
SELECT [c].[Name] FROM [Categories] AS [c];

-- Union (tekrarsız)
SELECT p.name FROM products AS p
UNION
SELECT c.name FROM categories AS c;

-- Union All (tekrarlı)
SELECT p.name FROM products AS p
UNION ALL
SELECT c.name FROM categories AS c;

Except

// Except — A'da olup B'de olmayan
var onlyInProducts = await context.Products.Select(p => p.Name)
    .Except(context.ArchivedProducts.Select(a => a.Name))
    .ToListAsync();

SELECT [p].[Name] FROM [Products] AS [p]
EXCEPT
SELECT [a].[Name] FROM [ArchivedProducts] AS [a];

SELECT p.name FROM products AS p
EXCEPT
SELECT a.name FROM archived_products AS a;

Intersect

// Intersect — her iki sorguda da ortak olan satırlar
var common = await context.Products.Select(p => p.Name)
    .Intersect(context.FeaturedProducts.Select(f => f.Name))
    .ToListAsync();

SELECT [p].[Name] FROM [Products] AS [p]
INTERSECT
SELECT [f].[Name] FROM [FeaturedProducts] AS [f];

SELECT p.name FROM products AS p
INTERSECT
SELECT f.name FROM featured_products AS f;

Set Operasyonları: Ne Zaman Hangisini Kullan?

/ Set Operasyonlarında Sık Hatalar:

// ❌ YANLIŞ: Farklı kolon sayısı/tipiyle Union (compile error)
var wrong = context.Products.Select(p => new { p.Name, p.Price })
    .Union(context.Categories.Select(c => new { c.Name })); // kolon uyumsuz!

// ✅ DOĞRU: Aynı shape (aynı tip ve sayıda property)
var correct = context.Products.Select(p => new { p.Name })
    .Union(context.Categories.Select(c => new { c.Name }))
    .ToListAsync();

// ❌ YANLIŞ: "Arşivlenmiş ürünleri çıkar" — client-side filter ile
var products = await context.Products.ToListAsync();
var archived = await context.ArchivedProducts.Select(a => a.Name).ToListAsync();
var result = products.Where(p => !archived.Contains(p.Name)).ToList(); // 💀 Tüm veri bellekte

// ✅ DOĞRU: EXCEPT ile DB'de çöz
var result = await context.Products.Select(p => p.Name)
    .Except(context.ArchivedProducts.Select(a => a.Name))
    .ToListAsync(); // ✅ Tek SQL, DB'de çözülür

Performans İpucu: Union() implicit DISTINCT yapar. Tekrar umurunda değilse Concat() (UNION ALL) kullan — sort maliyetinden kurtulursun.

String Operasyonları

var results = await context.Products
    .Where(p => p.Name.Contains("phone"))        // LIKE '%phone%'
    .Where(p => p.Name.StartsWith("i"))          // LIKE 'i%'
    .Where(p => p.Name.EndsWith("Pro"))          // LIKE '%Pro'
    .Where(p => p.Name.ToLower() == "iphone")    // LOWER([Name]) = 'iphone'
    .Where(p => p.Name.Length > 5)               // LEN([Name]) > 5
    .ToListAsync();

WHERE [p].[Name] LIKE N'%phone%'
  AND [p].[Name] LIKE N'i%'
  AND [p].[Name] LIKE N'%Pro'
  AND LOWER([p].[Name]) = N'iphone'
  AND LEN([p].[Name]) > 5;

WHERE p.name ILIKE '%phone%'
  AND p.name LIKE 'i%'
  AND p.name LIKE '%Pro'
  AND LOWER(p.name) = 'iphone'
  AND LENGTH(p.name) > 5;

Tarih & Zaman

var recent = await context.Orders
    .Where(o => o.OrderDate.Date == DateTime.Today)
    .Where(o => o.OrderDate.Year == 2025)
    .Where(o => o.OrderDate > DateTime.UtcNow.AddDays(-7))
    .ToListAsync();

WHERE CONVERT(date, [o].[OrderDate]) = CONVERT(date, GETDATE())
  AND DATEPART(year, [o].[OrderDate]) = 2025
  AND [o].[OrderDate] > DATEADD(day, -7, GETUTCDATE());

WHERE o.order_date::date = CURRENT_DATE
  AND EXTRACT(YEAR FROM o.order_date) = 2025
  AND o.order_date > NOW() - INTERVAL '7 days';

Null Kontrolü

var result = await context.Products
    .Where(p => p.Description != null)                    // IS NOT NULL
    .Where(p => p.DeletedAt == null)                      // IS NULL
    .Select(p => new { Name = p.Name ?? "İsimsiz" })     // COALESCE
    .ToListAsync();

WHERE [p].[Description] IS NOT NULL
  AND [p].[DeletedAt] IS NULL;

SELECT COALESCE([p].[Name], N'İsimsiz') AS [Name] FROM [Products];

WHERE p.description IS NOT NULL
  AND p.deleted_at IS NULL;

SELECT COALESCE(p.name, 'İsimsiz') AS name FROM products;

Sıralama & Sayfalama

var paged = await context.Products
    .OrderBy(p => p.Name)
    .ThenByDescending(p => p.Price)
    .Skip(20)
    .Take(10)
    .ToListAsync();

SELECT * FROM [Products]
ORDER BY [Name] ASC, [Price] DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT * FROM products
ORDER BY name ASC, price DESC
LIMIT 10 OFFSET 20;

Conditional (Koşullu) İfadeler

var result = await context.Products
    .Select(p => new
    {
        p.Name,
        Status = p.Stock > 0 ? "Stokta" : "Tükendi",
        PriceCategory = p.Price > 10000 ? "Premium" : p.Price > 1000 ? "Orta" : "Ekonomik"
    })
    .ToListAsync();

SELECT [p].[Name],
       CASE WHEN [p].[Stock] > 0 THEN N'Stokta' ELSE N'Tükendi' END AS [Status],
       CASE
           WHEN [p].[Price] > 10000 THEN N'Premium'
           WHEN [p].[Price] > 1000 THEN N'Orta'
           ELSE N'Ekonomik'
       END AS [PriceCategory]
FROM [Products] AS [p];

SELECT p.name,
       CASE WHEN p.stock > 0 THEN 'Stokta' ELSE 'Tükendi' END AS status,
       CASE
           WHEN p.price > 10000 THEN 'Premium'
           WHEN p.price > 1000 THEN 'Orta'
           ELSE 'Ekonomik'
       END AS price_category
FROM products AS p;

Client Evaluation — Dikkat!

// ❌ Client'ta çalışır (SQL'e çevrilemez — tüm tablo çekilir!)
var result = context.Products
    .Where(p => MyCustomMethod(p.Name))   // ← EF bunu SQL'e çeviremez
    .ToList();

// ❌ EF Core 3.0+ bu durumda HATA FIRLATTIR (eski sürümler sessizce client'ta çalıştırırdı)
// InvalidOperationException: ... could not be translated

// ✅ Çözüm 1: Önce SQL'de filtrele, sonra client'ta işle
var result = context.Products
    .Where(p => p.IsActive)          // SQL'de çalışır
    .AsEnumerable()                  // Buradan sonrası client
    .Where(p => MyCustomMethod(p.Name))
    .ToList();

// ✅ Çözüm 2: EF fonksiyonlarıyla yeniden yaz
var result = context.Products
    .Where(p => EF.Functions.Like(p.Name, "%phone%"))
    .ToList();

Kural: Where(), OrderBy(), Select() içinde yazdığın her ifade SQL'e çevrilebilir olmalı.
Çevrilemezse → ya AsEnumerable() sonrası yap, ya da EF.Functions kullan.