Mapowanie funkcji zdefiniowanych przez użytkownika
Program EF Core umożliwia korzystanie z funkcji SQL zdefiniowanych przez użytkownika w zapytaniach. W tym celu funkcje muszą być mapowane na metodę CLR podczas konfigurowania modelu. Podczas tłumaczenia zapytania LINQ na język SQL funkcja zdefiniowana przez użytkownika jest wywoływana zamiast funkcji CLR, na która została zamapowana.
Mapowanie metody na funkcję SQL
Aby zilustrować sposób działania mapowania funkcji zdefiniowanych przez użytkownika, zdefiniujmy następujące jednostki:
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; }
}
A następująca konfiguracja modelu:
modelBuilder.Entity<Blog>()
.HasMany(b => b.Posts)
.WithOne(p => p.Blog);
modelBuilder.Entity<Post>()
.HasMany(p => p.Comments)
.WithOne(c => c.Post);
Blog może zawierać wiele wpisów, a każdy wpis może zawierać wiele komentarzy.
Następnie utwórz funkcję CommentedPostCountForBlogzdefiniowaną przez użytkownika , która zwraca liczbę wpisów z co najmniej jednym komentarzem dla danego bloga na podstawie blogu 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
Aby użyć tej funkcji w programie EF Core, zdefiniujemy następującą metodę CLR, którą mapujemy na funkcję zdefiniowaną przez użytkownika:
public int ActivePostCountForBlog(int blogId)
=> throw new NotSupportedException();
Treść metody CLR nie jest ważna. Metoda nie zostanie wywołana po stronie klienta, chyba że program EF Core nie może przetłumaczyć swoich argumentów. Jeśli argumenty można przetłumaczyć, program EF Core dba tylko o sygnaturę metody.
Uwaga
W tym przykładzie metoda jest zdefiniowana w metodzie DbContext, ale można ją również zdefiniować jako metodę statyczną wewnątrz innych klas.
Ta definicja funkcji może być teraz skojarzona z funkcją zdefiniowaną przez użytkownika w konfiguracji modelu:
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
.HasName("CommentedPostCountForBlog");
Domyślnie program EF Core próbuje zamapować funkcję CLR na funkcję zdefiniowaną przez użytkownika o tej samej nazwie. Jeśli nazwy różnią się, możemy użyć HasName metody , aby podać poprawną nazwę funkcji zdefiniowanej przez użytkownika, do której chcesz zamapować.
Teraz wykonaj następujące zapytanie:
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
Spowoduje to wygenerowanie tego kodu SQL:
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
Mapowanie metody na niestandardową usługę SQL
Program EF Core umożliwia również korzystanie z funkcji zdefiniowanych przez użytkownika, które są konwertowane na określony język SQL. Wyrażenie SQL jest udostępniane przy użyciu HasTranslation metody podczas konfiguracji funkcji zdefiniowanej przez użytkownika.
W poniższym przykładzie utworzymy funkcję, która oblicza różnicę procentową między dwiema liczbami całkowitymi.
Metoda CLR jest następująca:
public double PercentageDifference(double first, int second)
=> throw new NotSupportedException();
Definicja funkcji jest następująca:
// 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));
Po zdefiniowaniu funkcji można jej użyć w zapytaniu. Zamiast wywoływać funkcję bazy danych, program EF Core przetłumacze treść metody bezpośrednio na język SQL na podstawie drzewa wyrażeń SQL skonstruowanego z funkcji HasTranslation. Następujące zapytanie LINQ:
var query2 = from p in context.Posts
select context.PercentageDifference(p.BlogId, 3);
Tworzy następujący kod SQL:
SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]
Konfigurowanie wartości null funkcji zdefiniowanej przez użytkownika na podstawie jej argumentów
Jeśli funkcja zdefiniowana przez użytkownika może zwracać null tylko wtedy, gdy co najmniej jeden z jego argumentów to null, funkcja EFCore umożliwia określenie tego, co powoduje zwiększenie wydajności bazy danych SQL. Można to zrobić, dodając wywołanie PropagatesNullability() do odpowiedniej konfiguracji modelu parametrów funkcji.
Aby to zilustrować, zdefiniuj funkcję ConcatStringsużytkownika :
CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
RETURN @prm1 + @prm2;
END
i dwie metody CLR mapujące na nią:
public string ConcatStrings(string prm1, string prm2)
=> throw new InvalidOperationException();
public string ConcatStringsOptimized(string prm1, string prm2)
=> throw new InvalidOperationException();
Konfiguracja modelu (wewnątrz OnModelCreating metody) jest następująca:
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();
});
Pierwsza funkcja jest skonfigurowana w standardowy sposób. Druga funkcja jest skonfigurowana do korzystania z optymalizacji propagacji wartości null, zapewniając więcej informacji na temat zachowania funkcji wokół parametrów null.
Podczas wydawania następujących zapytań:
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");
Otrzymujemy następujący język 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)
Drugie zapytanie nie musi ponownie ocenić samej funkcji, aby przetestować jej wartość null.
Uwaga
Tej optymalizacji należy używać tylko wtedy, gdy funkcja może zwracać null tylko wtedy, gdy parametry to null.
Mapowanie funkcji z możliwością wykonywania zapytań do funkcji wartości tabeli
Program EF Core obsługuje również mapowanie na funkcję wartości tabeli przy użyciu zdefiniowanej przez użytkownika metody CLR zwracającej IQueryable typy jednostek, umożliwiając programowi EF Core mapowanie plików TVF z parametrami. Proces jest podobny do mapowania funkcji zdefiniowanej przez użytkownika skalarnej na funkcję SQL: potrzebujemy funkcji TVF w bazie danych, funkcji CLR używanej w zapytaniach LINQ i mapowania między nimi.
Na przykład użyjemy funkcji z wartością tabeli, która zwraca wszystkie wpisy zawierające co najmniej jeden komentarz spełniający określony próg "Lubię to":
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
)
Sygnatura metody CLR jest następująca:
public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
=> FromExpression(() => PostsWithPopularComments(likeThreshold));
Napiwek
Wywołanie FromExpression w treści funkcji CLR umożliwia użycie funkcji zamiast zwykłego zestawu dbSet.
Poniżej znajduje się mapowanie:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), new[] { typeof(int) }));
Uwaga
Funkcja z możliwością wykonywania zapytań musi być mapowana na funkcję wartości tabeli i nie może używać funkcji HasTranslation.
Gdy funkcja jest mapowana, następujące zapytanie:
var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
orderby p.Rating
select p;
Produkuje:
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]
