前言
上一节我们讲完原始查询如何防止 SQL 注入问题同时并提供了几种方式本节我们继续来讲讲 EF Core 2.0 中的新特性自定义标量函数
自定义标量函数两种方式
在 EF Core 2.0 中我们可以将方法映射到数据库中的标量函数, 我们可在 LINQ 中调用此方法并会被正确翻译成 SQL 语句, 这为编写数据访问层的开发人员提供了一个很棒的功能来创建一个方法并在其上应用 DbFunction 特性即可该属性会将静态 CLR 方法映射到数据库函数, 以便可以在 LINQ 查询中使用此方法默认情况下, 数据库函数中的 CLR 静态方法名称必须相同, 除非我们在 DbFunctionAttribute 中指定了不同的名称自定义标量函数必须满足如下两个条件
(1) 函数必须是静态方法且在上下文中声明
(2) 只能作为参数标量值返回
自定义标量函数方式一
我们可直接在上下文中定义一个静态方法, 如下:
- [DbFunction]
- public static string ScalarFunction(string name)
- {
- throw new NotImplementedException();
- }
自定义标量函数方式二
- public static string ScalarFunction(string name)
- {
- throw new NotImplementedException();
- }
然后在 OnModelCreating 方法利用 ModelBuilder 中的 HasDbFunction 来调用上述方法, 如下:
- modelBuilder.HasDbFunction(
- () => ScalarFunction(null));
请注意以上自定义标量函数的两种方式必须定义架构名称即 Schema, 否则在调用上述方法查询时将抛出 System.Data.SqlClient.SqlException:'您自定义的函数名称' 不是可以识别的 内置函数名称, 也就是说我们无论是利用 DbFunction 特性还是 HasDbFunction 方法映射自定义标量函数也好都必须指定 Schema, 我们默认指定为 dbo, 如下:
- [DbFunction(FunctionName = "UdfFunction", Schema = "dbo")]
- public static string ScalarFunction(string name)
- {
- throw new NotImplementedException();
- }
或者
- public static string ScalarFunction(string name)
- {
- throw new NotImplementedException();
- }
- modelBuilder.HasDbFunction(
- () => ScalarFunction(null)).HasName("UdfFunction").HasSchema("dbo");
或者
- modelBuilder.HasDbFunction(GetType()
- .GetMethod("ScalarFunction"), options =>
- {
- options.HasName("UdfFunction");
- options.HasSchema("dbo");
- });
上述讲解了在 EF Core 2.0 中如何创建标量函数, 讲了这么多, 到底怎么用, 或者说它的出现可以解决什么问题呢? 下面我们首先来看一个例子比如我们想查询每篇博客的评论数的均值, 接下来我们会进行如下查询:
- using (var context = new EFCoreDbContext())
- {
- var blogs = context.Blogs
- .AsNoTracking();
- var result = blogs.Select(b => new BlogDTO()
- {
- Id = b.Id,
- Name = b.Name,
- Count = b.Posts.Count() > 0 ? b.Posts.Average(d => d.CommentCount) : 0
- }).ToList();
- }
此时将出现函数 Average 无法翻译成 SQL, 只能在内存中进行查询在 EF Core 中如果您有详细查看过生成的 SQL 语句的话, 您就能够明白, 对于 MinMaxAverage 等 LINQ 函数, EF Core 不支持翻译成远程 SQL, 只能在本地查询此时我们再来看看进行此次查询总共耗时 100ms, 如下:
接下来我们再利用自定义标量函数查询试试首先定义标量函数
- public static double? UdfAverage(int blogId)
- {
- throw new Exception();
- }
- modelBuilder.HasDbFunction(
- () => UdfAverage(default(int))).HasSchema("dbo");
然后我们再来创建标量函数
- public static class AddUdfHelper
- {
- public static void AddUdfToDatabase(this DbContext context)
- {
- using (var transaction = context.Database.BeginTransaction())
- {
- try
- {
- context.Database.ExecuteSqlCommand(
- "IF OBJECT_ID('dbo.UdfAverage', N'FN') IS NOT NULL" +
- "DROP FUNCTION dbo.UdfAverage");
- context.Database.ExecuteSqlCommand(
- "CREATE FUNCTION UdfAverage (@blogId int)" +
- @" RETURNS FLOAT
- AS
- BEGIN
- DECLARE @result AS FLOAT
- SELECT @result = AVG(CAST([CommentCount] AS FLOAT)) FROM dbo.Posts AS p
- WHERE p.BlogId = @blogId
- RETURN @result
- END");
- transaction.Commit();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- }
- }
上述标量函数理应在迁移时生成, 现在我们首先在上下文构造函数中创建即在运行时创建在数据库中函数中的标量函数中将生成 UdfAverage 函数, 如下:
接下来我们再来调用创建的自定义标量函数, 如下:
- using (var context = new EFCoreDbContext())
- {
- var blogs = context.Blogs
- .AsNoTracking();
- var result = blogs.Select(b => new BlogDTO()
- {
- Id = b.Id,
- Name = b.Name,
- Count = EFCoreDbContext.UdfAverage(b.Id)
- }).ToList();
- }
我们看看此此查询总共耗时 77ms 相比上述未调用标量函数直接调用 Average 方法, 不会翻译成 SQL, 所以在数据库中查询一次, 然后加载到内存中再查询一次, 效果显而易见
总结
本节我们详细讲解了 EF Core 2.0 中的自定义标量函数, 若我们需要进行子查询返回标量值时此时创建自定义标量函数将成为首选, 其性能比调用内置的 APi 然后在内存中进行查询而不会翻译成 SQL 的性能更好精简的内容, 简单的讲解, 希望对阅读的您有所帮助, 我们明天再会
来源: https://www.cnblogs.com/CreateMyself/p/8485697.html