Kullanıcı tanımlı işlev eşleme

EF Core, sorgularda kullanıcı tanımlı SQL işlevlerinin kullanılmasına olanak tanır. Bunu yapmak için, model yapılandırması sırasında işlevlerin bir CLR yöntemine eşlenmesi gerekir. LINQ sorgusunu SQL'e çevirirken, eşleştirildiği CLR işlevi yerine kullanıcı tanımlı işlev çağrılır.

Bir yöntemi SQL işlevine eşleme

Kullanıcı tanımlı işlev eşlemesinin nasıl çalıştığını göstermek için aşağıdaki varlıkları tanımlayalım:

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; }
}

Ve aşağıdaki model yapılandırması:

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

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

Blogda birçok gönderi olabilir ve her gönderinin birçok yorumu olabilir.

Ardından, blogu temel alarak CommentedPostCountForBlogbelirli bir blog için en az bir yorum içeren gönderi sayısını döndüren kullanıcı tanımlı işlevini Idoluşturun:

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

EF Core'da bu işlevi kullanmak için, kullanıcı tanımlı işlevle eşlediğimiz aşağıdaki CLR yöntemini tanımlarız:

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

CLR yönteminin gövdesi önemli değildir. EF Core bağımsız değişkenlerini çeviremedikçe yöntemi istemci tarafında çağrılmaz. Bağımsız değişkenler çevrilebiliyorsa EF Core yalnızca yöntem imzasını önemser.

Uyarı

Örnekte, yöntem DbContext üzerinde tanımlanır, ancak diğer sınıfların içinde de statik bir yöntem olarak tanımlanabilir.

Bu işlev tanımı artık model yapılandırmasındaki kullanıcı tanımlı bir işlevle ilişkilendirilebilir:

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

HasDbFunction öğesinin lambda aşırı yüklemesi, MethodInfo öğesini el ile arama gereğini ortadan kaldırır. default Bağımsız değişken değerleri yalnızca yöntemi tanımlamak için kullanılır; hiçbir zaman veritabanına gönderilmez.

VARSAYıLAN olarak, EF Core CLR yöntemini varsayılan şemada aynı ada sahip bir veritabanı işleviyle eşler. Ad veya şema farklı olduğunda HasSchema ve HasName kullanın.

Şimdi aşağıdaki sorguyu yürütüyoruz:

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

Bu SQL'i oluşturur:

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

Bir yöntemi yerleşik bir işleve eşleme

EF Core, eşlenmiş bir işlevi varsayılan olarak kullanıcı tanımlı olarak kabul eder. Bazı veritabanları, SQL oluştururken yerleşik ve kullanıcı tanımlı işlevleri ayırt eder. Örneğin, SQL Server kullanıcı tanımlı işlevlerin şemaya uygun olmasını gerektirir, ancak yerleşik işlevler şemaya uygun değildir.

CLR yöntemini yerleşik bir işlevle eşlemek için kullanın IsBuiltIn :

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

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

özelliği, IsBuiltIn bir öznitelik kullanılırken aynı yapılandırmayı sağlar:

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

DbFunctionAttribute kullanarak işlev eşleme

OnModelCreating içinde bir işlev kaydetmek yerine, DbContext üzerinde bildirilen statik bir yöntem, DbFunctionAttribute uygulanarak doğrudan eşlenebilir. Özniteliğin HasDbFunction, HasSchema, IsBuiltIn ve IsNullable özellikleri, veritabanı işlevinin karşılık gelen özelliklerini yapılandırır; bunlar, Name kullanılırken HasName, IsNullable, IsBuiltIn ve Schema akıcı API yöntemleri tarafından yapılandırılan özelliklerle aynıdır. Bağlam üzerindeki öznitelikli yöntemler otomatik olarak bulunur ve kaydedilir; diğer sınıflardaki öznitelikli yöntemler ise yine de HasDbFunction ile kaydedilmelidir. Otomatik olarak kaydedilen bir yöntemi yalnızca aşağıdaki mağaza türü örneğinde olduğu gibi ek akıcı yapılandırma için bir oluşturucu gerektiğinde çağırın HasDbFunction .

Örneğin, aşağıdaki yöntem SQL Server’ın yerleşik DbFunctionAttribute işlevini eşlemek için JSON_VALUE kullanır. IsBuiltIn, true olduğundan EF Core, fonksiyon adını şema belirtmeden üretir.

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

Depo türlerini yapılandırma

Bir fonksiyonun dönüş depolama türünü yapılandırmak için HasStoreType, bir parametrenin depolama türünü yapılandırmak içinse HasStoreType kullanın. Bu özellikle CLR parametre türünün yerel veritabanı eşlemesi olmadığında kullanışlıdır.

Bu örnekte, JsonEntity.Metadata, bir değer dönüştürücüsü aracılığıyla nvarchar(max) olarak depolanan bir sözlüktür. json işlev parametresi aynı depolama türüne sahipken, sonuç nvarchar(4000) tarafından döndürülen JSON_VALUE türünü kullanır:

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)");

İşlev daha sonra dönüştürülen özellik ile kullanılabilir:

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

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

