EF Core LINQ use scalar function

Question

I use Entity Framework Core 2.1.

I have a scalar function in the database which adds specified number of days. I created an extension method to execute it:

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?;
        }
    }

I try to use a scalar function in the query (to simplify things I use AdventureWorks2012) as follows:

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();

But get an System.InvalidOperationException: 'A second operation started on this context before a previous operation completed. Any instance members are not guaranteed to be thread safe.'

How can I achieve this?

UPDATE: I managed to do it with the help of Ivan's answer:

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();

But now I need to update ModifiedDate for filtered persons. So I'm doing like this:

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();

But got System.NotSupportedException: 'Specified method is not supported.'

How can I use scalar function in select statement?

I tried to split the query by two parts:

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

Instead of invoking the function client side (which is this particular case happens as part of the client evaluation of the query filter, while the query reading is still in progress), you can use EF Core Database scalar function mapping so it

can be used in LINQ queries and translated to SQL.

One way to do that is to create a public static method in the derived context class and mark it with DbFunction attribute:

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

and use

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

Another way is to create a public static method in another class

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

but then you'll need to register it with fluent API (which happens automatically for methods defined inside the context derived class):

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

The usage is the same:

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