Bagikan melalui

Ketidakcocokan Jenis SQL-CLR

  • Artikel

LINQ to SQL mengotomatiskan banyak terjemahan antara model objek dan SQL Server. Namun demikian, beberapa situasi mencegah terjemahan yang tepat. Ketidakcocokan kunci ini antara jenis runtime bahasa umum (CLR) dan jenis database SQL Server diringkas di bagian berikut. Anda dapat menemukan detail selengkapnya tentang pemetaan jenis dan terjemahan fungsi tertentu di SQL-CLR Type Mapping dan Data Type and Functions.

Jenis Data

Terjemahan antara CLR dan SQL Server terjadi saat kueri dikirim ke database, dan ketika hasilnya dikirim kembali ke model objek Anda. Misalnya, kueri transact-SQL berikut memerlukan dua konversi nilai:

Select DateOfBirth From Customer Where CustomerId = @id

Sebelum kueri dapat dijalankan pada SQL Server, nilai untuk parameter Transact-SQL harus ditentukan. Dalam contoh ini, nilai parameter id harus terlebih dahulu diterjemahkan dari jenis CLR System.Int32 ke jenis SQL Server INT sehingga database dapat memahami apa nilainya. Kemudian untuk mengambil hasilnya, kolom SQL Server DateOfBirth harus diterjemahkan dari tipe SQL Server DATETIME ke jenis runtime bahasa umum System.DateTime untuk digunakan dalam model objek. Dalam contoh ini, jenis dalam model objek CLR dan database SQL Server memiliki pemetaan alami. Tapi, ini tidak selalu terjadi.

Rekan-rekan yang Hilang

Jenis berikut tidak memiliki rekan yang wajar.

  • Ketidakcocokan di namespace layanan runtime bahasa umum System:

    • Bilangan bulat tidak bertanda. Jenis-jenis ini biasanya dipetakan ke rekan-rekan yang ditandatangani dengan ukuran yang lebih besar untuk menghindari luapan. Literal dapat dikonversi ke numerik yang ditandatangani dengan ukuran yang sama atau lebih kecil, berdasarkan nilai.

    • Boolean. Jenis-jenis ini dapat dipetakan ke sedikit atau numerik atau string yang lebih besar. Harfiah dapat dipetakan ke ekspresi yang mengevaluasi ke nilai yang sama (misalnya, 1=1 dalam SQL untuk True di CLS).

    • TimeSpan. Jenis ini menunjukkan perbedaan antara dua nilai DateTime dan tidak sesuai dengan timestamp SQL Server. System.TimeSpan CLR juga dapat memetakan ke jenis SQL Server TIME dalam beberapa kasus. Jenis SQL Server TIME hanya dimaksudkan untuk mewakili nilai positif kurang dari 24 jam. TimeSpan CLR memiliki rentang yang jauh lebih besar.

    Catatan

    SQL Server jenis .NET Framework khusus di System.Data.SqlTypes tidak disertakan dalam perbandingan ini.

  • Ketidakcocokan dalam SQL Server:

    • Jenis karakter panjang tetap. Transact-SQL membedakan antara kategori Unicode dan non-Unicode dan memiliki tiga jenis yang berbeda di setiap kategori: panjang tetap nchar/char, panjang variabel nvarchar/varchar, dan berukuran lebih besar ntext/text. Jenis karakter dengan panjang tetap dapat dipetakan ke jenis CLR System.Char untuk mengambil karakter, tetapi tidak benar-benar sesuai dengan jenis yang sama dalam konversi dan perilaku.

    • Bit. Meskipun domain bit memiliki jumlah nilai yang sama dengan Nullable<Boolean>, keduanya adalah jenis yang berbeda. Bit mengambil nilai 1 dan 0 sebagai ganti true/false, dan tidak dapat digunakan sebagai persamaan dengan ekspresi Boolean.

    • Stempel waktu. Tidak seperti jenis System.TimeSpan CLR, jenis SQL Server TIMESTAMP mewakili angka 8-byte yang dihasilkan oleh database yang unik untuk setiap pembaruan dan tidak didasarkan pada perbedaan antara nilai DateTime.

    • Money dan SmallMoney. Jenis ini dapat dipetakan ke Decimal tetapi pada dasarnya jenis yang berbeda dan diperlakukan seperti itu oleh fungsi dan konversi berbasis server.

Beberapa Pemetaan

Ada banyak jenis data SQL Server yang dapat Anda petakan ke satu atau beberapa jenis data CLR. Ada juga banyak jenis CLR yang dapat Anda petakan ke satu atau lebih jenis SQL Server. Meskipun pemetaan dapat didukung oleh LINQ to SQL, itu tidak berarti bahwa kedua jenis yang dipetakan antara CLR dan SQL Server adalah kecocokan sempurna dalam presisi, rentang, dan semantik. Beberapa pemetaan mungkin mencakup perbedaan dalam salah satu atau semua dimensi ini. Anda dapat menemukan detail tentang perbedaan potensial ini untuk berbagai kemungkinan pemetaan di Pemetaan Jenis SQL-CLR.

Jenis yang ditentukan pengguna

Jenis CLR yang ditentukan pengguna dirancang untuk membantu menjembatani kesenjangan sistem jenis. Namun demikian, mereka memunculkan masalah menarik tentang penerapan versi jenis. Perubahan versi pada klien mungkin tidak cocok dengan perubahan jenis yang disimpan di server database. Setiap perubahan tersebut menyebabkan jenis lain tidak cocok di mana semantik jenis mungkin tidak cocok dan kesenjangan versi kemungkinan akan terlihat. Komplikasi lebih lanjut terjadi karena hierarki pewarisan direfaktorkan dalam versi berturut-turut.

Semantik Ekspresi

Selain ketidakcocokan berpasangan antara CLR dan jenis database, ekspresi menambah kompleksitas pada ketidakcocokan. Ketidakcocokan dalam semantik operator, semantik fungsi, konversi jenis implisit, dan aturan prioritas harus dipertimbangkan.

Sub bagian berikut mengilustrasikan ketidakcocokan antara ekspresi yang rupanya mirip. Dimungkinkan untuk menghasilkan ekspresi SQL yang secara semantik setara dengan ekspresi CLR tertentu. Namun, tidak jelas apakah perbedaan semantik antara ekspresi yang tampaknya mirip terbukti oleh pengguna CLR, dan oleh karena itu apakah perubahan yang diperlukan untuk kesetaraan semantik dimaksudkan atau tidak. Ini adalah masalah yang sangat penting ketika ekspresi dievaluasi untuk sekumpulan nilai. Visibilitas perbedaan mungkin bergantung pada data- dan sulit diidentifikasi selama pengodean dan penelusuran kesalahan.

Semantik Null

SQL ekspresi menyediakan logika tiga nilai untuk ekspresi Boolean. Hasilnya bisa true, false, atau null. Sebaliknya, CLR menentukan hasil Boolean dua nilai untuk perbandingan yang melibatkan nilai null. Pertimbangkan gambar berikut:

Nullable<int> i = null;
Nullable<int> j = null;
if (i == j)
{
    // This branch is executed.
}

Dim i? As Integer = Nothing
Dim j? As Integer = Nothing
If i = j Then
    '  This branch is executed.
End If

-- Assume col1 and col2 are integer columns with null values.
-- Assume that ANSI null behavior has not been explicitly
--  turned off.
Select …
From …
Where col1 = col2
-- Evaluates to null, not true and the corresponding row is not
--   selected.
-- To obtain matching behavior (i -> col1, j -> col2) change
--   the query to the following:
Select …
From …
Where
    col1 = col2
or (col1 is null and col2 is null)
-- (Visual Basic 'Nothing'.)

Masalah serupa terjadi dengan asumsi tentang hasil dua nilai.

if ((i == j) || (i != j)) // Redundant condition.
{
    // ...
}

If (i = j) Or (i <> j) Then ' Redundant condition.
    ' ...
End If

-- Assume col1 and col2 are nullable columns.
-- Assume that ANSI null behavior has not been explicitly
--   turned off.
Select …
From …
Where
    col1 = col2
or col1 != col2
-- Visual Basic: col1 <> col2.

-- Excludes the case where the boolean expression evaluates
--   to null. Therefore the where clause does not always
--   evaluate to true.

Dalam kasus sebelumnya, Anda bisa mendapatkan perilaku yang setara dalam menghasilkan SQL, tetapi terjemahannya mungkin tidak secara akurat mencerminkan niat Anda.

LINQ to SQL tidak memberlakukan semantik perbandingan C# null atau Visual Basic nothing pada SQL. Operator perbandingan secara sintaksis diterjemahkan ke SQL yang setara. Semantik mencerminkan semantik SQL seperti yang didefinisikan oleh pengaturan server atau koneksi. Dua nilai null dianggap tidak sama di bawah pengaturan SQL Server default (meskipun Anda dapat mengubah pengaturan untuk mengubah semantik). Terlepas dari itu, LINQ to SQL tidak mempertimbangkan pengaturan server dalam terjemahan kueri.

