EF.Functions — Provider-Specific Fonksiyonlar

// LIKE — wildcard arama
var products = await context.Products
    .Where(p => EF.Functions.Like(p.Name, "%telefon%"))
    .ToListAsync();
// SQL: WHERE [p].[Name] LIKE N'%telefon%'

// CONTAINS (Full-Text Search — FTS index gerekir)
var results = await context.Products
    .Where(p => EF.Functions.Contains(p.Name, "akıllı AND telefon"))
    .ToListAsync();
// SQL: WHERE CONTAINS([p].[Name], N'akıllı AND telefon')

// FREETEXT (doğal dil araması)
var results = await context.Products
    .Where(p => EF.Functions.FreeText(p.Description, "ucuz kaliteli telefon"))
    .ToListAsync();
// SQL: WHERE FREETEXT([p].[Description], N'ucuz kaliteli telefon')

// Collation — case-sensitive karşılaştırma
var exact = await context.Users
    .Where(u => EF.Functions.Collate(u.Username, "SQL_Latin1_General_CP1_CS_AS") == "Admin")
    .ToListAsync();
// SQL: WHERE [u].[Username] COLLATE SQL_Latin1_General_CP1_CS_AS = N'Admin'

// ILIKE — Case-insensitive LIKE (SQL Server'da varsayılan CI, PG'de explicit gerekir)
var products = await context.Products
    .Where(p => EF.Functions.ILike(p.Name, "%telefon%"))
    .ToListAsync();
// SQL: WHERE p.name ILIKE '%telefon%'

// Full-Text Search — tsvector/tsquery (Türkçe dil desteği dahil!)
var results = await context.Products
    .Where(p => EF.Functions.ToTsVector("turkish", p.Name)
                    .Matches(EF.Functions.ToTsQuery("turkish", "akıllı & telefon")))
    .ToListAsync();
// SQL: WHERE to_tsvector('turkish', name) @@ to_tsquery('turkish', 'akıllı & telefon')

// Trigram benzerlik araması — typo-tolerant! (pg_trgm extension gerekir)
var similar = await context.Products
    .Where(p => EF.Functions.TrigramsAreSimilar(p.Name, "iphne"))
    .OrderBy(p => EF.Functions.TrigramsSimilarityDistance(p.Name, "iphne"))
    .ToListAsync();
// SQL: WHERE name % 'iphne' ORDER BY name <-> 'iphne'
// → "iPhone" bulur! Fuzzy search.

// Collation — case-sensitive (PG varsayılan case-sensitive, CI istersen collate ekle)
var exact = await context.Users
    .Where(u => EF.Functions.Collate(u.Username, "tr-x-icu") == "Admin")
    .ToListAsync();
// SQL: WHERE username COLLATE "tr-x-icu" = 'Admin'

// DateDiffDay — gün farkı
var recentOrders = await context.Orders
    .Where(o => EF.Functions.DateDiffDay(o.OrderDate, DateTime.UtcNow) <= 30)
    .ToListAsync();
// SQL: WHERE DATEDIFF(DAY, [o].[OrderDate], GETUTCDATE()) <= 30

// DateDiffMonth
var thisYear = await context.Orders
    .Where(o => EF.Functions.DateDiffMonth(o.OrderDate, DateTime.UtcNow) < 12)
    .ToListAsync();

// DateDiffHour — son 24 saat
var recent = await context.Logs
    .Where(l => EF.Functions.DateDiffHour(l.Timestamp, DateTime.UtcNow) <= 24)
    .ToListAsync();

// DateFromParts
var date = EF.Functions.DateFromParts(2024, 6, 15);

// PostgreSQL'de DateDiff YOK — doğrudan aritmetik kullan:
var recentOrders = await context.Orders
    .Where(o => DateTime.UtcNow - o.OrderDate < TimeSpan.FromDays(30))
    .ToListAsync();
// SQL: WHERE NOW() - order_date < INTERVAL '30 days'

// Son 12 ay
var thisYear = await context.Orders
    .Where(o => o.OrderDate >= DateTime.UtcNow.AddMonths(-12))
    .ToListAsync();
// SQL: WHERE order_date >= NOW() - INTERVAL '12 months'

// Son 24 saat
var recent = await context.Logs
    .Where(l => l.Timestamp >= DateTime.UtcNow.AddHours(-24))
    .ToListAsync();
// SQL: WHERE timestamp >= NOW() - INTERVAL '24 hours'

// Tarih oluşturma
var specific = await context.Orders
    .Where(o => o.OrderDate == new DateOnly(2024, 6, 15))
    .ToListAsync();
// SQL: WHERE order_date = '2024-06-15'

// SQL Server'da EF.Functions üzerinden JSON sorgusu sınırlı.
// Genelde Raw SQL veya JSON_VALUE ile:
var istanbul = await context.Orders
    .Where(o => EF.Property<string>(o, "ShippingAddress") != null)
    .FromSqlRaw("SELECT * FROM Orders WHERE JSON_VALUE(ShippingAddress, '$.City') = N'İstanbul'")
    .ToListAsync();
// Not: EF Core 10 + SQL Server 2025 ile native json tipi daha iyi destek sağlar (Bölüm 28'e bakın)

// JSON contains — JSONB sütunlarda:
var tagged = await context.Products
    .Where(p => EF.Functions.JsonContains(p.Metadata, @"{""featured"": true}"))
    .ToListAsync();
// SQL: WHERE metadata @> '{"featured": true}'

// JSON key exists:
var hasKey = await context.Products
    .Where(p => EF.Functions.JsonExists(p.Metadata, "discount"))
    .ToListAsync();
// SQL: WHERE metadata ? 'discount'

// IsNumeric
var numeric = await context.Products
    .Where(p => EF.Functions.IsNumeric(p.Sku))
    .ToListAsync();
// SQL: WHERE ISNUMERIC([Sku]) = 1

// DataLength (byte cinsinden veri uzunluğu)
var bigDescriptions = await context.Products
    .Where(p => EF.Functions.DataLength(p.Description) > 10000)
    .ToListAsync();
// SQL: WHERE DATALENGTH([Description]) > 10000

// Random — rastgele sıralama
var randomProducts = await context.Products
    .OrderBy(p => EF.Functions.Random())
    .Take(5)
    .ToListAsync();
// SQL: ORDER BY NEWID()

// IsNumeric yok — regex ile:
var numeric = await context.Products
    .Where(p => Regex.IsMatch(p.Sku, @"^\d+$"))
    .ToListAsync();
// SQL: WHERE sku ~ '^\d+$'

// Byte length:
var bigDescriptions = await context.Products
    .Where(p => EF.Functions.DataLength(p.Description) > 10000)
    .ToListAsync();
// SQL: WHERE octet_length(description) > 10000

// Random — rastgele sıralama
var randomProducts = await context.Products
    .OrderBy(p => EF.Functions.Random())
    .Take(5)
    .ToListAsync();
// SQL: ORDER BY random()

-- Üretilen SQL (SQL Server 2025):
SELECT TOP(5) [p].[Id], [p].[Name], [p].[Embedding]
FROM [Products] AS [p]
ORDER BY VECTOR_DISTANCE('cosine', [p].[Embedding], @queryVector);

-- Üretilen SQL (pgvector extension gerekli):
SELECT p.id, p.name, p.embedding
FROM products AS p
ORDER BY p.embedding <=> @queryVector   -- cosine distance operatörü
LIMIT 5;

-- pgvector kurulumu:
-- CREATE EXTENSION vector;
-- CREATE INDEX ON products USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);