There are two main aspects we have been talking about:

1. The one you ask about - hand made optimized queries expressed in the language of the PMapper (ie sql)
2. Easy access to unique unbeatable PMapper functionality like the database aggregation functions (ie sum,avg,count used with group by)

The number 2 has been has two very different possible solutions. The first solution is to implement these functions in OCLPs and that will require OCLPs to handle tuple creating in the results; one would also expect ECO to maintain returned sum and avg results using this approach and this I think is not very practical (after all the db-engine can do this on millions of rows).

So that is why I think that a solution for #2 may very well be a solution for #1.  A solution as simple as the introduction of a way to build sql from model-info.

select attributes, sql-functions from tables where joincriterias and othercriterias

The argument against this approach is that there are scenarios where the PMapper handles the types of attibutes very differently in the PStorage than what it does in ECO (boolean values being the simplest example (where x=false, should be be where x=0 etc \) ).

Anyway we welcome the discussion and we will be very happy to implement a good solution for this that stands the test of time.

(Whenever you find a "missing feature" in ECO you may consider our user-paid-for-extension strategy; you express your needs; we come up with a solution and fixed discounted price to add it to the product; your idea - our implementation reach you and all ECO users with priority. We will probably implement good ideas anyway but it will take longer time to reach you)

OCLPs enables access but the transformation is huge and you will loose ability to fine tune specific questions.

I think we, at some point, will need a way to have a thinner transformation between model and sql. So if I am correct in this assumption we might as well make the most of this approach and use the same solution to solve all the issues we see today.

public GetResult(operationInfo) {

  if (operationInfo.Mapping.PsEngine is not SqlServer) {

    throw new NotSupportedException ("For now this operation supports SqlServer only.".

  }

  var productKey = operationInfo.Mapping.GetKeyNameFor<Product>();

  var productTable = operationInfo.Mapping.GetTableNameFor<Product>();

  var orderInfoTable = operationInfo.Mapping.GetTableNameFor<OrderInfo>();

  var orderInfoKey = operationInfo.Mapping.GetKeyNameFor<OrderInfo>();

  var productPriceName = operationInfo.Mapping.GetPropertyNameFor<Product>(x=>x.Price);



  var poorSqlInTermsOfPs = string.Format("select max(p.{0}) as MaxSoldPrice from {1} p, {2} oi where oi.{3} = p.{4}", productPriceName, productTable, orderInfoTable, orderInfoKey, productKey);



  var query = operationInfo.CreatePsQuery(poorSqlInTermsOfPs, operationInfo.Returns<int>(), possiblySomeAdditionalSqlParameters);

  var result = (int)query.Execute().AsObject;

  return result;

}