Perbandingan dengan harfiah null (nothing) diterjemahkan ke versi SQL yang sesuai (is null atau is not null).

Nilai null (nothing) dalam kolab ditentukan oleh SQL Server; LINQ to SQL tidak mengubah kolab.

Konversi dan Promosi Jenis

SQL mendukung serangkaian konversi implisit yang kaya dalam ekspresi. Ekspresi serupa dalam C# akan memerlukan pemeran eksplisit. Misalnya:

  • Jenis Nvarchar dan DateTime dapat dibandingkan dalam SQL tanpa gips eksplisit apa pun; C# membutuhkan konversi eksplisit.

  • Decimal secara implisit dikonversi ke DateTime dalam SQL. C# tidak mengizinkan konversi implisit.

Demikian juga, jenis yang diutamakan dalam Transact-SQL berbeda dari jenis prioritas di C# karena set jenis yang mendasarinya berbeda. Bahkan, tidak ada hubungan subset/superset yang jelas antara daftar prioritas. Misalnya, membandingkan nvarchar dengan varchar menyebabkan konversi implisit dari ekspresi varchar menjadi nvarchar. Runtime bahasa umum tidak memberikan promosi yang setara.

Dalam kasus sederhana, perbedaan ini menyebabkan ekspresi runtime bahasa umum dengan transmisi berlebihan untuk ekspresi SQL yang sesuai. Lebih penting lagi, hasil perantara dari ekspresi SQL mungkin secara implisit dipromosikan ke jenis yang tidak memiliki rekan yang akurat di C#, dan sebaliknya. Secara keseluruhan, pengujian, penelusuran kesalahan, dan validasi ekspresi tersebut menambah beban yang signifikan pada pengguna.

Kolase

Transact-SQL mendukung kolase eksplisit sebagai anotasi untuk jenis string karakter. Kolab ini menentukan validitas perbandingan tertentu. Misalnya, membandingkan dua kolom dengan kolase eksplisit yang berbeda adalah kesalahan. Penggunaan banyak jenis string CTS yang disederhanakan tidak menyebabkan kesalahan tersebut. Pertimbangkan contoh berikut:

create table T2 (
    Col1 nvarchar(10),
    Col2      nvarchar(10) collate Latin_general_ci_as
)

class C
{
string s1;       // Map to T2.Col1.
string s2;       // Map to T2.Col2.

    void Compare()
    {
        if (s1 == s2) // This is correct.
        {
            // ...
        }
    }
}

Class C
    Dim s1 As String    ' Map to T2.Col1.
    Dim s2 As String    ' Map to T2.Col2.
    Sub Compare()
        If s1 = s2 Then ' This is correct.
            ' ...
        End If
    End Sub
End Class

Select …
From …
Where Col1 = Col2
-- Error, collation conflict.

Akibatnya, subklausul kolase membuat jenis terbatas yang tidak dapat diganti.

Demikian pula, urutan pengurutan dapat secara signifikan berbeda di seluruh sistem jenis. Perbedaan ini memengaruhi pengurutan hasil. Guid diurutkan pada semua 16 byte menurut urutan leksikografis ( IComparable() ), sedangkan T-SQL membandingkan GUID dalam urutan berikut: node(10-15), clock-seq(8-9), time-high(6 -7), waktu-pertengahan (4-5), waktu-rendah (0-3). Pemesanan ini dilakukan pada SQL 7.0 ketika GUID yang dihasilkan NT memiliki urutan oktet seperti itu. Pendekatan ini memastikan bahwa GUID yang dihasilkan pada kluster node yang sama disatukan dalam urutan berurutan sesuai dengan tanda waktu. Pendekatan ini juga berguna untuk membangun indeks (sisipan menjadi penambahan daripada IO acak). Perintah ini kemudian diacak di Windows karena masalah privasi, tetapi SQL harus menjaga kompatibilitas. Solusinya adalah menggunakan SqlGuid daripada Guid.

Perbedaan Operator dan Fungsi

