Using Oracle Stored Function in Linq Query

Discussion of open issues, suggestions and bugs regarding Entity Developer - ORM modeling and code generation tool
Post Reply
PatConnelly
Posts: 15
Joined: Fri 17 Jun 2005 14:15

Using Oracle Stored Function in Linq Query

Post by PatConnelly » Thu 15 Feb 2018 22:58

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

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Using Oracle Stored Function in Linq Query

Post by Shalex » Fri 16 Feb 2018 15:48


df5
Posts: 12
Joined: Wed 20 Jun 2018 23:22

Re: Using Oracle Stored Function in Linq Query

Post by df5 » Fri 22 Jun 2018 16:53

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

df5
Posts: 12
Joined: Wed 20 Jun 2018 23:22

Re: Using Oracle Stored Function in Linq Query

Post by df5 » Fri 22 Jun 2018 18:13

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

Post Reply