Using results from stored procedures/functions in LINQ joins or subqueries? ("parameterized view")

Hi,

I'm using LS4 and Postgresql 9.  I've got a Postgresql FUNCTION that returns a result set (basically the sproc wraps a SELECT that's doing lots of conditional Postgresql full-text comparisons that are a little hard to do in lightspeed).  Currently the SELECT returns records directly mapped to one of my entity types.

Ultimately I'd like parameterized views.  I'd like to be able to use the function as a entity source within LINQ queries, ultimately so that I can obtain an IQueryable<Entity> so that I can reuse that in many spots, and do additional sub-filtering and sorting in each of these spots.  I'd like to ensure that optimized SQL is used, primarily so that the filters and sorting is done on the db server.

Basically if the function name is MyFunction the SQL that would be generated could be something like:

SELECT *

FROM MyFunction(param1, param2)

WHERE

{ where and order by clauses built through IQueryable .Where() }

I've looked at SelectProcedures and it doesn't look like it's intended for this.  I've also read up on registering server functions, but am unsure of how or if that approach would work.

Is this possible with Lightspeed?  Thanks! 

It's not possible at the moment -- you can call sprocs or functions directly, but you can't do a SELECT from the function result, so you can't compose things like OrderBy and Where with the query.  We've been asked for a similar feature before though so we'll try to take another look -- thanks for the feedback!

Actually, thinking about this, you might be able to do it using a view name e.g.

var viewName = String.Format("MyFunction({0}, {1})", param1, param2);
var results = unitOfWork.Query<MyEntity>(viewName).Where(...);

(or, if you're using the query objects API, set Query.ViewName).

Obviously you can wrap this up into a helper method on your strong-typed unit of work class:

partial class MyModelUnitOfWork {
  public IQueryable<MyEntity> MyFunction(int param1, string param2) {
    var viewName = String.Format("MyFunction({0}, {1})", param1, param2);
    return this.Query<MyEntity>(viewName);
  }
}

I haven't tested this but I think it should work.  Let us know how it goes!

NOTE: Because this trick passes the parameters directly as part of the 'view' name, rather than as SQL parameters, it opens up a vulnerability to SQL injection.  Be very careful if the 'parameterised view' parameters are coming from user input!

Should say "this.Query" in the second fragment -- sorry!