Pemetaan fungsi yang ditentukan pengguna

EF Core memungkinkan penggunaan fungsi SQL yang ditentukan pengguna dalam kueri. Untuk melakukannya, fungsi perlu dipetakan ke metode CLR selama konfigurasi model. Saat menerjemahkan kueri LINQ ke SQL, fungsi yang ditentukan pengguna dipanggil alih-alih fungsi CLR yang telah dipetakan.

Memetakan metode ke fungsi SQL

Untuk mengilustrasikan cara kerja pemetaan fungsi yang ditentukan pengguna, mari kita tentukan entitas berikut:

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int? Rating { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int Rating { get; set; }
    public int BlogId { get; set; }

    public Blog Blog { get; set; }
    public List<Comment> Comments { get; set; }
}

public class Comment
{
    public int CommentId { get; set; }
    public string Text { get; set; }
    public int Likes { get; set; }
    public int PostId { get; set; }

    public Post Post { get; set; }
}

Dan konfigurasi model berikut:

modelBuilder.Entity<Blog>()
    .HasMany(b => b.Posts)
    .WithOne(p => p.Blog);

modelBuilder.Entity<Post>()
    .HasMany(p => p.Comments)
    .WithOne(c => c.Post);

Blog dapat memiliki banyak postingan dan setiap postingan dapat memiliki banyak komentar.

Selanjutnya, buat fungsi CommentedPostCountForBlogyang ditentukan pengguna , yang mengembalikan jumlah posting dengan setidaknya satu komentar untuk blog tertentu, berdasarkan blog Id:

CREATE FUNCTION dbo.CommentedPostCountForBlog(@id int)
RETURNS int
AS
BEGIN
    RETURN (SELECT COUNT(*)
        FROM [Posts] AS [p]
        WHERE ([p].[BlogId] = @id) AND ((
            SELECT COUNT(*)
            FROM [Comments] AS [c]
            WHERE [p].[PostId] = [c].[PostId]) > 0));
END

Untuk menggunakan fungsi ini di EF Core, kami menentukan metode CLR berikut, yang kami petakan ke fungsi yang ditentukan pengguna:

public int ActivePostCountForBlog(int blogId)
    => throw new NotSupportedException();

Isi dari metode CLR tidak penting. Metode ini tidak akan dipanggil sisi klien, kecuali EF Core tidak dapat menerjemahkan argumennya. Jika argumen dapat diterjemahkan, EF Core hanya peduli tentang tanda tangan metode.

Nota

Dalam contoh, metode didefinisikan pada , tetapi juga dapat didefinisikan DbContextsebagai metode statis di dalam kelas lain.

Definisi fungsi ini sekarang dapat dikaitkan dengan fungsi yang ditentukan pengguna dalam konfigurasi model:

modelBuilder.HasDbFunction(() => ActivePostCountForBlog(default))
    .HasName("CommentedPostCountForBlog")
    .HasSchema("dbo");

Overload lambda dari HasDbFunction menghindari keharusan mencari MethodInfo secara manual. Nilai default argumen hanya digunakan untuk mengidentifikasi metode; nilai tersebut tidak pernah dikirim ke database.

Secara default, EF Core memetakan metode CLR ke fungsi database dengan nama yang sama dalam skema default. Gunakan HasName dan HasSchema ketika nama atau skema berbeda.

Sekarang, jalankan kueri berikut:

var query1 = from b in context.Blogs
             where context.ActivePostCountForBlog(b.BlogId) > 1
             select b;

Akan menghasilkan SQL ini:

SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1

Memetakan metode ke fungsi bawaan

EF Core menganggap fungsi yang dipetakan ditentukan pengguna secara default. Beberapa database membedakan fungsi bawaan dan yang ditentukan pengguna saat menghasilkan SQL. Misalnya, SQL Server mengharuskan fungsi yang ditentukan pengguna memenuhi syarat skema, tetapi fungsi bawaan tidak memenuhi syarat skema.

Gunakan IsBuiltIn untuk memetakan metode CLR ke fungsi bawaan:

public static int IsDate(string value)
    => throw new NotSupportedException();

modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(IsDate), [typeof(string)]))
    .HasName("ISDATE")
    .IsBuiltIn();

Properti IsBuiltIn menyediakan konfigurasi yang sama saat menggunakan atribut:

[DbFunction(Name = "ISDATE", IsBuiltIn = true)]

Memetakan fungsi menggunakan DbFunctionAttribute

Alih-alih mendaftarkan fungsi di OnModelCreating, metode statis yang dideklarasikan pada DbContext dapat dipetakan secara langsung dengan menerapkan DbFunctionAttribute. Properti Name, Schema, IsBuiltIn, dan IsNullable milik atribut tersebut mengonfigurasi karakteristik fungsi database yang bersesuaian; ini adalah karakteristik yang sama dengan yang dikonfigurasi oleh metode fluent API HasName, HasSchema, IsBuiltIn, dan IsNullable saat menggunakan HasDbFunction. Metode yang diatribusikan pada konteks ditemukan dan didaftarkan secara otomatis; metode atribut pada kelas lain masih harus didaftarkan dengan HasDbFunction. Panggil HasDbFunction untuk metode yang terdaftar secara otomatis hanya jika builder diperlukan untuk konfigurasi fluent tambahan, seperti pada contoh tipe penyimpanan di bawah ini.

Misalnya, metode berikut menggunakan DbFunctionAttribute untuk memetakan fungsi bawaan JSON_VALUE SQL Server. Karena IsBuiltIn adalah true, EF Core memancarkan nama fungsi tanpa skema.

[DbFunction(Name = "JSON_VALUE", IsBuiltIn = true, IsNullable = true)]
public static string JsonValue(Dictionary<string, string> json, string path)
    => throw new NotSupportedException();

Mengonfigurasi jenis penyimpanan

Gunakan HasStoreType untuk mengonfigurasi jenis penyimpanan pengembalian fungsi dan HasStoreType untuk mengonfigurasi jenis penyimpanan parameter. Ini sangat berguna ketika jenis parameter CLR tidak memiliki pemetaan database asli.

Dalam contoh ini, JsonEntity.Metadata adalah kamus yang disimpan sebagai nvarchar(max) melalui pengonversi nilai. Parameter fungsi json memiliki tipe penyimpanan yang sama, sedangkan hasilnya menggunakan tipe nvarchar(4000) yang dikembalikan oleh JSON_VALUE:

modelBuilder.Entity<JsonEntity>()
    .Property(e => e.Metadata)
    .HasConversion(
        value => JsonSerializer.Serialize(value, (JsonSerializerOptions)null),
        value => JsonSerializer.Deserialize<Dictionary<string, string>>(value, (JsonSerializerOptions)null),
        new ValueComparer<Dictionary<string, string>>(
            (c1, c2) => c1.Count == c2.Count && !c1.Except(c2).Any(),
            c => c.Aggregate(0, (a, kvp) => a ^ HashCode.Combine(kvp.Key, kvp.Value)),
            c => c.ToDictionary(kvp => kvp.Key, kvp => kvp.Value)));

var jsonValueFunction = modelBuilder.HasDbFunction(() => JsonValue(default, default));
jsonValueFunction.HasStoreType("nvarchar(4000)");
jsonValueFunction.HasParameter("json").HasStoreType("nvarchar(max)");

Fungsi kemudian dapat digunakan dengan properti yang dikonversi:

var jsonQuery = context.JsonEntities.Select(e => BloggingContext.JsonValue(e.Metadata, "$.Filter"));

SELECT JSON_VALUE([j].[Metadata], N'$.Filter')
FROM [JsonEntities] AS [j]

Pengonversi nilai berasal dari ekspresi yang diteruskan sebagai argumen fungsi. Oleh karena itu, pola ini berfungsi untuk properti yang dipetakan seperti JsonEntity.Metadata, tetapi mengonfigurasi jenis penyimpanan parameter tidak membuat nilai kamus arbitrer dapat diterjemahkan. Untuk menggunakan kamus dalam memori, serialisasikan dan teruskan string yang dihasilkan ke metode yang dipetakan secara terpisah yang parameter CLR-nya adalah string.

