Kueri SQL

  • Artikel

Entity Framework Core memungkinkan Anda untuk turun ke kueri SQL saat bekerja dengan database relasional. Kueri SQL berguna jika kueri yang Anda inginkan tidak dapat diekspresikan menggunakan LINQ, atau jika kueri LINQ menyebabkan EF menghasilkan SQL yang tidak efisien. Kueri SQL dapat mengembalikan jenis entitas reguler atau jenis entitas tanpa kunci yang merupakan bagian dari model Anda.

Tip

Anda dapat melihat contoh artikel ini di GitHub.

Kueri SQL dasar

Anda bisa menggunakan FromSql untuk memulai kueri LINQ berdasarkan kueri SQL:

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

Catatan

FromSql diperkenalkan dalam EF Core 7.0. Saat menggunakan versi yang lebih lama, gunakan FromSqlInterpolated sebagai gantinya.

Kueri SQL dapat digunakan untuk menjalankan prosedur tersimpan yang mengembalikan data entitas:

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

Catatan

FromSql hanya dapat digunakan langsung pada DbSet. Ini tidak dapat disusun melalui kueri LINQ arbitrer.

Parameter Passing

Peringatan

Perhatikan parameterisasi dengan cerah saat menggunakan kueri SQL

Saat memperkenalkan nilai yang disediakan pengguna ke dalam kueri SQL, perawatan harus dilakukan untuk menghindari serangan injeksi SQL. Injeksi SQL terjadi ketika program mengintegrasikan nilai string yang disediakan pengguna ke dalam kueri SQL, dan nilai yang disediakan pengguna dibuat untuk mengakhiri string dan melakukan operasi SQL berbahaya lainnya. Untuk mempelajari selengkapnya tentang injeksi SQL, lihat halaman ini.

Metode FromSql dan FromSqlInterpolated aman terhadap injeksi SQL, dan selalu mengintegrasikan data parameter sebagai parameter SQL terpisah. Namun, metode ini FromSqlRaw dapat rentan terhadap serangan injeksi SQL, jika digunakan secara tidak benar. Baca di bawah ini untuk detail selengkapnya.

Contoh berikut meneruskan parameter tunggal ke prosedur tersimpan dengan menyertakan tempat penampung parameter dalam string kueri SQL dan memberikan argumen tambahan:

var user = "johndoe";

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

Meskipun sintaks ini mungkin terlihat seperti interpolasi string C# biasa, nilai yang disediakan dibungkus dalam DbParameter dan nama parameter yang dihasilkan dimasukkan di mana {0} tempat penampung ditentukan. Ini membuat FromSql aman dari serangan injeksi SQL, dan mengirim nilai secara efisien dan benar ke database.

Saat menjalankan prosedur tersimpan, akan berguna untuk menggunakan parameter bernama dalam string kueri SQL, terutama ketika prosedur tersimpan memiliki parameter opsional:

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

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

Jika Anda memerlukan kontrol lebih besar atas parameter database yang dikirim, Anda juga dapat membuat DbParameter dan menyediakannya sebagai nilai parameter. Ini memungkinkan Anda untuk mengatur jenis database parameter yang tepat, atau faset seperti ukuran, presisi, atau panjangnya:

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

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

Catatan

Parameter yang Anda lewati harus sama persis dengan definisi prosedur tersimpan. Beri perhatian khusus pada urutan parameter, berhati-hatilah untuk tidak melewatkan atau salah menempatkan salah satu dari mereka - atau pertimbangkan untuk menggunakan notasi parameter bernama. Selain itu, pastikan jenis parameter sesuai, dan bahwa fasetnya (ukuran, presisi, skala) diatur sesuai kebutuhan.

SQL dinamis dan parameter

FromSql dan parameterisasinya harus digunakan sedapat mungkin. Namun, ada skenario tertentu di mana SQL perlu dipotong secara dinamis bersama-sama, dan parameter database tidak dapat digunakan. Misalnya, mari kita asumsikan bahwa variabel C# menyimpan nama properti yang akan difilter. Mungkin menggoda untuk menggunakan kueri SQL seperti berikut ini:

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

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

Kode ini tidak berfungsi, karena database tidak mengizinkan parameterisasi nama kolom (atau bagian lain dari skema).

Pertama, penting untuk mempertimbangkan implikasi membangun kueri secara dinamis - melalui SQL atau sebaliknya. Menerima nama kolom dari pengguna dapat memungkinkan mereka memilih kolom yang tidak diindeks, membuat kueri berjalan sangat lambat dan membebani database Anda; atau mungkin memungkinkan mereka memilih kolom yang berisi data yang tidak ingin Anda ekspos. Kecuali untuk skenario yang benar-benar dinamis, biasanya lebih baik memiliki dua kueri untuk dua nama kolom, daripada menggunakan parameterisasi untuk menciutkannya ke dalam satu kueri.

Jika Anda telah memutuskan ingin membangun SQL secara dinamis, Anda harus menggunakan FromSqlRaw, yang memungkinkan interpolasi data variabel langsung ke dalam string SQL, alih-alih menggunakan parameter database:

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

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

Dalam kode di atas, nama kolom dimasukkan langsung ke dalam SQL, menggunakan interpolasi string C#. Anda bertanggung jawab untuk memastikan nilai string ini aman, membersihkannya jika berasal dari asal yang tidak aman; ini berarti mendeteksi karakter khusus seperti titik koma, komentar, dan konstruksi SQL lainnya, dan melarikan diri dengan benar atau menolak input tersebut.

Di sisi lain, nilai kolom dikirim melalui DbParameter, dan oleh karena itu aman dalam menghadapi injeksi SQL.

Peringatan

Berhati-hatilah saat menggunakan FromSqlRaw, dan selalu pastikan nilai berasal dari asal yang aman, atau dibersihkan dengan benar. Serangan injeksi SQL dapat memiliki konsekuensi bencana untuk aplikasi Anda.

Menyusun dengan LINQ

Anda dapat menyusun di atas kueri SQL awal menggunakan operator LINQ; EF Core akan memperlakukan SQL Anda sebagai subkueri dan menyusunnya dalam database. Contoh berikut menggunakan kueri SQL yang memilih dari Fungsi Bernilai Tabel (TVF). Lalu menyusunnya menggunakan LINQ untuk melakukan pemfilteran dan pengurutan.

var searchTerm = "Lorem ipsum";

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

Kueri di atas menghasilkan SQL berikut:

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

Operator Include dapat digunakan untuk memuat data terkait, sama seperti kueri LINQ lainnya:

var searchTerm = "Lorem ipsum";

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

Membuat dengan LINQ mengharuskan kueri SQL Anda dapat dikomposisikan, karena EF Core akan memperlakukan SQL yang disediakan sebagai subkueri. Kueri SQL yang dapat dikomposisi umumnya dimulai dengan SELECT kata kunci, dan tidak dapat berisi fitur SQL yang tidak valid dalam subkueri, seperti:

  • Titik koma berikutnya
  • Di SQL Server, petunjuk tingkat kueri berikutnya (misalnya, OPTION (HASH JOIN))
  • Di SQL Server, ORDER BY klausa yang tidak digunakan dengan OFFSET 0 OR TOP 100 PERCENT dalam SELECT klausul

SQL Server tidak mengizinkan pembuatan melalui panggilan prosedur tersimpan, sehingga setiap upaya untuk menerapkan operator kueri tambahan ke panggilan tersebut akan mengakibatkan SQL tidak valid. Gunakan AsEnumerable atau AsAsyncEnumerable tepat setelah FromSql atau FromSqlRaw untuk memastikan bahwa EF Core tidak mencoba menyusun prosedur tersimpan.

Pelacakan Perubahan