Değer dönüştürücüsü, fonksiyon argümanı olarak iletilen ifadeden alınır. Bu nedenle, bu desen gibi JsonEntity.Metadataeşlenmiş bir özellik için çalışır, ancak parametre deposu türünü yapılandırmak rastgele sözlük değerlerinin çevrilebilir olmasını sağlamaz. Bellek içi sözlük kullanmak için, bunu serileştirin ve sonuçta elde edilen dizeyi CLR parametresi stringolan ayrı eşlenmiş bir yönteme geçirin.

Bir yöntemi özel SQL'e eşleme

EF Core ayrıca bir CLR yönteminin veritabanı işlevi yerine doğrudan sql ifadesine çevrilmesini sağlar. SQL ifadesi, işlev yapılandırması sırasında HasTranslation kullanılarak sağlanır.

Aşağıdaki örnekte, iki tamsayı arasındaki yüzde farkını hesaplayan bir işlev oluşturacağız.

CLR yöntemi aşağıdaki gibidir:

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

İşlev tanımı aşağıdaki gibidir:

// 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));

İşlevi tanımladıktan sonra sorguda kullanılabilir. EF Core, veritabanı işlevini çağırmak yerine, HasTranslation'dan derlenen SQL ifade ağacını temel alarak yöntem gövdesini doğrudan SQL'e çevirir. Aşağıdaki LINQ sorgusu:

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

Aşağıdaki SQL'i üretir:

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

Caution

HasTranslation SQL metniyle değil SQL ifade ağacıyla çalışır. Çeviri, doğru tür eşlemeleri, null olabilirlik ve argüman null olabilirlik aktarımıyla geçerli SqlExpression nesneleri oluşturmalıdır. Yanlış meta veriler geçersiz SQL veya yanlış sorgu sonuçları üretebilir ve çeviri tarafından kullanılan ifade türleri veritabanı sağlayıcısına özgü olabilir. Bu alt düzey API'yi yalnızca sağlayıcının SQL ifade ağacını anladıktan sonra kullanın; mümkün olduğunda normal bir işlev eşlemesini veya mevcut bir sağlayıcı çevirisini tercih edin.

Kullanıcı tanımlı işlevin bağımsız değişkenlerine göre null kabul edebilirliğini yapılandırma.

Null atanabilirlik bir işlev bağımsız değişkeninden yayılıyorsa (yani, bu bağımsız değişken null olduğunda işlev null döndürüyorsa), EF Core daha verimli SQL oluşturabilir. Bunu, ilgili parametrelerle PropagatesNullability çağrısı yaparak yapılandırın. EF Core'un SQL'in üç değerli mantığını nasıl telafi ettiği hakkında daha fazla bilgi için Sorgularda null semantiği konusuna bakın.

Bunu göstermek için kullanıcı işlevini ConcatStringstanımlayın:

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

ve söz konusu yöntemle eşleşen iki CLR yöntemi:

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

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

Model yapılandırması (OnModelCreating yönteminin içinde) aşağıdaki gibidir:

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();
    });

İlk işlev standart şekilde yapılandırılır. İkinci işlev, null olabilirlik yayılım optimizasyonunun avantajından yararlanacak şekilde yapılandırılır ve fonksiyonun null parametrelerle etkileşimini daha doğru bir şekilde izah eder.

Aşağıdaki sorguları oluştururken:

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");

Şu SQL'i alacağız:

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)

İkinci sorgunun null atanabilirliğini test etmek için işlevin kendisini yeniden değerlendirmesi gerekmez.

Uyarı

Yalnızca, işlevin null değerini yalnızca yapılandırılmış parametrelerden bir veya daha fazlası null olduğu için döndürebildiği durumlarda null atanabilirlik yayılımını yapılandırın.

Sorgulanabilir bir işlevi tablo değeri döndüren bir işlevle dönüştürme

EF Core ayrıca, varlık türleri topluluğunu döndüren kullanıcı tanımlı CLR yöntemi kullanarak tablo-değerli bir IQueryable fonksiyona eşlemeyi de destekler ve EF Core'un TVF'leri parametrelerle eşlemesine olanak sağlar. İşlem, skaler kullanıcı tanımlı bir işlevi SQL işleviyle eşlemeye benzer: Veritabanında TVF, LINQ sorgularında kullanılan bir CLR işlevi ve ikisi arasındaki eşleme gerekir.

Örneğin, belirli bir "Beğen" eşiğine uyan en az bir açıklamaya sahip tüm gönderileri döndüren tablo değerli bir işlev kullanacağız:

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
)

CLR yöntemi imzası aşağıdaki gibidir:

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

Tavsiye

FromExpression CLR işlev gövdesindeki çağrı, işlevin normal bir DbSet yerine kullanılmasına olanak tanır.

İşte aşağıdaki eşleme:

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

Uyarı

Sorgulanabilir bir işlev tablo değerli bir işlevle eşlenmelidir. HasTranslation yalnızca skaler işlevleri destekler ve tablo değerli bir işlev için kullanılamaz.

İşlev eşlendiğinde aşağıdaki sorgu:

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

Üretir:

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