Operator dan fungsi yang pada dasarnya sebanding memiliki semantik yang sangat berbeda. Misalnya:

  • C# menentukan semantik hubung singkat berdasarkan urutan leksikal operan untuk operator logika && dan ||. SQL di sisi lain ditargetkan untuk kueri berbasis set dan oleh karena itu memberikan lebih banyak kebebasan bagi pengoptimal untuk memutuskan urutan eksekusi. Beberapa implikasinya meliputi hal-hal berikut:

    • Terjemahan yang setara secara semantik akan memerlukan "CASE ... WHEN … THEN" buat dalam SQL untuk menghindari penyusunan ulang eksekusi operan.

    • Terjemahan longgar ke operator AND/OR dapat menyebabkan kesalahan tak terduga jika ekspresi C# bergantung pada evaluasi operan kedua yang didasarkan pada hasil evaluasi operan pertama.

  • Fungsi Round() memiliki semantik yang berbeda di .NET Framework dan di T-SQL.

  • Indeks awal untuk string adalah 0 di CLR tetapi 1 dalam SQL. Oleh karena itu, fungsi apa pun yang memiliki indeks membutuhkan terjemahan indeks.

  • Runtuime bahasa umum mendukung operator modulus ('%') untuk angka titik mengambang tetapi SQL tidak.

  • Operator Like secara efektif memperoleh kelebihan beban otomatis berdasarkan konversi implisit. Meskipun operator Like didefinisikan untuk beroperasi pada jenis string karakter, konversi implisit dari jenis numerik atau jenis DateTime memungkinkan jenis non-string tersebut digunakan dengan Like juga. Dalam CTS, konversi implisit yang sebanding tidak ada. Oleh karena itu, kelebihan beban tambahan diperlukan.

    Catatan

    Perilaku operator Like ini hanya berlaku untuk C#; kata kunci Visual Basic Like tidak berubah.

  • Overflow selalu dicek masuk SQL tetapi harus secara eksplisit ditentukan dalam C# (tidak dalam Visual Basic) untuk menghindari wraparound. Kolom bilangan bulat yang diberikan C1, C2 dan C3, jika C1+C2 disimpan di C3 (Perbarui T Set C3 = C1 + C2).

    create table T3 (
    Col1      integer,
    Col2      integer
)
insert into T3 (col1, col2) values (2147483647, 5)
-- Valid values: max integer value and 5.
select * from T3 where col1 + col2 < 0
-- Produces arithmetic overflow error.

// C# overflow in absence of explicit checks.
int i = Int32.MaxValue;
int j = 5;
if (i+j < 0) Console.WriteLine("Overflow!");
// This code prints the overflow message.

' Does not apply.
' Visual Basic overflow in absence of implicit check
' (turn off overflow checks in compiler options)
Dim I As Integer = Int32.MaxValue
Dim j As Integer = 5
If I + j < 0 Then
    ' This code prints the overflow message.
    Console.WriteLine("Overflow!")
End If

  • SQL melakukan pembulatan aritmatika simetris sementara .NET Framework menggunakan pembulatan bankir. Lihat artikel Knowledgebase 196652 untuk detail tambahan.

  • Secara default, untuk lokal umum, perbandingan string karakter tidak peka huruf besar/kecil dalam SQL. Dalam Visual Basic dan di C#, mereka peka huruf besar/kecil. Misalnya, s == "Food" (s = "Food" dalam Visual Basic) dan s == "Food" dapat menghasilkan hasil yang berbeda jika s adalah food.

    -- Assume default US-English locale (case insensitive).
create table T4 (
    Col1      nvarchar (256)
)
insert into T4 values ('Food')
insert into T4 values ('FOOD')
select * from T4 where Col1 = 'food'
-- Both the rows are returned because of case-insensitive matching.

// C# equivalent on collections of Strings in place of nvarchars.
String[] strings = { "food", "FOOD" };
foreach (String s in strings)
{
    if (s == "food")
    {
        Console.WriteLine(s);
    }
}
// Only "food" is returned.

' Visual Basic equivalent on collections of Strings in place of
' nvarchars.
Dim strings() As String = {"food", "FOOD"}
For Each s As String In strings
    If s = "food" Then
        Console.WriteLine(s)
    End If
Next
' Only "food" is returned.

  • Operator/fungsi yang diterapkan ke argumen jenis karakter panjang tetap dalam SQL memiliki semantik yang berbeda secara signifikan daripada operator/fungsi yang sama yang diterapkan ke CLR System.String. Ini juga dapat dilihat sebagai perpanjangan dari masalah mitra yang hilang yang dibahas di bagian tentang jenis.

    create table T4 (
    Col1      nchar(4)
)
Insert into T5(Col1) values ('21');
Insert into T5(Col1) values ('1021');
Select * from T5 where Col1 like '%1'
-- Only the second row with Col1 = '1021' is returned.
-- Not the first row!

    // Assume Like(String, String) method.
