Mapování uživatelem definované funkce
EF Core umožňuje používat uživatelem definované funkce SQL v dotazech. K tomu je potřeba při konfiguraci modelu namapovat funkce na metodu CLR. Při překladu dotazu LINQ do SQL se místo funkce CLR, na které byla namapována, volá funkce definovaná uživatelem.
Mapování metody na funkci SQL
Abychom si ukázali, jak funguje mapování funkcí definovaných uživatelem, pojďme definovat následující entity:
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 následující konfigurace modelu:
modelBuilder.Entity<Blog>()
.HasMany(b => b.Posts)
.WithOne(p => p.Blog);
modelBuilder.Entity<Post>()
.HasMany(p => p.Comments)
.WithOne(c => c.Post);
Blog může mít mnoho příspěvků a každý příspěvek může mít mnoho komentářů.
Dále vytvořte uživatelem definovanou funkci CommentedPostCountForBlog, která vrátí počet příspěvků s alespoň jedním komentářem pro daný blog na základě 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
Abychom mohli tuto funkci použít v EF Core, definujeme následující metodu CLR, kterou namapujeme na uživatelem definovanou funkci:
public int ActivePostCountForBlog(int blogId)
=> throw new NotSupportedException();
Tělo metody CLR není důležité. Metoda nebude vyvolána na straně klienta, pokud EF Core nemůže přeložit své argumenty. Pokud se argumenty dají přeložit, EF Core se stará jenom o podpis metody.
Poznámka
V příkladu je metoda definována na DbContext, ale může být také definována jako statická metoda uvnitř jiných tříd.
Tuto definici funkce je teď možné přidružit k uživatelem definované funkci v konfiguraci modelu:
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
.HasName("CommentedPostCountForBlog");
EF Core se ve výchozím nastavení pokusí namapovat funkci CLR na uživatelem definovanou funkci se stejným názvem. Pokud se názvy liší, můžeme použít HasName k zadání správného názvu uživatelem definované funkce, na kterou chceme namapovat.
Teď spusťte následující dotaz:
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
Vygeneruje tento SQL:
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
Mapování metody na vlastní SQL
EF Core také umožňuje uživatelem definované funkce, které se převedou na konkrétní SQL. Výraz SQL se poskytuje pomocí HasTranslation metody během konfigurace uživatelem definované funkce.
V následujícím příkladu vytvoříme funkci, která vypočítá procentuální rozdíl mezi dvěma celými čísly.
Metoda CLR je následující:
public double PercentageDifference(double first, int second)
=> throw new NotSupportedException();
Definice funkce je následující:
// 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));
Jakmile funkci definujeme, můžete ji použít v dotazu. Místo volání databázové funkce EF Core přeloží tělo metody přímo do SQL na základě stromu výrazů SQL vytvořeného z HasTranslation. Následující dotaz LINQ:
var query2 = from p in context.Posts
select context.PercentageDifference(p.BlogId, 3);
Vytvoří následující SQL:
SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]
Konfigurace nullability uživatelem definované funkce na základě jejích argumentů
Pokud uživatelem definovaná funkce může vrátit null pouze tehdy, když je nulljeden nebo více jejích argumentů , EFCore poskytuje způsob, jak to určit, což vede k výkonnějšímu SQL. Můžete to provést přidáním PropagatesNullability() volání do konfigurace příslušného modelu parametrů funkce.
Chcete-li to ilustrovat, definujte uživatelskou funkci ConcatStrings:
CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
RETURN @prm1 + @prm2;
END
a dvě metody CLR, které se na ni mapuje:
public string ConcatStrings(string prm1, string prm2)
=> throw new InvalidOperationException();
public string ConcatStringsOptimized(string prm1, string prm2)
=> throw new InvalidOperationException();
Konfigurace modelu (uvnitř OnModelCreating metody) je následující:
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();
});
První funkce je nakonfigurována standardním způsobem. Druhá funkce je nakonfigurovaná tak, aby využívala optimalizaci šíření null a poskytuje další informace o chování funkce kolem parametrů null.
Při vydávání následujících dotazů:
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");
Získáte tento 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)
Druhý dotaz nemusí znovu vyhodnotit samotnou funkci, aby otestovala její nulovost.
Poznámka
Tuto optimalizaci byste měli použít pouze v případě, že funkce může vrátit null pouze v případě, že jsou nullparametry .
Mapování dotazovatelné funkce na funkci s hodnotou tabulky
EF Core také podporuje mapování na funkci s hodnotou tabulky pomocí uživatelsky definované metody CLR vracející IQueryable typy entit, což ef Core umožňuje mapovat TVF s parametry. Tento proces se podobá mapování skalární uživatelem definované funkce na funkci SQL: potřebujeme v databázi TVF, funkci CLR, která se používá v dotazech LINQ, a mapování mezi těmito dvěma funkcemi.
Jako příklad použijeme funkci s hodnotou tabulky, která vrátí všechny příspěvky s alespoň jedním komentářem, který splňuje danou prahovou hodnotu 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
)
Podpis metody CLR je následující:
public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
=> FromExpression(() => PostsWithPopularComments(likeThreshold));
Tip
Volání FromExpression v těle funkce CLR umožňuje použít funkci místo běžné dbSet.
A níže je mapování:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), new[] { typeof(int) }));
Poznámka
Dotazovatelná funkce musí být namapována na funkci s hodnotou tabulky a nemůže použít HasTranslation.
Když je funkce namapovaná, následující dotaz:
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]
