Execute function in LINQ scenarios

[yarikus]

We are migrating to Postgres DevArt Entity Model and have some problems with functions.
With Entity Framework from Microsoft our functions were imported to model as composable and we could use them in LINQ scenarios like this:

Code: Select all

from fts in this.Context.GetClientsUsingFts(term)
  join ...
  where ...
  select fts.Id;

But with DevArt Entity Model we can not import our functions. The next message is appears:
"Cannot create method for a storage function ... that can be composed. Only stored procedures may be mapped"

With "Consealed Function" flag we can execute functions directly from context but they are not working with LINQ scenarious. (((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<...> is used in generated code).

Please help us with solution how to support our scenarious.

Demo function which can not be imported to conceptual model:

Code: Select all

CREATE FUNCTION demoFunction(IN keywords character varying)
  RETURNS TABLE(par integer, rank real) AS
$BODY$
    SELECT "Id" as par, value as rank FROM demoTable;
$BODY$
  LANGUAGE sql 

[Shalex]

With "Consealed Function" flag we can execute functions directly from context but they are not working with LINQ scenarious.

1. That is correct: "Consealed Function" should be set to True.
2. Specify the exact text of the error and its full call stack.

This code works in our environment with your demoFunction:

Code: Select all

    var result = (from fts in context.Demofunction("keyword")
                join d in context.Depts on fts.Par equals d.Deptno
                where fts.Par == d.Deptno
                select fts.Par).ToList();

[yarikus]

Thank you for reply.
But we need DemoFunction result as IQueryable to support scenario like this:

Code: Select all

IQueryable query1 = from m in context.Books
    select m;

 IQueryable query2 = from fts in context.DemoFunction("keyword")
     select fts.Par;

 var query = from q in query1
    join r in query2 on q.Id equals r
    select q;

 var result = query.ToList();

[Shalex]

we need DemoFunction result as IQueryable

There is no way to implement this scenario.

As a workaround, please create an additional database function which will implement JOIN at the database side. Also this approach will increase a performance of your application.