Примечание.
Для доступа к этой странице требуется авторизация. Вы можете попробовать войти или изменить каталоги.
Для доступа к этой странице требуется авторизация. Вы можете попробовать изменить каталоги.
EF Core позволяет использовать определяемые пользователем функции SQL в запросах. Для этого функции необходимо сопоставить с методом CLR во время настройки модели. При переводе запроса LINQ в SQL определяемая пользователем функция вызывается вместо функции CLR, с которую она сопоставлена.
Сопоставление метода с функцией SQL
Чтобы продемонстрировать, как работает сопоставление определяемых пользователем функций, давайте определим следующие сущности:
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; }
}
И следующая конфигурация модели:
modelBuilder.Entity<Blog>()
.HasMany(b => b.Posts)
.WithOne(p => p.Blog);
modelBuilder.Entity<Post>()
.HasMany(p => p.Comments)
.WithOne(c => c.Post);
В блоге может быть много записей, и каждая запись может иметь много комментариев.
Затем создайте определяемую пользователем функцию CommentedPostCountForBlog, которая возвращает количество записей с хотя бы одним комментарием для данного блога, основываясь на блоге 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
Чтобы использовать эту функцию в EF Core, мы определим следующий метод CLR, который сопоставляем с определяемой пользователем функцией:
public int ActivePostCountForBlog(int blogId)
=> throw new NotSupportedException();
Текст метода CLR не важен. Этот метод не будет вызываться на стороне клиента, если ef Core не может перевести свои аргументы. Если аргументы можно преобразовать, EF Core заботится только о сигнатуре метода.
Замечание
В примере метод определяется DbContext, но он также может быть определен как статический метод внутри других классов.
Теперь это определение функции можно связать с определяемой пользователем функцией в конфигурации модели:
modelBuilder.HasDbFunction(() => ActivePostCountForBlog(default))
.HasName("CommentedPostCountForBlog")
.HasSchema("dbo");
Лямбда-перегрузка HasDbFunction избегает ручного MethodInfoпоиска. Значения default аргументов используются только для идентификации метода. Они никогда не отправляются в базу данных.
По умолчанию EF Core сопоставляет метод CLR с функцией базы данных с тем же именем в схеме по умолчанию. Используйте HasName и HasSchema когда имя или схема отличаются.
Теперь выполните следующий запрос:
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
Будет создавать этот SQL:
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
Сопоставление метода со встроенной функцией
EF Core считает сопоставленную функцию определяемой пользователем по умолчанию. Некоторые базы данных различают встроенные и определяемые пользователем функции при создании SQL. Например, SQL Server требует, чтобы определяемые пользователем функции были квалифицированы по схеме, но встроенные функции не являются квалифицированными по схеме.
Используется IsBuiltIn для сопоставления метода CLR со встроенной функцией:
public static int IsDate(string value)
=> throw new NotSupportedException();
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(IsDate), [typeof(string)]))
.HasName("ISDATE")
.IsBuiltIn();
Свойство IsBuiltIn предоставляет ту же конфигурацию при использовании атрибута:
[DbFunction(Name = "ISDATE", IsBuiltIn = true)]
Сопоставление функции с помощью DbFunctionAttribute
Вместо регистрации функции в OnModelCreating статический метод, объявленный в DbContext, можно напрямую сопоставить с помощью применения DbFunctionAttribute. Свойства атрибута Name, Schema, IsBuiltIn и IsNullable настраивают соответствующие характеристики функции базы данных; это те же характеристики, которые настраиваются методами fluent API HasName, HasSchema, IsBuiltIn и IsNullable при использовании HasDbFunction. Методы, помеченные атрибутами, в классе контекста обнаруживаются и регистрируются автоматически; методы, помеченные атрибутами, в других классах по-прежнему необходимо регистрировать с помощью HasDbFunction. Вызывайте HasDbFunction для автоматически зарегистрированного метода только в том случае, если для дополнительной гибкой конфигурации требуется построитель, как в приведённом ниже примере с типом хранилища.
Например, следующий метод использует DbFunctionAttribute для сопоставления со встроенной функцией JSON_VALUE SQL Server. Так как true является IsBuiltIn, EF Core выводит имя функции без схемы.
[DbFunction(Name = "JSON_VALUE", IsBuiltIn = true, IsNullable = true)]
public static string JsonValue(Dictionary<string, string> json, string path)
=> throw new NotSupportedException();
Настройка типов хранилища
Используется HasStoreType для настройки типа возвращаемого хранилища функции и HasStoreType настройки типа хранилища параметра. Это особенно полезно, если тип параметра CLR не имеет собственного сопоставления базы данных.
В этом примере JsonEntity.Metadata — это словарь, сохраняемый как nvarchar(max) с помощью преобразователя значений. Параметр функции json имеет тот же тип хранилища, а результат использует тип nvarchar(4000), возвращаемый 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)");
Затем функцию можно использовать с преобразованным свойством:
var jsonQuery = context.JsonEntities.Select(e => BloggingContext.JsonValue(e.Metadata, "$.Filter"));
SELECT JSON_VALUE([j].[Metadata], N'$.Filter')
FROM [JsonEntities] AS [j]
Преобразователь значений берется из выражения, переданного в качестве аргумента функции. Поэтому этот шаблон работает для сопоставленного свойства, например JsonEntity.Metadata, но настройка типа хранилища параметров не делает произвольные значения словаря переводимыми. Чтобы использовать словарь в памяти, сериализуйте его и передайте полученную строку отдельно сопоставленным методу, параметр CLR которого имеет значение string.
Сопоставление метода с пользовательским SQL
EF Core также позволяет преобразовать метод CLR непосредственно в выражение SQL, а не функцию базы данных. SQL-выражение задается с помощью HasTranslation при настройке функции.
В приведенном ниже примере мы создадим функцию, которая вычисляет процентную разницу между двумя целыми числами.
Метод CLR выглядит следующим образом:
public double PercentageDifference(double first, int second)
=> throw new NotSupportedException();
Определение функции выглядит следующим образом:
// 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));
Определив функцию, ее можно использовать в запросе. Вместо вызова функции базы данных EF Core преобразует текст метода непосредственно в SQL на основе дерева выражений SQL, созданного из HasTranslation. Следующий запрос LINQ:
var query2 = from p in context.Posts
select context.PercentageDifference(p.BlogId, 3);
Создает следующий SQL:
SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]
Предостережение
HasTranslation работает с деревом выражений SQL, а не с текстом SQL. Перевод должен создавать корректные объекты SqlExpression с правильными сопоставлениями типов, допустимостью null и распространением допустимости null для аргументов. Неверные метаданные могут создавать недопустимые результаты SQL или неверные результаты запроса, а типы выражений, используемые переводом, могут быть характерны для поставщика базы данных. Используйте этот низкоуровневый API только после того, как вы разберётесь с деревом выражений SQL поставщика данных; по возможности предпочитайте обычное сопоставление функций или существующую трансляцию, предоставляемую поставщиком.
Настройка аннулируемости функции, определяемой пользователем, на основе её аргументов.
Если значение NULL распространяется из аргумента функции, то есть функция возвращается null всякий раз, когда этот аргумент имеет nullзначение — EF Core может создать более эффективный SQL. Настройте это, вызвав PropagatesNullability с соответствующими параметрами. Дополнительные сведения о том, как EF Core компенсирует трехзначную логику SQL, см. в разделе "Семантика запроса NULL".
Чтобы проиллюстрировать это, определите пользовательную функцию ConcatStrings:
CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
RETURN @prm1 + @prm2;
END
и два метода CLR, которые соответствуют ему:
public string ConcatStrings(string prm1, string prm2)
=> throw new InvalidOperationException();
public string ConcatStringsOptimized(string prm1, string prm2)
=> throw new InvalidOperationException();
Конфигурация модели (внутри OnModelCreating метода) выглядит следующим образом:
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();
});
Первая функция настраивается стандартным способом. Вторая функция настроена, чтобы воспользоваться преимуществами оптимизации распространения null, предоставляя дополнительные сведения о том, как функция работает вокруг параметров NULL.
При выполнении следующих запросов:
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");
Мы получаем этот 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)
Второй запрос не требует повторной оценки самой этой функции для проверки её наличия значения null.
Замечание
Настраивайте распространение допуска значения NULL только если функция может возвращать null исключительно потому, что один или несколько настроенных параметров имеют значение null.
Сопоставление запрашиваемой функции с табличным значением функции
EF Core также поддерживает сопоставление с функцией, возвращающей табличное значение, с помощью определяемого пользователем метода CLR, возвращающего IQueryable из типов сущностей, тем самым позволяя EF Core сопоставлять ТВФ с параметрами. Процесс аналогичен сопоставлению скалярной определяемой пользователем функции с функцией SQL: нам нужен TVF в базе данных, функция CLR, используемая в запросах LINQ, и сопоставление между ними.
В качестве примера мы будем использовать табличную функцию, которая возвращает все записи, имеющие по крайней мере один комментарий, соответствующий заданному порогу "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 выглядит следующим образом:
public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
=> FromExpression(() => PostsWithPopularComments(likeThreshold));
Подсказка
Вызов FromExpression в теле функции CLR позволяет использовать эту функцию вместо обычного DbSet.
Ниже приведено сопоставление:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), [typeof(int)]));
Замечание
Запрашиваемая функция должна быть сопоставлена с табличной функцией.
HasTranslation поддерживает только скалярные функции и не может использоваться для табличной функции.
При сопоставлении функции выполняется следующий запрос:
var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
orderby p.Rating
select p;
Производит:
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]
Совместная работа с нами на GitHub
Источник этого содержимого можно найти на GitHub, где также можно создавать и просматривать проблемы и запросы на вытягивание. Дополнительные сведения см. в нашем руководстве для участников.
