使用Entity Framework Core(2.1)调用标量函数的最佳实践 [英] Best practice calling scalar functions with Entity Framework Core (2.1)

问题描述

我经常需要从我的Web应用程序(ASP.NET Core/EF Core)中调用在SQL Server上定义的标量函数.由于这些函数只是简单的辅助函数，因此我也使用了许多辅助函数，因此在EF Core 2.1可用的新查询类型的帮助下，我使用了通用模式来调用这些标量函数. 由于我是EF Core的新手，所以我的问题是这种模式是否会引起问题和/或是否有更好的解决方案或最佳实践来调用标量函数.该解决方案有效，到目前为止，我还没有发现任何问题，但是例如，我想知道是否由于EF Core中的缓存/跟踪行为等原因，对不同的功能使用相同的查询类型是否可能导致意外的值或奇怪的行为-这更多的是直觉.

I often need to call scalar functions that are defined on a SQL Server from my web applications (ASP.NET Core / EF Core). Since these functions are just simple helper functions and I also use a lot of them I use a general pattern for calling these scalar functions - with the help of the new query types available from EF Core 2.1. Since I am relatively new to EF Core my question is if this pattern might cause problems and/or if there is a better solution or best practice for calling scalar functions. The solution works and I cannot observe any problems so far but for example I wondered if using the same query type for different functions might lead to unexpected values or weird behaviour due to caching/tracking behaviour, etc. within EF Core - it's more of a gut feeling.

所以这是模式: 不必为每个单个标量函数定义不同的实体类型，而是仅定义一个通用类型:

So here's the pattern: Instead of defining different entity types for every single scalar function I simply define one generic type:

public class PrimitiveDto<T>
{
    public T Value { get; set; }
}

在我的上下文类中，我为要使用的标量函数期望的每种返回类型注册了这些类型-因此，对于所有返回"int"的标量函数，上下文类将具有一个附加条目，如下所示:

In my context class I register these types for every return type I expect from the scalar functions I want to use - so for all scalar functions returning 'int' the context class would have one additional entry like this:

public virtual DbQuery<PrimitiveDto<int>> BasicIntDto { get; set; }

在我要调用返回"int"的标量函数的应用程序的每个部分中，我都使用相同的以下模式:

In every part of the application where I want to call a scalar function returning 'int' I simply use the same following pattern:

context.BasicIntDto.FromSql("SELECT <FUNCTION> AS Value")

通过使用这种模式，我可以以相同的方式调用任意数量的函数，而无需定义其他类型或扩展上下文类.

By using this pattern I can call any number of functions the same way without defining additional types or extending the context class.

请让我知道是否可以通过此模式遇到陷阱.非常感谢.

Please let me know if I could run into a trap through this pattern. Thank you very much.

推荐答案

不幸的是，该功能似乎已经被搁置了: https://github.com/aspnet/EntityFrameworkCore/issues/9810

Unfortunately it seems this feature has been left aside: https://github.com/aspnet/EntityFrameworkCore/issues/9810

一种选择是使用一个永远不会为空的小表将函数调用包装在静态类中:

An option is to wrap the functions calls in a static class using a small table which is never empty:

public static class DbFunctions
{
   public static decimal MyFunctionABC(int param1, int param2)
   {
       using (var db = new MyDbContext())
       {
        return db.table.Take(1).Select(t => MyDbContext.MyFunctionABC(x, y)).Single();
       }
    }
 }

然后您可以拨打DbFunctions.MyFunctionABC(x,y);

这篇关于使用Entity Framework Core(2.1)调用标量函数的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！