Aracılığıyla paylaş

SQL Sorguları

  • Makale

Entity Framework Core, ilişkisel bir veritabanıyla çalışırken SQL sorgularına açılan menüye geçiş yapmanızı sağlar. İstediğiniz sorgu LINQ kullanılarak ifade edilemiyorsa veya BIR LINQ sorgusu EF'nin verimsiz SQL oluşturmasına neden oluyorsa SQL sorguları kullanışlıdır. SQL sorguları, modelinizin parçası olan normal varlık türlerini veya anahtarsız varlık türlerini döndürebilir.

İpucu

Bu makalenin örneğini GitHub'da görüntüleyebilirsiniz.

Temel SQL sorguları

SQL sorgusunu temel alan bir LINQ sorgusu başlatmak için kullanabilirsiniz FromSql :

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

Not

FromSql EF Core 7.0 ile kullanıma sunulmuştur. Eski sürümleri kullanırken bunun yerine kullanın FromSqlInterpolated .

SQL sorguları, varlık verilerini döndüren saklı yordamı yürütmek için kullanılabilir:

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Not

FromSql yalnızca doğrudan üzerinde DbSetkullanılabilir. Rastgele bir LINQ sorgusu üzerinden oluşturulamaz.

Parametreleri geçirme

Uyarı

SQL sorgularını kullanırken parametreleştirmeye çok dikkat edin

Kullanıcı tarafından sağlanan değerleri bir SQL sorgusuna eklerken SQL ekleme saldırılarını önlemek için dikkatli olunmalıdır. SQL ekleme, bir program kullanıcı tarafından sağlanan dize değerini SQL sorgusuyla tümleştirdiğinde ve kullanıcı tarafından sağlanan değer dizeyi sonlandırmak ve başka bir kötü amaçlı SQL işlemi gerçekleştirmek için tasarlandığında gerçekleşir. SQL ekleme hakkında daha fazla bilgi edinmek için bu sayfaya bakın.

FromSql ve FromSqlInterpolated yöntemleri SQL eklemeye karşı güvenlidir ve parametre verilerini her zaman ayrı bir SQL parametresi olarak tümleştirir. Ancak, FromSqlRaw yöntem yanlış kullanılıyorsa SQL ekleme saldırılarına karşı savunmasız olabilir. Ayrıntılar için aşağıya bakın.

Aşağıdaki örnek, SQL sorgu dizesine bir parametre yer tutucusu ekleyerek ve ek bir bağımsız değişken sağlayarak tek bir parametreyi saklı yordama geçirir:

var user = "johndoe";

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

Bu söz dizimi normal C# dize ilişkilendirmesi gibi görünse de, sağlanan değer bir DbParameter içinde sarmalanır ve oluşturulan parametre adı yer tutucunun {0} belirtildiği yere eklenir. Bu, SQL ekleme saldırılarına karşı güvenli hale getirir FromSql ve değeri verimli ve doğru bir şekilde veritabanına gönderir.

Saklı yordamları yürütürken, özellikle saklı yordamın isteğe bağlı parametreleri olduğunda SQL sorgu dizesinde adlandırılmış parametreleri kullanmak yararlı olabilir:

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

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

Gönderilen veritabanı parametresi üzerinde daha fazla denetime ihtiyacınız varsa, bir DbParameter oluşturabilir ve parametre değeri olarak sağlayabilirsiniz. Bu, parametrenin tam veritabanı türünü veya boyutu, duyarlığı veya uzunluğu gibi modelleri ayarlamanıza olanak tanır:

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

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

Not

Geçirdiğiniz parametreler saklı yordam tanımıyla tam olarak eşleşmelidir. Parametrelerin sıralamasına özellikle dikkat edin, herhangi birini kaçırmamaya veya yanlış yere bırakmamaya dikkat edin veya adlandırılmış parametre gösterimi kullanmayı göz önünde bulundurun. Ayrıca parametre türlerinin karşılık gelip modellerinin (boyut, duyarlık, ölçek) gerektiği gibi ayarlandığından emin olun.

Dinamik SQL ve parametreler

