Hi I am using DataAccessAdapter and I have the following query.

SELECT
t1.,t2., MinPrice, MaxPrice
FROM Table1 t1

  INNER JOIN Table2 t2 t1.t1ID= t2.t2ID 
  LEFT JOIN 
 (  
   SELECT ProductID, MinPrice=MIN(price), MaxPrice=MAX(price)
   FROM Table3 t3  WHERE t3.status = 1 
   GROUP BY ProductID
 )
 AS newTable ON newTable.ProductID = t1.ProductID  

Please let me know how to write this relationship in LLBLGEN. Is there any good alternate way to write this query?

Thanks

SELECT
    t1.t1ID,
    t1.ProductID,
    t1.z,
    t2.t2ID,
    t2.y,
    (SELECT MIN(price) FROM Table3 t3 WHERE t3.ProductID = t1.ProductID) MinPrice,
    (SELECT MAX(price) FROM Table3 t3 WHERE t3.ProductID = t1.ProductID) MaxPrice,

FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.t1ID = t2.t2ID 

Here you can use DynamicLists and ScalarQueryExpressions.

Hi, It's cute for this query.

But what if. 1. There are more number of fields in the select clause and 2. The predicate is more complex than this which filters the data in left join say, 4-6 fields in predicate.

So in that case we will be repeating predicate for each field in the left join? Well, I guess then writing query will be more cumbersome for me What you say?

Thanks.

Well. The more complex query, the more lines of code you have to write, that's unavoidable. However, with LLBLGen you can reuse filters. You also can use LINQ2LLBL if you like to write IQueryables.

When you would immerse more in predicate system, maybe you wont think in term of "SQL queries", but in terms of "what do I want to fetch", so you will only think once, not two (think the query, and think the predicate expression construct).

Cheers,,

How do i restrict the number of rows to be retrived in any query using llblgen

can i append the "fetch first 100 rows only" or use the rownum between x and y commands.

IF so can you post an example or alternate solution.

Obviously it will be ineffective to query all rows and then process especically when you do not have a tight business key to slice your query by it.

thanks.

Hi Mri,

DataAccessAdapterClass (for Adapter) and the GetMulti method (for SelfServicing) offer a parameter to indicate the maxNumberOfItemsToReturn. This way you can limit the resulset . For example (Adapter):

int maxItemsToReturn = 10;
EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>();

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    // this will return only 10 entities
    adapter.FetchEntityCollection(orders, null, maxItemsToReturn);
}

P.S. Next time please create a new thread (don't hijack other/old threads ) as stayed in the guidelines.