Kueri yang menggunakan FromSql atau FromSqlRaw mengikuti aturan pelacakan perubahan yang sama persis dengan kueri LINQ lainnya di EF Core. Misalnya, jika kueri memproyeksikan jenis entitas, hasilnya dilacak secara default.

Contoh berikut menggunakan kueri SQL yang memilih dari Fungsi Bernilai Tabel (TVF), lalu menonaktifkan pelacakan perubahan dengan panggilan ke AsNoTracking:

var searchTerm = "Lorem ipsum";

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

Mengkueri jenis skalar (non-entitas)

Catatan

Fitur ini diperkenalkan dalam EF Core 7.0.

Meskipun FromSql berguna untuk mengkueri entitas yang ditentukan dalam model Anda, SqlQuery memungkinkan Anda untuk dengan mudah mengkueri jenis skalar dan non-entitas melalui SQL, tanpa perlu turun ke API akses data tingkat bawah. Misalnya, kueri berikut mengambil semua ID dari Blogs tabel:

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

Anda juga dapat menyusun operator LINQ melalui kueri SQL Anda. Namun, karena SQL Anda menjadi subkueri yang kolom outputnya perlu direferensikan oleh penambahan SQL EF, Anda harus memberi nama kolom Valueoutput . Misalnya, kueri berikut mengembalikan ID yang berada di atas rata-rata ID:

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

FromSql dapat digunakan dengan jenis skalar apa pun yang didukung oleh penyedia database Anda. Jika Anda ingin menggunakan jenis yang tidak didukung oleh penyedia database, Anda dapat menggunakan konfigurasi pra-konvensi untuk menentukan konversi nilai untuknya.

SqlQueryRaw memungkinkan konstruksi dinamis kueri SQL, seperti FromSqlRaw halnya untuk jenis entitas.

Menjalankan SQL yang tidak mengkueri

Dalam beberapa skenario, mungkin perlu untuk menjalankan SQL yang tidak mengembalikan data apa pun, biasanya untuk memodifikasi data dalam database atau memanggil prosedur tersimpan yang tidak mengembalikan kumpulan hasil apa pun. Ini dapat dilakukan melalui ExecuteSql:

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

Ini menjalankan SQL yang disediakan dan mengembalikan jumlah baris yang dimodifikasi. ExecuteSql melindungi dari injeksi SQL dengan menggunakan parameterisasi yang aman, sama seperti FromSql, dan ExecuteSqlRaw memungkinkan konstruksi dinamis kueri SQL, seperti FromSqlRaw halnya untuk kueri.

Catatan

Sebelum EF Core 7.0, terkadang perlu menggunakan ExecuteSql API untuk melakukan "pembaruan massal" pada database, seperti di atas; ini jauh lebih efisien daripada mengkueri semua baris yang cocok dan kemudian menggunakan SaveChanges untuk memodifikasinya. EF Core 7.0 memperkenalkan ExecuteUpdate dan ExecuteDelete, yang memungkinkan untuk mengekspresikan operasi pembaruan massal yang efisien melalui LINQ. Disarankan untuk menggunakan API tersebut jika memungkinkan, bukan ExecuteSql.

Pembatasan

Ada beberapa batasan yang perlu diperhatikan saat mengembalikan jenis entitas dari kueri SQL:

  • Kueri SQL harus mengembalikan data untuk semua properti jenis entitas.
  • Nama kolom dalam tataan hasil harus cocok dengan nama kolom tempat properti dipetakan. Perhatikan bahwa perilaku ini berbeda dari EF6; EF6 mengabaikan pemetaan properti-ke-kolom untuk kueri SQL, dan nama kolom kumpulan hasil harus cocok dengan nama properti tersebut.
  • Kueri SQL tidak boleh berisi data terkait. Namun, dalam banyak kasus Anda dapat menyusun di atas kueri menggunakan Include operator untuk mengembalikan data terkait (lihat Menyertakan data terkait).