Page 1 of 1
Using Oracle Stored Function in Linq Query
Posted: Thu 15 Feb 2018 22:58
by PatConnelly
Hello!
Is this possible:
Code: Select all
create or replace function Some_Function( inNumber number) return number is
begin
return inNumber + 1;
end;
/
Code: Select all
var num = db.SomeTable.Where(a=> a.SomeFunction(1) > 1).ToList();
var list = from s in db.SomeTable
select SomeFunction(s.num);
Or any other way to use custom stored functions within linq queries?
Thanks,
Patrick
Re: Using Oracle Stored Function in Linq Query
Posted: Fri 16 Feb 2018 15:48
by Shalex
Re: Using Oracle Stored Function in Linq Query
Posted: Fri 22 Jun 2018 16:53
by df5
Is this possible in EF Core? I'm using the insider build Devart Oracle EF version 9.5.527 and I am not having any success.
I get an error of "A second operation started on this context before a previous operation completed." error when this LINQ statement is issued:
using (var db = new ScheduleContext())
{
var query = (from s in db.Schedules
where s.OPR_DATE == System.DateTime.Now.Date &&
db.IsAuthorizedFunction("SomeUserAccount", s.FORNAME, s.WITHNAME, s.SENDERNAME, s.RECEIVERNAME, s.OPR_DATE) == 1
select s).ToList();
}
The DbContext IsAuthorizedFunction is defined like this:
public int IsAuthorizedFunction(string userAccount, string forCompanySN, string withCompanySN, string senderCompanySN, string receiverCompanySN, DateTime oprDate)
{
OracleParameter pReturn = new OracleParameter("pReturn", OracleDbType.Number, ParameterDirection.ReturnValue);
var isAuthd = this.Database.ExecuteSqlCommand("begin :pReturn := pkgSecurity.Is_Authorized(:p0 ,:p1, :p2, :p3, :p4, :p5); end;",
pReturn, userAccount, forCompanySN, withCompanySN, senderCompanySN, receiverCompanySN, oprDate);
return Convert.ToInt16(pReturn.Value);
}
Re: Using Oracle Stored Function in Linq Query
Posted: Fri 22 Jun 2018 18:13
by df5
For anyone interested in implementing it in EF Core, the solutions is from this post and is quite very simple. The key is the [DbFunction] attribute.
https://github.com/aspnet/EntityFramewo ... ssues/7368