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();
Tubuh 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.
Catatan
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(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
.HasName("CommentedPostCountForBlog");
Secara default, EF Core mencoba memetakan fungsi CLR ke fungsi yang ditentukan pengguna dengan nama yang sama. Jika nama berbeda, kita dapat menggunakan HasName untuk memberikan nama yang benar untuk fungsi yang ditentukan pengguna yang ingin kita petakan.
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 SQL kustom
EF Core juga memungkinkan fungsi yang ditentukan pengguna yang dikonversi ke SQL tertentu. Ekspresi SQL disediakan menggunakan HasTranslation metode selama konfigurasi fungsi yang ditentukan pengguna.
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), new[] { typeof(double), typeof(int) }))
.HasTranslation(
args =>
new SqlBinaryExpression(
ExpressionType.Multiply,
new SqlConstantExpression(
Expression.Constant(100),
new IntTypeMapping("int", DbType.Int32)),
new SqlBinaryExpression(
ExpressionType.Divide,
new SqlFunctionExpression(
"ABS",
new SqlExpression[]
{
new SqlBinaryExpression(
ExpressionType.Subtract,
args.First(),
args.Skip(1).First(),
args.First().Type,
args.First().TypeMapping)
},
nullable: true,
argumentsPropagateNullability: new[] { 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(
Expression.Constant(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]
Mengonfigurasi nullability fungsi yang ditentukan pengguna berdasarkan argumennya
Jika fungsi yang ditentukan pengguna hanya dapat mengembalikan null ketika satu atau beberapa argumennya adalah null, EFCore menyediakan cara untuk menentukannya, menghasilkan SQL yang lebih berkinerja. Ini dapat dilakukan dengan menambahkan PropagatesNullability() panggilan ke konfigurasi model parameter fungsi yang relevan.
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 (metode dalam OnModelCreating ) adalah sebagai berikut:
modelBuilder
.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ConcatStrings), new[] { typeof(string), typeof(string) }))
.HasName("ConcatStrings");
modelBuilder.HasDbFunction(
typeof(BloggingContext).GetMethod(nameof(ConcatStringsOptimized), new[] { 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 pengoptimalan penyebaran nullability, memberikan informasi lebih lanjut tentang bagaimana fungsi bertempur di sekitar 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 nullability-nya.
Catatan
Pengoptimalan ini hanya boleh digunakan jika fungsi hanya dapat kembali null ketika parameternya adalah null.
Memetakan fungsi yang dapat dikueri ke fungsi bernilai tabel
EF Core juga mendukung pemetaan ke fungsi bernilai tabel menggunakan metode CLR yang ditentukan pengguna yang IQueryable mengembalikan jenis entitas, memungkinkan EF Core memetakan 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));
Tip
Panggilan FromExpression dalam isi fungsi CLR memungkinkan fungsi digunakan alih-alih DbSet biasa.
Dan di bawah ini adalah pemetaan:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), new[] { typeof(int) }));
Catatan
Fungsi yang dapat dikueri harus dipetakan ke fungsi bernilai tabel dan tidak dapat menggunakan HasTranslation.
Saat fungsi dipetakan, kueri berikut:
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]
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk