Door de gebruiker gedefinieerde functie-mapping

EF Core maakt het gebruik van door de gebruiker gedefinieerde SQL-functies in query's mogelijk. Hiervoor moeten de functies tijdens de modelconfiguratie worden toegewezen aan een CLR-methode. Bij het vertalen van de LINQ-query naar SQL wordt de door de gebruiker gedefinieerde functie aangeroepen in plaats van de CLR-functie waaraan deze is toegewezen.

Een methode toewijzen aan een SQL-functie

Laten we de volgende entiteiten definiëren om te laten zien hoe door de gebruiker gedefinieerde functietoewijzing werkt:

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

En de volgende modelconfiguratie:

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

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

Blog kan veel berichten bevatten en elk bericht kan veel opmerkingen bevatten.

Maak vervolgens de door de gebruiker gedefinieerde functie CommentedPostCountForBlog, die het aantal berichten met ten minste één opmerking voor een bepaalde blog retourneert, op basis van het 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

Als u deze functie in EF Core wilt gebruiken, definiëren we de volgende CLR-methode, die we toewijzen aan de door de gebruiker gedefinieerde functie:

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

De inhoud van de CLR-methode is niet belangrijk. De methode wordt niet aangeroepen aan de clientzijde, tenzij EF Core de argumenten niet kan omzetten. Als de argumenten kunnen worden vertaald, geeft EF Core uitsluitend om de handtekening van de methode.

Deze functiedefinitie kan nu worden gekoppeld aan een door de gebruiker gedefinieerde functie in de modelconfiguratie:

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

De lambda-overbelasting van HasDbFunction vermijdt handmatig opzoeken van de MethodInfo. De default argumentwaarden worden alleen gebruikt om de methode te identificeren. Ze worden nooit naar de database verzonden.

Ef Core wijst de CLR-methode standaard toe aan een databasefunctie met dezelfde naam in het standaardschema. Gebruik HasName en HasSchema wanneer de naam of het schema verschilt.

Voer nu de volgende query uit:

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

Hiermee wordt deze SQL geproduceerd:

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

Een methode toewijzen aan een ingebouwde functie

EF Core beschouwt een gemapte functie standaard als door de gebruiker gedefinieerd. Sommige databases onderscheiden ingebouwde en door de gebruiker gedefinieerde functies bij het genereren van SQL. Voor SQL Server moeten door de gebruiker gedefinieerde functies bijvoorbeeld schemagekwalificeerd zijn, maar ingebouwde functies zijn niet schemagekwalificeerd.

Gebruik IsBuiltIn om een CLR-methode te koppelen aan een ingebouwde functie:

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

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

De IsBuiltIn eigenschap biedt dezelfde configuratie wanneer u een kenmerk gebruikt:

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

Een functie toewijzen met behulp van DbFunctionAttribute

In plaats van een functie te registreren in OnModelCreating, kan een statische methode die is gedeclareerd op de DbContext rechtstreeks worden gekoppeld door DbFunctionAttribute toe te passen. De eigenschappen IsNullable, Schema, IsBuiltIn en Name van het attribuut configureren de overeenkomstige kenmerken van de databasefunctie; dit zijn dezelfde kenmerken die worden geconfigureerd door de fluent API-methoden HasName, HasSchema, IsBuiltIn en IsNullable bij gebruik van HasDbFunction. Toegeschreven methoden voor de context worden gedetecteerd en automatisch geregistreerd; toegewezen methoden voor andere klassen moeten nog steeds worden geregistreerd bij HasDbFunction. Roep HasDbFunction voor een automatisch geregistreerde methode alleen aan wanneer een builder nodig is voor extra fluent-configuratie, zoals in het onderstaande voorbeeld met het store-type.

Bijvoorbeeld gebruikt de volgende methode DbFunctionAttribute om de ingebouwde functie JSON_VALUE van SQL Server te koppelen aan. Omdat IsBuiltIntrue is, genereert EF Core de functienaam zonder schema.

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

Winkeltypen configureren

Gebruik HasStoreType om het opslagtype van de retourwaarde van een functie te configureren en HasStoreType om het opslagtype van een parameter te configureren. Dit is met name handig wanneer het parametertype CLR geen systeemeigen databasetoewijzing heeft.

In dit voorbeeld JsonEntity.Metadata is een woordenlijst opgeslagen als nvarchar(max) via een waardeconversieprogramma. De json functieparameter heeft hetzelfde winkeltype, terwijl het resultaat gebruikmaakt van het nvarchar(4000) type dat wordt geretourneerd door 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)");

De functie kan vervolgens worden gebruikt met de geconverteerde eigenschap:

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

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

De waardeconverter wordt overgenomen uit de expressie die als functieargument wordt doorgegeven. Daarom werkt dit patroon voor een toegewezen eigenschap, zoals JsonEntity.Metadata, maar het configureren van het parameterarchieftype maakt willekeurige woordenlijstwaarden niet vertaalbaar. Als u een woordenlijst in het geheugen wilt gebruiken, serialiseert u deze en geeft u de resulterende tekenreeks door aan een afzonderlijk toegewezen methode waarvan de CLR-parameter is string.

Een methode toewijzen aan een aangepaste SQL

Met EF Core kan een CLR-methode ook rechtstreeks worden vertaald naar een SQL-expressie in plaats van een databasefunctie. De SQL-expressie wordt tijdens de configuratie van de functie opgegeven met HasTranslation.

In het onderstaande voorbeeld maken we een functie waarmee het percentageverschil tussen twee gehele getallen wordt berekend.

De CLR-methode is als volgt:

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

De functiedefinitie is als volgt:

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

Zodra we de functie hebben gedefinieerd, kan deze worden gebruikt in de query. In plaats van de databasefunctie aan te roepen, vertaalt EF Core de methodebody direct in SQL op basis van de SQL-expressieboom die is geconstrueerd vanuit de HasTranslation. De volgende LINQ-query:

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

Produceert de volgende SQL:

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

Nullbaarheid van gebruikersgedefinieerde functie configureren op basis van zijn argumenten

Als nullbaarheid voortkomt uit een functieargument — dat wil zeggen, de functie retourneert null wanneer dat argument null is — kan EF Core efficiëntere SQL genereren. Configureer dit door de relevante parameters aan te roepen PropagatesNullability . Zie Query null-semantiek voor meer informatie over hoe EF Core de driewaardelogica van SQL compenseert.

Om dit te illustreren, definieert u de gebruikersfunctie ConcatStrings:

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

en twee CLR-methoden die ermee overeenkomen:

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

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

De modelconfiguratie (binnen OnModelCreating de methode) is als volgt:

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

De eerste functie wordt op de standaard manier geconfigureerd. De tweede functie is geconfigureerd om te profiteren van de optimalisatie voor het doorgeven van null-waarden, zodat u meer informatie krijgt over hoe de functie zich gedraagt rond null-parameters.

Bij het uitvoeren van de volgende queries:

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

We krijgen deze SQL:

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)

De tweede query hoeft de functie zelf niet opnieuw te evalueren om de null-waarde ervan te testen.

Een opvraagbare functie toewijzen aan een tabelwaarde-functie

EF Core biedt ook ondersteuning voor toewijzing aan een tabelwaardefunctie met behulp van een door de gebruiker gedefinieerde CLR-methode die een IQueryable entiteitstype retourneert, zodat EF Core TVF's kan toewijzen aan parameters. Het proces is vergelijkbaar met het toewijzen van een scalaire door de gebruiker gedefinieerde functie aan een SQL-functie: we hebben een TVF in de database nodig, een CLR-functie die wordt gebruikt in de LINQ-query's en een toewijzing tussen de twee.

Als voorbeeld gebruiken we een tabelwaardefunctie die alle berichten retourneert met ten minste één opmerking die voldoet aan een bepaalde drempelwaarde voor Vind ik leuk:

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
)

De CLR methodesignatuur is als volgt:

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

Hieronder ziet u de mapping:

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

Wanneer de functie is toegewezen, voert u de volgende query uit:

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

Produceert:

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