EF Core FromSql, SqlQuery ve ExecuteSql ile Ham SQL Sorguları

FromSql ile temel kullanım

FromSql, bir SQL sorgusuna dayanan LINQ sorgusu başlatır. Aynı yöntemle varlık verisi döndüren bir saklı yordam (stored procedure) da çalıştırabilirsiniz:

var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToListAsync();

var popular = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToListAsync();

Belgelerin vurguladığı bir kısıt: FromSql yalnızca doğrudan bir DbSet üzerinde kullanılabilir; rastgele bir LINQ sorgusunun üzerine eklenemez. Yani önce Where yazıp sonra FromSql çağıramazsınız; SQL her zaman sorgunun başlangıç noktasıdır, LINQ ise onun üzerine kurulur.

Varlık türü döndüren SQL sorgularının bazı kuralları vardır. SQL, varlık türünün tüm özellikleri için veri döndürmelidir; sonuç kümesindeki kolon adları, özelliklerin eşlendiği kolon adlarıyla eşleşmelidir. Belgeler bunun EF6'dan farklı olduğunu not eder: EF6 SQL sorgularında özellik-kolon eşlemesini yok sayar ve kolon adlarının özellik adlarıyla eşleşmesini beklerdi. Ayrıca SQL sorgusu ilişkili veri içeremez; ancak çoğu durumda sorgunun üzerine Include ekleyerek ilişkili veriyi getirebilirsiniz. Kolon adlarının modelde nasıl belirlendiği property ve kolon yapılandırma yazısında.

Parametreler: interpolasyon neden güvenli?

Bir saklı yordama parametre vermek için değeri interpolasyonla SQL metnine yazarsınız. Sözdizimi sıradan C# string interpolasyonuna benzese de belgelere göre verilen değer bir DbParameter içine sarılır ve SQL metnine yalnızca üretilen parametre adı yerleştirilir. Bu hem SQL injection'ı engeller hem de değerin veritabanına doğru ve verimli biçimde gitmesini sağlar.

var user = "johndoe";

var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToListAsync();

Saklı yordamlarda, özellikle isteğe bağlı parametreler varsa, SQL metninde adlandırılmış parametre kullanmak faydalıdır. Gönderilen parametre üzerinde daha fazla denetim istiyorsanız kendiniz bir DbParameter (SQL Server için SqlParameter) oluşturup değer olarak verebilirsiniz; böylece parametrenin veritabanı türünü, boyutunu, kesinliğini ya da uzunluğunu tam olarak belirleyebilirsiniz.

var user = new SqlParameter("user", "johndoe");

var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToListAsync();

Belgeler burada bir uyarı ekler: verdiğiniz parametreler saklı yordam tanımıyla tam olarak eşleşmelidir. Sıralamaya özellikle dikkat edin, hiçbir parametreyi atlamayın ya da yerini karıştırmayın; bunun yerine adlandırılmış parametre gösterimini kullanmayı düşünebilirsiniz. Parametre türlerinin uyumlu olduğundan ve boyut, kesinlik, ölçek gibi özelliklerin gerektiği gibi ayarlandığından emin olun.

Dinamik SQL ve FromSqlRaw

Parametreleme her yerde mümkün değildir. Belgelerdeki örnekte filtrelenecek kolonun adı bir C# değişkeninde tutulur ve şöyle bir sorgu yazmak cazip gelebilir:

var propertyName = "User";
var propertyValue = "johndoe";

// Çalışmaz: kolon adı parametre olamaz
var blogs = await context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToListAsync();

Bu kod çalışmaz, çünkü veritabanları kolon adlarının (ya da şemanın herhangi bir parçasının) parametreyle verilmesine izin vermez. Belgeler önce dinamik sorgu kurmanın sonuçlarını düşünmeyi önerir: kullanıcıdan kolon adı almak, index'i olmayan bir kolonun seçilmesine ve sorgunun son derece yavaş çalışıp veritabanını yormasına, ya da göstermek istemediğiniz verileri içeren bir kolonun seçilmesine izin verebilir. Gerçekten dinamik senaryolar dışında, iki kolon adı için tek bir parametreli sorgu yerine iki ayrı sorgu yazmak genellikle daha iyidir.