Memetakan metode ke SQL kustom

EF Core juga memungkinkan metode CLR untuk diterjemahkan langsung ke ekspresi SQL daripada fungsi database. Ekspresi SQL disediakan menggunakan HasTranslation selama konfigurasi fungsi.

Dalam contoh di bawah ini, kita akan membuat fungsi yang menghitung perbedaan persentase antara dua bilangan bulat.

Metode CLR adalah sebagai berikut:

public double PercentageDifference(double first, int second)
    => throw new NotSupportedException();

Definisi fungsinya adalah sebagai berikut:

// 100 * ABS(first - second) / ((first + second) / 2)
modelBuilder.HasDbFunction(
        typeof(BloggingContext).GetMethod(nameof(PercentageDifference), [typeof(double), typeof(int)]))
    .HasTranslation(
        args =>
            new SqlBinaryExpression(
                ExpressionType.Multiply,
                new SqlConstantExpression(100, new IntTypeMapping("int", DbType.Int32)),
                new SqlBinaryExpression(
                    ExpressionType.Divide,
                    new SqlFunctionExpression(
                        "ABS",
                        [
                            new SqlBinaryExpression(
                                ExpressionType.Subtract,
                                args.First(),
                                args.Skip(1).First(),
                                args.First().Type,
                                args.First().TypeMapping)
                        ],
                        nullable: true,
                        argumentsPropagateNullability: [true, true],
                        type: args.First().Type,
                        typeMapping: args.First().TypeMapping),
                    new SqlBinaryExpression(
                        ExpressionType.Divide,
                        new SqlBinaryExpression(
                            ExpressionType.Add,
                            args.First(),
                            args.Skip(1).First(),
                            args.First().Type,
                            args.First().TypeMapping),
                        new SqlConstantExpression(2, new IntTypeMapping("int", DbType.Int32)),
                        args.First().Type,
                        args.First().TypeMapping),
                    args.First().Type,
                    args.First().TypeMapping),
                args.First().Type,
                args.First().TypeMapping));

Setelah kita menentukan fungsi, fungsi tersebut dapat digunakan dalam kueri. Alih-alih memanggil fungsi database, EF Core akan menerjemahkan isi metode langsung ke SQL berdasarkan pohon ekspresi SQL yang dibangun dari HasTranslation. Kueri LINQ berikut:

var query2 = from p in context.Posts
             select context.PercentageDifference(p.BlogId, 3);

Menghasilkan SQL berikut:

SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]

Caution

HasTranslation bekerja dengan pohon ekspresi SQL, bukan teks SQL. Terjemahan harus membangun objek SqlExpression yang valid dengan pemetaan tipe yang benar, kemungkinan null, dan propagasi kemungkinan null argumen. Metadata yang salah dapat menghasilkan SQL yang tidak valid atau hasil kueri yang salah, dan jenis ekspresi yang digunakan oleh terjemahan mungkin khusus untuk penyedia database. Gunakan API tingkat rendah ini hanya setelah memahami pohon ekspresi SQL penyedia; lebih suka pemetaan fungsi reguler atau terjemahan penyedia yang ada jika memungkinkan.

Pengaturan nullability dari fungsi yang didefinisikan oleh pengguna berdasarkan argumennya.

Jika nullability berasal dari argumen fungsi—artinya, fungsi mengembalikan null setiap kali argumen tersebut bernilai null—EF Core dapat menghasilkan SQL yang lebih efisien. Konfigurasikan ini dengan memanggil PropagatesNullability untuk parameter yang relevan. Untuk informasi selengkapnya tentang bagaimana EF Core mengimbangi logika tiga nilai milik SQL, lihat Semantik null pada kueri.

Untuk mengilustrasikan ini, tentukan fungsi ConcatStringspengguna :

CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    RETURN @prm1 + @prm2;
END

dan dua metode CLR yang memetakannya:

public string ConcatStrings(string prm1, string prm2)
    => throw new InvalidOperationException();

public string ConcatStringsOptimized(string prm1, string prm2)
    => throw new InvalidOperationException();