string s = ""; // map to T4.Col1
if (System.Data.Linq.SqlClient.SqlMethods.Like(s, "%1"))
{
    Console.WriteLine(s);
}
// Expected to return true for both "21" and "1021"

    ' Assume Like(String, String) method.
Dim s As String    ' Map to T4.Col1.
If s Like (System.Data.Linq.SqlClient.SqlMethods.Like(s, "%1")) Then
    Console.WriteLine(s)
End If
' Expected to return true for both "21" and "1021".

    Masalah serupa terjadi dengan perangkaian string.

    create table T6 (
    Col1      nchar(4)
    Col2       nchar(4)
)
Insert into T6 values ('a', 'b');
Select Col1+Col2 from T6
-- Returns concatenation of padded strings "a   b   " and not "ab".

Singkatnya, terjemahan berbelit mungkin diperlukan untuk ekspresi runtime bahasa umum dan operator/fungsi tambahan mungkin diperlukan untuk mengekspos fungsionalitas SQL.

Jenis Transmisi

Di C# dan di SQL, pengguna dapat mengambil alih semantik ekspresi default dengan menggunakan jenis eksplisit yang ditransmisikan (Cast dan Convert). Namun, mengekspos kemampuan ini di seluruh batas sistem jenis menimbulkan dilema. Transmisi SQL yang menyediakan semantik yang diinginkan tidak dapat dengan mudah diterjemahkan ke transmisi C# yang sesuai. Di sisi lain, transmisi C# tidak dapat langsung diterjemahkan ke dalam SQL yang setara karena ketidakcocokan jenis, mitra yang hilang, dan hierarki prioritas jenis yang berbeda. Ada trade-off antara mengekspos ketidakcocokan sistem jenis dan kehilangan kekuatan ekspresi yang signifikan.

Dalam kasus lain, transmisi jenis mungkin tidak diperlukan di salah satu domain untuk validasi ekspresi tetapi mungkin diperlukan untuk memastikan bahwa pemetaan non-default diterapkan dengan benar ke ekspresi.

-- Example from "Non-default Mapping" section extended
create table T5 (
    Col1      nvarchar(10),
    Col2      nvarchar(10)
)
Insert into T5(col1, col2) values ('3', '2');

class C
{
    int x;        // Map to T5.Col1.
    int y;        // Map to T5.Col2.

    void Casting()
    {
        // Intended predicate.
        if (x + y > 4)
        {
            // valid for the data above
        }
    }
}

Class C
    Dim x As Integer        ' Map to T5.Col1.
    Dim y As Integer        ' Map to T5.Col2.

    Sub Casting()
        ' Intended predicate.
        If (x + y) > 4 Then
            ' Valid for the data above.
        End If
    End Sub
End Class

Select *
From T5
Where Col1 + Col2 > 4
-- "Col1 + Col2" expr evaluates to '32'

Masalah Performa

Akuntansi untuk beberapa perbedaan jenis SQL Server-CLR dapat mengakibatkan penurunan performa saat melintasi antara sistem jenis runtime bahasa umum dan SQL Server. Contoh skenario yang memengaruhi performa meliputi berikut ini:

  • Urutan evaluasi paksa untuk logika dan/atau operator

  • Menghasilkan SQL untuk menegakkan urutan evaluasi predikat membatasi kemampuan pengoptimal SQL.

  • Konversi jenis, baik yang diperkenalkan oleh pengompilasi CLR atau oleh implementasi kueri Object-Relational, dapat mengumpulkan penggunaan indeks.

    Contohnya,

    -- Table DDL
create table T5 (
    Col1      varchar(100)
)

    class C5
{
    string s;        // Map to T5.Col1.
}

    Class C5
    Dim s As String ' Map to T5.Col1.
End Class

    Pertimbangkan terjemahan ekspresi (s = SOME_STRING_CONSTANT).

    -- Corresponding part of SQL where clause
Where …
Col1 = SOME_STRING_CONSTANT
-- This expression is of the form <varchar> = <nvarchar>.
-- Hence SQL introduces a conversion from varchar to nvarchar,
--   resulting in
Where …
Convert(nvarchar(100), Col1) = SOME_STRING_CONSTANT
-- Cannot use the index for column Col1 for some implementations.

Selain perbedaan semantik, penting untuk mempertimbangkan dampak pada performa saat melintasi antara sistem jenis SQL Server dan runtime bahasa umum. Untuk himpunan data besar, masalah performa tersebut dapat menentukan apakah aplikasi dapat disebarkan.

Lihat juga