我使用的是Entity Framework Core 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: '在上次操作完成之前,在此上下文中启动了第二个操作。 任何实例成员都不能保证是线程安全的。'
我应该怎么做呢?
更新:在 Ivan 的回答的帮助下,我成功做到了。
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:“不支持指定的方法。”
我该如何在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();
fn_AddDayPeriod
可以修改为接受datetime
而不是当前使用的varchar
(或其他文本类型)吗? - Ivan Stoev