linq - EF Core LINQ 使用标量函数

Question

我使用 Entity Framework 核心 2.1。

我在数据库中有一个标量函数，它增加了指定的天数。
我创建了一个扩展方法来执行它:

public static class AdventureWorks2012ContextExt
    {
        public static DateTime? ExecFn_AddDayPeriod(this AdventureWorks2012Context db, DateTime dateTime, int days, string periodName)
        {
            var sql = $"set @result = dbo.[fn_AddDayPeriod]('{dateTime.ToString("yyyy-MM-dd HH:mm:ss.fff")}', {days}, '{periodName}')";
            var output = new SqlParameter { ParameterName = @"result", DbType = DbType.DateTime, Size = 16, Direction = ParameterDirection.Output };
            var result = db.Database.ExecuteSqlCommand(sql, output);
            return output.Value as DateTime?;
        }
    }

我尝试在查询中使用标量函数(以简化我使用 AdventureWorks2012 的操作)，如下所示:

var persons =
    (from p in db.Person
     join pa in db.Address on p.BusinessEntityId equals pa.AddressId
     where p.ModifiedDate > db.ExecFn_AddDayPeriod(pa.ModifiedDate, 100, "DayPeriod_day")
     select p).ToList();

但是得到一个 System.InvalidOperationException: '在前一个操作完成之前在此上下文上启动了第二个操作。不保证任何实例成员都是线程安全的。

我怎样才能做到这一点？

更新:
在伊万的回答的帮助下，我设法做到了:

var persons =
    (from p in db.Person
     join bea in db.BusinessEntityAddress on p.BusinessEntityId equals bea.BusinessEntityId
     join a in db.Address on bea.AddressId equals a.AddressId
     where p.ModifiedDate > AdventureWorks2012ContextFunctions.AddDayPeriod(a.ModifiedDate, 100, "DayPeriod_day")
     select p).ToList();

但现在我需要更新已过滤人员的 ModifiedDate。所以我这样做:

var persons =
     (from p in db.Person
      join bea in db.BusinessEntityAddress on p.BusinessEntityId equals bea.BusinessEntityId
      join a in db.Address on bea.AddressId equals a.AddressId
      let date = AdventureWorks2012ContextFunctions.AddDayPeriod(a.ModifiedDate, 100, "DayPeriod_day")
      where p.ModifiedDate > date
      select new { Person = p, NewDate = date }).ToList();

  foreach (var p in persons)
      p.Person.ModifiedDate = p.NewDate ?? DateTime.Now;

  db.SaveChanges();

但得到 System.NotSupportedException: 'Specified method is not supported.'

如何在 select 语句中使用标量函数？

我试图将查询分为两部分:

var filteredPersons =                  // ok   
   (from p in db.Person
    join bea in db.BusinessEntityAddress on p.BusinessEntityId equals bea.BusinessEntityId
    join a in db.Address on bea.AddressId equals a.AddressId
    where p.ModifiedDate > AdventureWorks2012ContextFunctions.AddDayPeriod(a.ModifiedDate, 100, "DayPeriod_day")
    select new { Person = p, a.ModifiedDate }).ToList();

var persons =                          // here an exception occurs
    (from p in filteredPersons
     select new { Person = p, NewDate = AdventureWorks2012ContextFunctions.AddDayPeriod(p.ModifiedDate, 100, "DayPeriod_day") }).ToList();

Answer 1

您可以使用 EF Core client evaluation 而不是调用客户端函数(这种特殊情况是查询过滤器的 Database scalar function mapping 的一部分，而查询读取仍在进行中)所以

can be used in LINQ queries and translated to SQL.

一种方法是在派生上下文类中创建一个公共(public)静态方法并用 DbFunction 标记它。属性:

public partial class AdventureWorks2012Context
{
    [DbFunction("fn_AddDayPeriod")]
    public static DateTime? AddDayPeriod(DateTime dateTime, int days, string periodName) => throw new NotSupportedException();
} 

并使用

where p.ModifiedDate > AdventureWorks2012Context.AddDayPeriod(pa.ModifiedDate, 100, "DayPeriod_day")

另一种方法是在另一个类中创建一个公共(public)静态方法

public static class AdventureWorks2012DbFunctions
{
    [DbFunction("fn_AddDayPeriod")]
    public static DateTime? AddDayPeriod(DateTime dateTime, int days, string periodName) => throw new NotSupportedException();
} 

但随后您需要使用 fluent API 注册它(对于在上下文派生类中定义的方法会自动发生):

modelBuilder
    .HasDbFunction(() => AdventureWorks2012DbFunctions.AddDayPeriod(default(DateTime), default(int), default(string)));

用法是一样的:

where p.ModifiedDate > AdventureWorksDbFunctions.AddDayPeriod(pa.ModifiedDate, 100, "DayPeriod_day")