FromSql ve parametreleştirmesi mümkün olan her yerde kullanılmalıdır. Ancak, SQL'in dinamik olarak bir araya alınması gereken ve veritabanı parametrelerinin kullanılamadığı bazı senaryolar vardır. Örneğin, bir C# değişkeninin filtrelenecek özelliğin adını barındırdığını varsayalım. Aşağıdaki gibi bir SQL sorgusu kullanmak cazip olabilir:

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

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

Veritabanları sütun adlarını (veya şemanın başka bir bölümünü) parametreleştirmeye izin vermediğinden bu kod çalışmaz.

İlk olarak, SQL aracılığıyla veya başka bir şekilde dinamik olarak sorgu oluşturmanın etkilerini göz önünde bulundurmak önemlidir. Kullanıcının sütun adını kabul etmek, dizine eklenmemiş bir sütun seçmesine olanak tanıyarak sorgunun son derece yavaş çalışmasını ve veritabanınızı aşırı yüklemesini sağlar; veya kullanıma sunulmasını istemediğiniz verileri içeren bir sütun seçmelerine izin verebilir. Gerçekten dinamik senaryolar dışında, genellikle iki sütun adı için iki sorgu olması daha iyidir; bunları tek bir sorguya daraltmak için parametreleme kullanmak yerine.

SQL'inizi dinamik olarak oluşturmak istediğinize karar verdiyseniz, veritabanı parametresi kullanmak yerine değişken verilerini doğrudan SQL dizesiyle ilişkilendirmeye olanak tanıyan öğesini kullanmanız FromSqlRawgerekir:

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

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

Yukarıdaki kodda sütun adı, C# dize ilişkilendirmesi kullanılarak doğrudan SQL'e eklenir. Bu dize değerinin güvenli olduğundan emin olmak, güvenli olmayan bir kaynaktan geliyorsa temizlendiğinden emin olmak sizin sorumluluğunuzdadır; bu noktalı virgüller, açıklamalar ve diğer SQL yapıları gibi özel karakterleri algılamak ve bunları doğru şekilde kaçmak veya bu tür girişleri reddetmek anlamına gelir.

Öte yandan, sütun değeri aracılığıyla DbParametergönderilir ve bu nedenle SQL eklemesi karşısında güvenlidir.

Uyarı

kullanırken FromSqlRawçok dikkatli olun ve her zaman değerlerin güvenli bir kaynaktan geldiğinden veya düzgün bir şekilde temizlendiğinden emin olun. SQL ekleme saldırıları, uygulamanız için olağanüstü sonuçlara neden olabilir.

LINQ ile oluşturma

LINQ işleçlerini kullanarak ilk SQL sorgusunun üzerine oluşturabilirsiniz; EF Core, SQL'inizi bir alt sorgu olarak ele alır ve veritabanında bunun üzerinde oluşturur. Aşağıdaki örnek, Tablo Değerli İşlev (TVF) arasından seçim yapan bir SQL sorgusu kullanır. Ardından, filtreleme ve sıralama yapmak için LINQ kullanarak bunu oluşturur.

var searchTerm = "Lorem ipsum";

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

Yukarıdaki sorgu aşağıdaki SQL'i oluşturur:

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

işleç Include , diğer LINQ sorgularında olduğu gibi ilgili verileri yüklemek için kullanılabilir:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

LINQ ile oluşturma, SQL sorgunuzun birleştirilebilir olmasını gerektirir, çünkü EF Core sağlanan SQL'i bir alt sorgu olarak ele alır. Birleştirilebilir SQL sorguları genellikle anahtar sözcükle SELECT başlar ve aşağıdakiler gibi bir alt sorguda geçerli olmayan SQL özelliklerini içeremez:

  • Sondaki noktalı virgül
  • SQL Server'da, sondaki sorgu düzeyi ipucu (örneğin, OPTION (HASH JOIN))
  • SQL Server'da, yan tümcesinde SELECT OR TOP 100 PERCENT ile OFFSET 0 kullanılmayan bir ORDER BY yan tümce

SQL Server saklı yordam çağrıları üzerinden oluşturma işlemine izin vermez, bu nedenle bu tür bir çağrıya ek sorgu işleçleri uygulama girişimi geçersiz SQL'e neden olur. AsAsyncEnumerable EF Core'un bir saklı yordam üzerinde oluşturmayı denemediğinden emin olmak için veya hemen sonrasında veya FromSqlRaw sonrasında FromSql kullanınAsEnumerable.