Dinamik SQL kurmaya karar verdiyseniz FromSqlRaw kullanmanız gerekir. Bu yöntem değişken veriyi veritabanı parametresi yerine doğrudan SQL metnine yerleştirmenize izin verir. Doğru kullanımda yalnızca güvenilir ya da temizlenmiş parçalar metne girer, kullanıcı değeri ise yine parametre olarak gönderilir:

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = await context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToListAsync();

Bu örnekte kolon adı C# interpolasyonuyla doğrudan SQL'e eklenir; bu değerin güvenli olduğundan emin olmak sizin sorumluluğunuzdur. Değer güvenilmeyen bir kaynaktan geliyorsa noktalı virgül, yorum işaretleri ve diğer SQL yapıları gibi özel karakterleri tespit edip uygun biçimde kaçışlamanız ya da bu girdileri reddetmeniz gerekir. Kolon değeri ise bir DbParameter ile gönderildiği için SQL injection karşısında güvenlidir. Belgelerin "reddetme" seçeneğinin basit bir uygulaması, gelen kolon adını izin verilen adlardan oluşan sabit bir listeyle karşılaştırmak ve listede olmayan her değeri geri çevirmektir. Aşağıdaki küçük kontrol bu yazı için yazılmıştır ve çalıştırılmamıştır.

var izinli = new[] { "Url", "Rating" };
if (!izinli.Contains(columnName))
{
    throw new ArgumentException("Geçersiz kolon adı");
}

EF Core 10: birleştirme uyarısı

EF Core 10 sürüm notlarına göre EF artık "raw" bir SQL metodu çağrısının içinde string birleştirme yapıldığında uyarı veren bir analizör içerir. Belgelerdeki örnek, kolon adını + ile SQL metnine ekleyen bir FromSqlRaw çağrısıdır. Sürüm notları, fieldName değeri güvenilirse ya da düzgün temizlendiyse bu uyarının güvenle bastırılabileceğini belirtir. Uyarıyı bastırmadan önce gerçekten bu durumda olduğunuzu doğrulayın; analizörün amacı tam da gözden kaçan birleştirmeleri görünür kılmaktır.

var users = context.Users.FromSqlRaw("SELECT * FROM Users WHERE [" + fieldName + "] IS NULL");

SQL üzerine LINQ eklemek

İlk SQL sorgusunun üzerine LINQ operatörleri ekleyebilirsiniz; EF Core sizin SQL'inizi bir alt sorgu olarak ele alır ve bileşimi veritabanında yapar. Belgelerdeki örnek, tablo değerli bir fonksiyondan (TVF) seçim yapan bir SQL'in üzerine filtre ve sıralama ekler:

var searchTerm = "Lorem ipsum";

var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToListAsync();

Belgelerin gösterdiği örnek çıktıda SQL'iniz parantez içinde bir alt sorgu olur, arama terimi @p0 parametresiyle gider ve filtre ile sıralama dış sorguya eklenir:

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

Bileşim için SQL'inizin bir alt sorgu olarak geçerli olması, yani "composable" olması gerekir. Bu tür sorgular genellikle SELECT ile başlar ve alt sorguda geçersiz olan şu yapıları içeremez: sondaki noktalı virgül; SQL Server'da sorgunun sonuna eklenen bir sorgu ipucu (örneğin OPTION (HASH JOIN)); SQL Server'da SELECT içinde OFFSET 0 ya da TOP 100 PERCENT ile kullanılmayan bir ORDER BY.

Saklı yordamlar ayrıca ele alınmalıdır. SQL Server, saklı yordam çağrılarının üzerine bileşim yapılmasına izin vermez; böyle bir çağrının üzerine ek sorgu operatörü uygulamak geçersiz SQL üretir. Belgeler, EF Core'un bir saklı yordam üzerine bileşim yapmaya çalışmaması için FromSql ya da FromSqlRaw çağrısının hemen ardından AsEnumerable ya da AsAsyncEnumerable kullanılmasını önerir. Bu durumda sonraki operatörler istemcide, bellekteki sonuçlar üzerinde çalışır; büyük sonuç kümelerinde bunun maliyetini hesaba katın. İstemci tarafı değerlendirmenin genel kuralları client evaluation hatası yazısında.

Include ve takip davranışı

Ham SQL ile başlayan sorgularda da Include ile ilişkili veri yükleyebilirsiniz: FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})").Include(b => b.Posts) gibi. Takip açısından da fark yoktur. Belgelere göre FromSql ya da FromSqlRaw kullanan sorgular EF Core'daki diğer LINQ sorgularıyla birebir aynı change tracking kurallarına uyar; varlık türü döndürüyorlarsa sonuçlar varsayılan olarak takip edilir ve AsNoTracking() ile takip kapatılabilir. İlişkili veri yükleme Include ile eager loading, takip davranışı AsNoTracking ve tracking sorguları yazısında.

SqlQuery: skaler ve eşlenmemiş türler

FromSql modeldeki varlıkları sorgulamak içindir. Varlık olmayan skaler türleri SQL ile sorgulamak için ise Database.SqlQuery kullanılır; böylece daha alt düzey veri erişim API'lerine inmeniz gerekmez. Belgelerdeki SQL Server örneği tüm blog kimliklerini getirir:

var ids = await context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToListAsync();

Bu sorgunun üzerine de LINQ ekleyebilirsiniz, ama bir kural vardır: SQL'iniz bir alt sorguya dönüşeceği ve EF'in eklediği SQL çıktı kolonuna başvurmak zorunda olduğu için kolonun adını Value yapmanız gerekir. Belgelerdeki örnek ortalamanın üzerindeki kimlikleri döndürür:

var overAverageIds = await context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToListAsync();

SqlQuery sağlayıcınızın desteklediği herhangi bir skaler türle kullanılabilir. Sağlayıcının desteklemediği bir tür kullanmak istiyorsanız belgeler, bu tür için bir değer dönüşümü tanımlamak üzere pre-convention yapılandırmasına yönlendirir; değer dönüşümleri value conversion ve enum string yazısında. Dinamik SQL gerektiğinde SqlQueryRaw, tıpkı varlıklar için FromSqlRaw gibi, metnin dinamik olarak kurulmasına izin verir ve aynı dikkat gerektirir.

Eşlenmemiş türler

Belgelere göre SqlQuery ile eşlenmemiş türler döndürmek EF Core 8.0 ile geldi. Skaler türlerin yanında, modelde yer almayan ama eşlenebilir bir CLR türünün örneklerini de SQL sonuç kümesinden doldurabilirsiniz. Tür, parametreli kurucular ve eşleme öznitelikleri gibi EF Core'un desteklediği yaygın eşleme yapılarını içerebilir. Tek koşul, sonuç kümesindeki her değer için bir özelliğinin olmasıdır; veritabanındaki bir tabloyla eşleşmesi gerekmez, kolonların bir alt kümesini ya da birden fazla tablonun join sonucunu temsil edebilir.

public class BlogPost
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateOnly PublishedOn { get; set; }
    public int BlogId { get; set; }
}

var start = new DateOnly(2022, 1, 1);
var end = new DateOnly(2023, 1, 1);
var posts = await context.Database
    .SqlQuery<BlogPost>($"SELECT * FROM Posts AS p WHERE p.PublishedOn >= {start} AND p.PublishedOn < {end}")
    .ToListAsync();

Bu şekilde kullanılan eşlenmemiş türlerin anahtarı yoktur ve başka türlerle ilişki kuramazlar; ilişkisi olan türlerin modelde eşlenmesi gerekir. Belgeler eşlenmemiş sonuç türünün iç içe complex type'lar da içerebileceğini anlatır: örneğin bir Customer içindeki [ComplexType] işaretli Address türünün özellikleri, complex property adı ile iç özellik adının alt çizgiyle birleştirildiği kolonlardan (Address_City gibi) doldurulur. Model içinde ham SQL için sık kullanılan bir başka yapı da anahtarsız (keyless) varlık türleridir: belgelerin anahtarsız türler sayfası, bunların ana kullanım senaryolarından birinin SQL sorgularının dönüş türü olmak olduğunu, ayrıca hiçbir zaman takip edilmediklerini belirtir. Modelleme tarafı model yapılandırma: Fluent API ve data annotations yazısında.

ExecuteSql: veri döndürmeyen komutlar

Veri değiştiren ya da sonuç kümesi döndürmeyen bir saklı yordamı çağıran SQL için Database.ExecuteSql kullanılır. Belgelere göre bu yöntem SQL'i çalıştırır ve değiştirilen satır sayısını döndürür. ExecuteSql, FromSql ile aynı güvenli parametreleme sayesinde SQL injection'a karşı korur; ExecuteSqlRaw ise, FromSqlRaw gibi, SQL metninin dinamik kurulmasına izin verir.

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

Toplu güncelleme ya da silme için SQL yazmadan önce LINQ tabanlı ExecuteUpdateAsync ve ExecuteDeleteAsync yöntemlerine bakmanızı öneririz; bu yöntemler ve change tracker ile ilişkileri ExecuteUpdate ve ExecuteDelete yazısında. Kimlik çözümlemesi belgelerinin hatırlattığı gibi, veritabanında değişen veri, context'in zaten izlediği varlıklara yansımaz; ham SQL ile güncellediğiniz satırları aynı context'te izlenen nesneler üzerinden kullanmaya devam edecekseniz bunları Reload ile yenilemeyi ya da yeni bir context kullanmayı düşünün.

Hangi yöntem ne için?

Belgelerin performans sayfası ham SQL'i son çare olarak görür: ancak EF'in istediğiniz SQL'i üretemediğinden emin olduktan sonra ve o sorgu için performans bunu haklı çıkaracak kadar önemliyse kullanılmalıdır, çünkü ham SQL ciddi bakım dezavantajları getirir. Aynı sayfa alternatif olarak kullanıcı tanımlı fonksiyonları (tablo değerli fonksiyonlar dahil) ve veritabanı view'larını da sayar; view'ların fonksiyonların aksine parametre alamadığını belirtir. Ham SQL kullanmadan önce LINQ sorgunuzun ürettiği SQL'i görmek için SQL loglama yazısına, LINQ'in genel davranışı için EF Core LINQ sorguları yazısına bakın.

Sık yapılan hatalar

• FromSqlRaw'a kullanıcı değerini birleştirerek vermek: SQL injection'ın klasik yoludur. Değerleri her zaman parametre olarak verin; yalnızca doğrulanmış tanımlayıcıları metne ekleyin.
• FromSql'i LINQ'in ortasında çağırmak: Yalnızca DbSet üzerinde başlangıç noktası olarak kullanılabilir.
• Varlığın tüm kolonlarını döndürmemek: Varlık türü döndüren SQL, tüm özellikler için veri içermeli ve kolon adları eşlemeyle uyuşmalıdır.
• Saklı yordam üzerine LINQ eklemek: SQL Server'da geçersiz SQL üretir; araya AsEnumerable ya da AsAsyncEnumerable koyun.
• Bileşim için sonda noktalı virgül bırakmak: Alt sorguda geçersizdir.
• SqlQuery üzerine LINQ eklerken kolonu adlandırmamak: Skaler sorgularda kolon adı Value olmalıdır.

Sık Sorulan Sorular

FromSql ile FromSqlRaw arasındaki fark nedir?

FromSql interpolasyonlu bir FormattableString alır ve her değeri ayrı bir SQL parametresine dönüştürür; bu yüzden SQL injection'a karşı güvenlidir. FromSqlRaw ise metni olduğu gibi kullanır; interpolasyonla eklediğiniz her şey doğrudan SQL'e girer, parametreleri DbParameter olarak ayrıca vermeniz gerekir.

Kolon ya da tablo adını parametre olarak verebilir miyim?

Hayır. Veritabanları şemanın hiçbir parçasının parametreyle verilmesine izin vermez. Bu tür durumlarda FromSqlRaw kullanıp adı güvenli bir listeyle doğrulamanız ya da her seçenek için ayrı bir sorgu yazmanız gerekir.

Ham SQL ile dönen varlıklar değiştirilip kaydedilebilir mi?

Evet. FromSql sorguları diğer LINQ sorgularıyla aynı takip kurallarına uyar; varlık türü döndürüyorsa ve AsNoTracking kullanılmadıysa sonuçlar takip edilir ve değişiklikler SaveChanges ile kaydedilir. Anahtarsız türler ve SqlQuery ile dönen eşlenmemiş türler ise takip edilmez.

EF Core loglarında ham SQL parametrelerinin değerleri görünür mü?

Belgelere göre EF, çalıştırılan SQL'i loglarken parametre değerlerini varsayılan olarak loglamaz; EnableSensitiveDataLogging bunu açar ve yalnızca tanılama amacıyla kullanılmalıdır. EF Core 10 ayrıca SQL'e gömülen (inline) sabitleri de loglarda varsayılan olarak soru işaretiyle gizler. Ayrıntılar SQL loglama: LogTo ve interceptor yazısında.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: EF Core öğrenme rehberi · SQL injection önleme · ExecuteUpdate ve ExecuteDelete · EF Core LINQ sorguları · AsNoTracking ve tracking sorguları