Användardefinierad funktionsmappning

MED EF Core kan du använda användardefinierade SQL-funktioner i frågor. För att göra det måste funktionerna mappas till en CLR-metod under modellkonfigurationen. När linq-frågan översätts till SQL anropas den användardefinierade funktionen i stället för den CLR-funktion som den har mappats till.

Mappa en metod till en SQL-funktion

För att illustrera hur användardefinierad funktionsmappning fungerar ska vi definiera följande entiteter:

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

Och följande modellkonfiguration:

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

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

Bloggen kan ha många inlägg och varje inlägg kan ha många kommentarer.

Skapa sedan den användardefinierade funktionen CommentedPostCountForBlog, som returnerar antalet inlägg med minst en kommentar för en viss blogg, baserat på bloggen 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

Om du vill använda den här funktionen i EF Core definierar vi följande CLR-metod, som vi mappar till den användardefinierade funktionen:

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

Clr-metodens brödtext är inte viktig. Metoden anropas inte på klientsidan, såvida inte EF Core inte kan översätta sina argument. Om argumenten kan översättas bryr sig EF Core bara om metodsignaturen.

Den här funktionsdefinitionen kan nu associeras med användardefinierad funktion i modellkonfigurationen:

modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), [typeof(int)]))
    .HasName("CommentedPostCountForBlog");

Som standard försöker EF Core mappa CLR-funktionen till en användardefinierad funktion med samma namn. Om namnen skiljer sig åt kan vi använda HasName för att ange rätt namn för den användardefinierade funktion som vi vill mappa till.

Kör nu följande fråga:

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

Kommer att producera denna SQL:

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

Mappa en metod till en anpassad SQL

EF Core möjliggör också användardefinierade funktioner som konverteras till en specifik SQL. SQL-uttrycket tillhandahålls med hjälp av HasTranslation metoden under användardefinierad funktionskonfiguration.

I exemplet nedan skapar vi en funktion som beräknar procentuell skillnad mellan två heltal.

CLR-metoden är följande:

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

Funktionsdefinitionen är följande:

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

När vi har definierat funktionen kan den användas i frågan. I stället för att anropa databasfunktionen översätter EF Core metodtexten direkt till SQL baserat på SQL-uttrycksträdet som skapats från HasTranslation. Följande LINQ-fråga:

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

Genererar följande SQL:

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

Konfigurera nullabilitet för användardefinierad funktion baserat på dess argument

Om den användardefinierade funktionen bara kan returnera null när ett eller flera av dess argument är null, ger EFCore ett sätt att ange det, vilket resulterar i mer högpresterande SQL. Det kan göras genom att lägga till ett PropagatesNullability() anrop till den relevanta modellkonfigurationen för funktionsparametrar.

För att illustrera detta definierar du användarfunktionen ConcatStrings:

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

och två CLR-metoder som mappar till den:

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

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

Modellkonfigurationen (inuti OnModelCreating metoden) är följande:

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

Den första funktionen konfigureras på standard sätt. Den andra funktionen är konfigurerad för att dra nytta av optimeringen av nullabilitetsspridning, vilket ger mer information om hur funktionen beter sig kring null-parametrar.

När du utfärdar följande frågor:

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

Vi får den här SQL:en:

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)

Den andra frågan behöver inte omvärdera själva funktionen för att testa dess nullbarhet.

Mappa en frågefunktion till en tabellvärdesfunktion

EF Core stöder också mappning till en tabellvärdesfunktion med hjälp av en användardefinierad CLR-metod som returnerar en av entitetstyper IQueryable , vilket gör att EF Core kan mappa TVF:er med parametrar. Processen liknar mappning av en skalär användardefinierad funktion till en SQL-funktion: vi behöver en TVF i databasen, en CLR-funktion som används i LINQ-frågorna och en mappning mellan de två.

Vi använder till exempel en tabellvärdesfunktion som returnerar alla inlägg med minst en kommentar som uppfyller ett visst tröskelvärde för "Like":

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-metodsignaturen är följande:

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

Nedan visas mappningen:

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

När funktionen är kartlagd, utförs följande fråga:

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

Producerar:

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