Değişiklik İzleme

EF Core'daki diğer LINQ sorgularında olduğu gibi değişiklik izleme kurallarını kullanan FromSql veya FromSqlRaw izleyen sorgular. Örneğin, sorgu varlık türlerini projelendiriyorsa, sonuçlar varsayılan olarak izlenir.

Aşağıdaki örnek, Tablo Değerli İşlev (TVF) arasından seçim yapan bir SQL sorgusu kullanır ve ardından çağrısıyla AsNoTrackingdeğişiklik izlemeyi devre dışı bırakır:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();

Skaler (varlık olmayan) türleri sorgulama

Not

Bu özellik EF Core 7.0'da kullanıma sunulmuştur.

Modelinizde tanımlanan varlıkları sorgulamak için yararlı olsa FromSql da, SqlQuery daha düşük düzey veri erişim API'lerine açılana gerek kalmadan SQL aracılığıyla skaler, varlık dışı türleri kolayca sorgulamanıza olanak tanır. Örneğin, aşağıdaki sorgu tablodan tüm kimlikleri Blogs getirir:

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

SQL sorgunuz üzerinden LINQ işleçleri de oluşturabilirsiniz. Ancak, SQL'iniz çıkış sütununa SQL EF tarafından başvurulması gereken bir alt sorgu haline geldiğinden, çıkış sütununu Valueolarak adlandırmanız gerekir. Örneğin, aşağıdaki sorgu kimlik ortalamasının üzerinde olan kimlikleri döndürür:

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

FromSql veritabanı sağlayıcınız tarafından desteklenen herhangi bir skaler türle kullanılabilir. Veritabanı sağlayıcınız tarafından desteklenmeyen bir tür kullanmak isterseniz, bunun için bir değer dönüştürme tanımlamak için kural öncesi yapılandırmasını kullanabilirsiniz.

SqlQueryRaw , varlık türleri için olduğu gibi FromSqlRaw SQL sorgularının dinamik olarak oluşturulmasını sağlar.

Sorgulanmayan SQL yürütme

Bazı senaryolarda, genellikle veritabanındaki verileri değiştirmek veya herhangi bir sonuç kümesi döndürmeyen saklı yordamı çağırmak için veri döndürmeyen SQL'in yürütülmesi gerekebilir. Bu işlem aracılığıyla ExecuteSqlyapılabilir:

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

Bu, sağlanan SQL'i yürütür ve değiştirilen satır sayısını döndürür. ExecuteSql , gibi FromSqlgüvenli parametreleştirme kullanarak SQL eklemeye karşı koruma sağlar ve ExecuteSqlRaw sorgularda olduğu gibi FromSqlRaw SQL sorgularının dinamik olarak oluşturulmasını sağlar.

Not

EF Core 7.0'dan önce, yukarıdaki gibi veritabanında "toplu güncelleştirme" yapmak için API'lerin kullanılması ExecuteSql gerekiyordu; bu, eşleşen tüm satırları sorgulamaktan ve sonra bunları değiştirmek için kullanmaktan SaveChanges çok daha verimlidir. EF Core 7.0, LINQ aracılığıyla verimli toplu güncelleştirme işlemlerini ifade etmeye olanak tanıyan ExecuteUpdate ve ExecuteDelete'i kullanıma sunar. Mümkün olduğunda ExecuteSqlyerine bu API'lerin kullanılması önerilir.

Sınırlamalar

SQL sorgularından varlık türlerini döndürürken dikkat edilmesi gereken birkaç sınırlama vardır:

  • SQL sorgusu, varlık türünün tüm özellikleri için veri döndürmelidir.
  • Sonuç kümesindeki sütun adları, özelliklerin eşlendiği sütun adlarla eşleşmelidir. Bu davranışın EF6'dan farklı olduğunu unutmayın; EF6, SQL sorguları için özellik-sütun eşlemesini yoksaydı ve sonuç kümesi sütun adlarının bu özellik adlarına uyması gerekiyordu.
  • SQL sorgusu ilgili verileri içeremez. Ancak çoğu durumda, ilgili verileri döndürmek için işlecini kullanarak sorgunun Include üzerinde oluşturabilirsiniz (bkz . İlgili verileri ekleme).