Konfigurasi model (di dalam metode OnModelCreating) adalah sebagai berikut:

modelBuilder
    .HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ConcatStrings), [typeof(string), typeof(string)]))
    .HasName("ConcatStrings");

modelBuilder.HasDbFunction(
    typeof(BloggingContext).GetMethod(nameof(ConcatStringsOptimized), [typeof(string), typeof(string)]),
    b =>
    {
        b.HasName("ConcatStrings");
        b.HasParameter("prm1").PropagatesNullability();
        b.HasParameter("prm2").PropagatesNullability();
    });

Fungsi pertama dikonfigurasi dengan cara standar. Fungsi kedua dikonfigurasi untuk memanfaatkan optimasi propagasi nullability, memberikan informasi lebih lanjut tentang bagaimana fungsi berfungsi dengan parameter null.

Saat mengeluarkan kueri berikut:

var query3 = context.Blogs.Where(e => context.ConcatStrings(e.Url, e.Rating.ToString()) != "https://mytravelblog.com/4");
var query4 = context.Blogs.Where(
    e => context.ConcatStringsOptimized(e.Url, e.Rating.ToString()) != "https://mytravelblog.com/4");

Kami mendapatkan SQL ini:

SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE ([dbo].[ConcatStrings]([b].[Url], CONVERT(VARCHAR(11), [b].[Rating])) <> N'Lorem ipsum...') OR [dbo].[ConcatStrings]([b].[Url], CONVERT(VARCHAR(11), [b].[Rating])) IS NULL

SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE ([dbo].[ConcatStrings]([b].[Url], CONVERT(VARCHAR(11), [b].[Rating])) <> N'Lorem ipsum...') OR ([b].[Url] IS NULL OR [b].[Rating] IS NULL)

Kueri kedua tidak perlu mengevaluasi ulang fungsi itu sendiri untuk menguji ketidakadaannya.

Nota

Hanya konfigurasikan penyebaran kemampuan bernilai null saat fungsi hanya dapat mengembalikan null karena satu atau beberapa parameter yang dikonfigurasi bernilai null.

Memetakan fungsi yang dapat dikueri ke fungsi bernilai tabel

EF Core juga mendukung pemetaan ke fungsi bernilai tabel dengan menggunakan metode CLR yang ditentukan oleh pengguna yang mengembalikan kumpulan jenis entitas IQueryable, memungkinkan EF Core untuk memetakan fungsi bernilai tabel (TVF) dengan parameter. Proses ini mirip dengan pemetaan fungsi skalar yang ditentukan pengguna ke fungsi SQL: kita memerlukan TVF dalam database, fungsi CLR yang digunakan dalam kueri LINQ, dan pemetaan antara keduanya.

Sebagai contoh, kita akan menggunakan fungsi bernilai tabel yang mengembalikan semua posting yang memiliki setidaknya satu komentar yang memenuhi ambang batas "Suka" tertentu:

CREATE FUNCTION dbo.PostsWithPopularComments(@likeThreshold int)
RETURNS TABLE
AS
RETURN
(
    SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
    FROM [Posts] AS [p]
    WHERE (
        SELECT COUNT(*)
        FROM [Comments] AS [c]
        WHERE ([p].[PostId] = [c].[PostId]) AND ([c].[Likes] >= @likeThreshold)) > 0
)

Tanda tangan metode CLR adalah sebagai berikut:

public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
    => FromExpression(() => PostsWithPopularComments(likeThreshold));

Petunjuk / Saran

Panggilan FromExpression dalam isi fungsi CLR memungkinkan penggunaan fungsi sebagai pengganti DbSet biasa.

Dan di bawah ini adalah pemetaan:

modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), [typeof(int)]));

Nota

Fungsi yang dapat diakses melalui kueri harus dipetakan ke fungsi bernilai tabel. HasTranslation hanya mendukung fungsi skalar dan tidak dapat digunakan untuk fungsi bernilai tabel.

Saat fungsi dipetakan, dilakukan kueri berikut ini:

var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
             orderby p.Rating
             select p;

Menghasilkan:

SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]